За чистый и ясный код!

Статьи на тему программирования под веб, используя PHP, MySQL, Jquery и многое другое

Как из csv занести данные в mysql

Апрель29
Спонсор статьи: Электронная книга azbooka n516 black - читалка на основе электронных чернил. Экран идентичен обычной бумаге.

Всем привет!

Сегодня я хочу рассказать как можно данные из CSV-файла загрузить в БД MySQL.

Для сего это нужно???? Да разные бывают ситуации, иногда клиенты из экселя дают нам данные и просят их занести в БД, а как это сделать???? Не будем же мы подключать массивные скрипт для обработки этого файла и потом еще наступим на грабли с кодировкой… Есть пути куда проще.

В предыдущей своей статье я рассказывал, как записать данные из БД в CSV-файл.

Имеется содержимое файла

"id";"login";"pass"
"3";"superadmin";"da1c42eb9cec3336fa8e996832fcbc90"
"4";"admin";"21232f297a57a5a743894a0e4a801fc3"
"5";"Fabian_Admin";"60f8ffb1f0d1d30a3d4a5909452da58d"

Первое что делаем, убираем первую строку — аля название поля в БД. Именно тут эта строка не влияет ни на что.
Далее создаем таблицу в БД с соот полями:

CREATE TABLE `user` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`login` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`pass` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	PRIMARY KEY (`id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT

Теперь просто используя запрос:

LOAD DATA INFILE 'd:\\user.csv'
INTO TABLE `user`
FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\'
LINES STARTING BY '' TERMINATED BY '\r\n';

Конструкция LOAD DATA INFILE ‘d:\\user.csv’ говорит о том, помещен файл с данными по указанному пути, путь абсолютный.

Конструкция FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘»‘ ESCAPED BY ‘\r\n’ говорит о том, что каждое поля в файле будут разделены знаком ;, также что каждое поле будет обернуто в знак «, если внутри поле будет соот знак, то БД позаботится об экранирования (ESCAPED BY) таких знаков (правила построения CSV файлов).

Конструкция LINES STARTING BY » TERMINATED BY ‘\r\n’ говорит о том, что каждая строка начинается с указаного занчения в LINES STARTING BY, а заканчивается строка значением в TERMINATED BY, в данном примере, я ничего не присваиваю началу строки, просто написал чтобы был виден общий синтаксис. Т.к. я юзаю Windows, которому в качестве переноса строки необходимо указать \r\n, поэтому я это значение и поставил в TERMINATED BY, если Вы юзаете *nix подобные системы, то там значение конца строки \n

В итоге выполнения запроса в таблицы БД будет следующее:

mysql> select * from user;
+----+--------------+----------------------------------+
| id | login        | pass                             |
+----+--------------+----------------------------------+
|  3 | superadmin   | da1c42eb9cec3336fa8e996832fcbc90 |
|  4 | admin        | 21232f297a57a5a743894a0e4a801fc3 |
|  5 | Fabian_Admin | 60f8ffb1f0d1d30a3d4a5909452da58d |
+----+--------------+----------------------------------+
3 rows in set (0.00 sec)

Пожалуйста пользуйтесь!!! Есть вопросы — через комментарии.
UPD:

Вчера я допустил неточность, сказав что из CSV файла необходимо вручную убрать первую строчку. Её можно убрать с помощью запроса. Строка IGNORE num LINES, гду num это кол-во строк которые надо пропустить.

Вот полный запрос:

LOAD DATA INFILE 'd:\\user.csv'
INTO TABLE `user`
FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\'
LINES STARTING BY '' TERMINATED BY '\r\n'
IGNORE 1 LINES;

Статья просмотренна 57633 раз, зашло посетителей 17459

рубрика: MySQL

14 комментариев в “Как из csv занести данные в mysql”

  1. Avatar
    @ohAwesome пишет:

    Спасибо за статью! Может сможете подсказать как лучше совместить такую конструкцию с учетом сохранения исходных данных + переводом их на еще 2 языка? Исходный файл в iso-8859-1 и есть гугл скрипт переводящий содержимое. Решение "в лоб" (вложенные циклы и последующая отправка в бд) — дает неточности и очень медленно работает…


  2. Avatar
    maxnag пишет:

    Привет. Всегда пожалуйста. Не совсем понимаю о какой конструкции Вы говорите, если хотите скиньте файл на maxnag собака meta.ua


  3. Avatar
    gnome пишет:

    странно как то даже это читать, позиционируемое автором как хау-ту, это вродь как все RTFM, честно говоря понадеялся, что какой то революционно-новый способ появился.


  4. Avatar
    maxnag пишет:

    ну кто-то знает об этом, а кто-то нет. Я например не знал, когда стала задача, разобрался и отписался….

    Только не кричите, баян — это раздражает))


  5. Avatar
    Scriptin пишет:

    Так просто?! Черт, я и не знал, что формат CSV устроен таким образом. Именно это решение как раз нужно моему другу для переноса данных из Excel-таблиц в БД. Спасибо!


  6. Avatar
    maxnag пишет:

    пользуйтесь! Правда в самом экселе есть ошибка, если в файле есть "особые буквы", то они не попадают в этот формат, поэтому я всегда сохранял в тектс-юникод с разделение по табу, затем открывал блокнотом, заменял таб на точку с запятой и работал дальше… геморно, но нет потери в тексте.


  7. Avatar
    Never Lex пишет:

    Надо теперь адаптировать для загрузки файла и будет механизм обновления данных интернет-магазина. Его можно вручать клиенту и пусть сам всё обновляет.

    Итого: обновление данных одной кнопкой.


  8. Avatar
    maxnag пишет:

    ну я бы не доверял клиенту такое делать, перепутает типы данных (например дату напишет прописью) или столбцы не так определить, не в той последовательность и будет все не очень хорошо. Я вот читал что с XML мускуль умеет работать, только надо понять как…


  9. Avatar
    Never Lex пишет:

    Нужно ему строгое указание давать и бэкап автоматом делать с возможностью откатить. Мол, сделал фигню — нажми кнопочку "восстановить" и ищи ошибку в своём файле 🙂


  10. Avatar
    Never Lex пишет:

    Надо только предупредить читателей, что у пользователя должна быть привилегия FILE. Без этого не получится работать с файлами, а не любой хостинг предоставляет возможность менять привилегии.


  11. Avatar
    Lirriella пишет:

    Большое спасибо, именно то, что я искала!


  12. Avatar
    maxnag пишет:

    Рад, что помог.


  13. Avatar
    LoL пишет:

    Добрый день!
    Здесь указан способ загрузки в одну табличку.
    А если в исходном csv есть данные, которые необходимо закинуть сразу в несколько таблиц? Можно как-то сделать "одним махом"?


  14. Avatar
    maxnag пишет:

    Привет. Такого хака мне не известно. Вот ссылка на документацию — может вы там найдёте ответ http://dev.mysql.com/doc/refman/5.7/en/load-data….


не публикуется

пример

Оставить комментарий или два:

  

Облако тегов

cli csv dump events form Kohana locale models MySQL mysqldump orm PHP tools trigger validate газ газовый счетчик итоги кеширование переменные

Облако тегов плагина WP Cumulus для WordPress требует для просмотра Flash Player 9 или выше.

Я на твиттере!

  • у твиттера тоже бывают перерывы...

Календарь

Апрель 2010
Пн Вт Ср Чт Пт Сб Вс
« Мар   Май »
 1234
567891011
12131415161718
19202122232425
2627282930  

Сейчас на сайте