Как из csv занести данные в mysql
Всем привет!
Сегодня я хочу рассказать как можно данные из 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;
Статья просмотренна 94676 раз, зашло посетителей 31061
Спасибо за статью! Может сможете подсказать как лучше совместить такую конструкцию с учетом сохранения исходных данных + переводом их на еще 2 языка? Исходный файл в iso-8859-1 и есть гугл скрипт переводящий содержимое. Решение "в лоб" (вложенные циклы и последующая отправка в бд) — дает неточности и очень медленно работает…
Привет. Всегда пожалуйста. Не совсем понимаю о какой конструкции Вы говорите, если хотите скиньте файл на maxnag собака meta.ua
странно как то даже это читать, позиционируемое автором как хау-ту, это вродь как все RTFM, честно говоря понадеялся, что какой то революционно-новый способ появился.
ну кто-то знает об этом, а кто-то нет. Я например не знал, когда стала задача, разобрался и отписался….
Только не кричите, баян — это раздражает))
Так просто?! Черт, я и не знал, что формат CSV устроен таким образом. Именно это решение как раз нужно моему другу для переноса данных из Excel-таблиц в БД. Спасибо!
пользуйтесь! Правда в самом экселе есть ошибка, если в файле есть "особые буквы", то они не попадают в этот формат, поэтому я всегда сохранял в тектс-юникод с разделение по табу, затем открывал блокнотом, заменял таб на точку с запятой и работал дальше… геморно, но нет потери в тексте.
Надо теперь адаптировать для загрузки файла и будет механизм обновления данных интернет-магазина. Его можно вручать клиенту и пусть сам всё обновляет.
Итого: обновление данных одной кнопкой.
ну я бы не доверял клиенту такое делать, перепутает типы данных (например дату напишет прописью) или столбцы не так определить, не в той последовательность и будет все не очень хорошо. Я вот читал что с XML мускуль умеет работать, только надо понять как…
Нужно ему строгое указание давать и бэкап автоматом делать с возможностью откатить. Мол, сделал фигню — нажми кнопочку "восстановить" и ищи ошибку в своём файле 🙂
Надо только предупредить читателей, что у пользователя должна быть привилегия FILE. Без этого не получится работать с файлами, а не любой хостинг предоставляет возможность менять привилегии.
Большое спасибо, именно то, что я искала!
Рад, что помог.
Добрый день!
Здесь указан способ загрузки в одну табличку.
А если в исходном csv есть данные, которые необходимо закинуть сразу в несколько таблиц? Можно как-то сделать "одним махом"?
Привет. Такого хака мне не известно. Вот ссылка на документацию — может вы там найдёте ответ http://dev.mysql.com/doc/refman/5.7/en/load-data….