Поиск по блогу

четверг, 27 мая 2010 г.

Запись данных из CSV-файлов в базу MySQL

Это вопрос из категории "для начинающих". Но тем не менее я решила его осветить на страницах блога, в угоду желанию объять необъятное и получить с поисковиков дополнительный трафик. :)

Итак, у вас есть CSV-файл, и перед вами встала задача записать содержимое этого файла в базу.

Импорт CSV-файла через PHPMyAdmin


Первый и самый простой способ — воспользоваться готовым инструментом. Например, функция импорта данных из csv-файла в базу есть в PHPMyAdmin-е.

Выбираем нужную таблицу, на вкладке "Структура" внизу нажимаем на "Вставить текстовые файлы в таблицу". Указаваем настройки импорта.




Внимание! Если после нажатия "Выполнить" у вас вылезли ошибки типа

ldi_check.php: Missing parameter: db
ldi_check.php: Missing parameter: table


это может объясняться разными причинами.

1. Возможно, файл, предназначенный для импорта, слишком большой. В старых версиях PHPMyAdmin (младше 2.7.0) был баг с импортом больших файлов. Так что, возможно, выходом из ситуации будет обновление PHPMyAdmin-а.

(Ссылка на пункт 1.16 FAQ) Начиная с версии 2.7.0 функция импорта была переписана и проблем с импортом больших файлов не должно возникать.

Следующее, что можно проверить (или спросить у провайдера), - это значения параметров upload_max_filesize, memory_limit и post_max_size в конфигурационном файле php.ini. Эти три настройки ограничивают максимальный размер данных, которые могут быть переданы и обработаны PHP.


Там же, в документации, приводится описание нескольких "обходных путей", которые подойдут, если вы импортируете не scv-файл, а заливаете дамп базы, а ваш провайдер не хочет менять настройки:

а) Проверьте настройку $cfg['UploadDir']. Она позволяет настроить загрузку файла на сервер через scp, ftp или другим методом. PhpMyAdmin может работать с файлами, расположенными во временном каталоге. Более подробную информацию читайте в разделе Настройки документации.

б) Используйте сторонние утилиты (например, BigDump) для того, чтобы разбить файл перед загрузкой.

в) Если у вас есть прямой shell доступ, используйте MySQL для импорта файлов напрямую. Вы можете это сделать с помощью команды "source":
source filename.sql

2. Если файл небольшой, но эти ошибки все же появляются, то вам может помочь вот эта тема на форуме.

Парсинг CSV-файла с помощью PHP


Далее — программные способы. Они подойдут вам, если вы хотите все это дело автоматизировать или если перед непосредственной вставкой вам нужно провести дополнительную обработку данных.

Если вы предполагаете работать с большими файлами, то так просто описанные ниже способы вам тоже могут не подойти. Вам нужно будет провести на подготовительном этапе действия, описанные в пункте об импорте больших файлов через PHPMyAdmin.


Использование fgetcsv


array fgetcsv (resource descriptor, int lenght [, string separator [, string offset]])


Функция построчно считывает данные, анализирует и возвращает поля в виде массива. Параметрами функции служат дескриптор файла, максимальная длина строки, разделитель полей и смещение. В случае ошибки или при достижении конца файла возвращается FALSE.

$columns = "`code`,`contract`,`price`,`amount`,`dat_time`,`trade_id`,`Nosystem`";
if ( ($handle_o = fopen($file_name, "r") ) !== FALSE ) {
while ( ($data_o = fgetcsv($handle_o, 1000, ";")) !== FALSE) {
$insertValues = array();
foreach( $data_o as $v ) {
$insertValues[]="'".addslashes(trim($v))."'";
}
$values=implode(',',$insertValues);
$sql = "INSERT INTO `sdelka_temp` ( $columns ) VALUES ( $values )";
mysql_query($sql) or die('SQL ERROR:'.mysql_error());
}
}
fclose($handle_o);


Если названия столбцов прописаны в первой строке CSV-файла (обычно именно так и бывает), то красивее будет написать так:

if ( ($handle_o = fopen($file_name, "r") ) !== FALSE ) {
// читаем первую строку и разбираем названия полей
$columns_o = fgetcsv($handle_o, 1000, ";");
foreach( $columns_o as $v ) {
$insertColumns[]="'".addslashes(trim($v))."'";
}
$columns=implode(',',$insertColumns);


while ( ($data_o = fgetcsv($handle_o, 1000, ";")) !== FALSE) {
$insertValues = array();
foreach( $data_o as $v ) {
$insertValues[]="'".addslashes(trim($v))."'";
}
$values=implode(',',$insertValues);
$sql = "INSERT INTO `sdelka_temp` ( $columns ) VALUES ( $values )";
mysql_query($sql) or die('SQL ERROR:'.mysql_error());
}

}
fclose($handle_o);


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

Библиотеки для импорта CSV


Если поискать в интернете, можно найти не одну библиотеку для импорта CSV. Они не очень отличаются по быстродействию, но раз уж специально разрабатывались для этих целей, то разработчики видели смысл в своей работе. Мне кажется, что под каждый конкретный случай у какой-то из библиотек может быть преимущество. Все зависит от данных в файле.

simplecsvimport (Скачать.)
Библиотека позволяет импортировать данные в базу.
Принцип: считывание данных построчно, разбор отдельно каждой строки (значения полей "подчищаются"; где надо символы экранируются), формирование sql-запроса. Предусмотрена возможность сохранения полученных insert-ов в виде дампа. Возможно настроить добавление первого пустого поля (нужно в случае, если в базе поле автоинкрементируется).

Quick CSV import
Эту библиотеку я не тестировала, но если кто протестирует — поделитесь опытом в комментариях, пожалуйста. Не зря же Quick вынесено в ее название :)
___

Чтобы быть в курсе обновлений блога, можно подписаться на RSS.

Статьи схожей тематики:



13 комментариев:

  1. Есть проще варианты. Можно воспользоваться встроенными ф-ями MySQL, вот статья - http://bit.ly/cIWS9l

    ОтветитьУдалить
  2. @maxnag, спасибо за дополнение :)
    А большие файлы таким способом импортируются без проблем?

    ОтветитьУдалить
  3. Анонимный29 мая 2010 г., 14:46

    Есть такая таблица, как вывести определённую колонку через echo ? Заранее спасибо!

    SUMLEV,state,county,PLACE,cousub,NAME,STATENAME,POPCENSUS_2000,POPBASE_2000,POP_2000,POP_2001,POP_2002,POP_2003,POP_2004,POP_2005,POP_2006,POP_2007,POP_2008
    040,01,000,00000,00000,Alabama,Alabama,4447100,4447355,4451687,4462832,4469906,4486598,4506574,4537299,4587564,4626595,4661900

    ОтветитьУдалить
  4. @Анонимный, проще простого. Считываете построчно в массив, как описано в статье, т.е.
    $data = fgetcsv($handle, 1000, ",");
    а потом обращаетесь к элементам массива $data. Первое значение будет $data[0] и так далее.

    Пример:
    while ( ($data = fgetcsv($handle, 1000, ","))!== FALSE) {
    echo $data[0].'<br>';
    }

    ОтветитьУдалить
  5. Анонимный21 июня 2010 г., 15:11

    Есть еще вариант: бесплатная и русская тулза для разработчиков "dbForge Studio for MySQL" :) Правда, там связь меню-действие не совсем логична.

    ОтветитьУдалить
  6. В примере скрипта с использованием fgetcvs ошибка:

    $insertValues="'".addslashes(trim($v))."'";

    т.к. $insertValues - массив, то добавлять в него значения надо используя квадратные скобки []:

    $insertValues[]="'".addslashes(trim($v))."'";

    При этом перед каждой итерацией - обнулять массив: $insertValues = array();

    А в целом, спасибо! Распарсил так огромные файлы ip-привязок к городам.

    ОтветитьУдалить
  7. SloNik, спасибо, что заметили, конечно же, это так. Исправила.

    ОтветитьУдалить
  8. Вообще-то делать множество запросов к базе при парсинге csv файла, это грех. Есть многострочный оператор вставки - подготовили данные, записали.

    ОтветитьУдалить
  9. Подскажите алгоритм. Как при импорте проверять, есть ли такая запись в бд или нет (обычный select по уник. полю)? Как узнать, какие записи удалить, если их нет в csv-файле? В обоих примерах, csv-файл загружается не в первый раз, т.е. записи в бд уже существуют и нужно проверить, какие обновить/добавить/удалить.

    ОтветитьУдалить
    Ответы
    1. Проверять наличие — да, по уникальному полю, иначе никак.

      Что касается удаления, тут я бы добавила еще одно поле, например, с датой последнего обновления. Берется запись, проверяется ее наличие, при необходимости данные в таблице меняются, а в то поле записывается время этой последней "ревизии". После того, как все записи подобным образом обработаны, можно в таблице удалить те записи, у которых в том поле дата/время меньше, чем на начало работы. Или не дату/время писать, а просто флаг какой-нибудь: 1 - запись есть в csv, 0 - нет (перед началом проверки обнулить значение этого поля у всех записей). Как-то так, может быть.

      Удалить
  10. а что вы скажете на такие значения как NULL или TIMESTAMP ?
    - тоже обернете в кавычки ?

    ОтветитьУдалить
  11. Спасибо, очень помогло.

    ОтветитьУдалить

Комментарии модерируются, вопросы не по теме удаляются, троллинг тоже.

К сожалению, у меня нет столько свободного времени, чтобы отвечать на все частные вопросы, так что, может, свой вопрос лучше задать на каком-нибудь форуме?

Поделиться