С проблемой обработки больших CSV-файлов на PHP в первый раз я столкнулась недавно. На PHP я вообще мало программирую, только если возникают задачи написать что-либо конкретно на этом языке.
В предыдущей статье были рассмотрены . Там же я отметила, что работа с большими файлами требует особого подхода. Основным ограничением для импорта большого объема данных является время выполнения скрипта, которое задается хостером (как правило 30 секунд).
Мне необходимо было именно автоматизировать процесс полного импорта. Перед вставкой в таблицу значения полей, полученные из scv-файла, требовали анализа и дополнительной обработки.
Когда я прочитала в описании утилиты BigDump (в предыдущей статье я на нее ссылалась) о принципе работы:
The script executes only a small part of the huge dump and restarts itself. The next session starts where the last was stopped. (Перевод: Скрипт выполняет лишь небольшую часть SQL-команд из файла и перезапускает сам себя. В следующий раз импорт начинается с того места, в котором скрипт прервал свою работу.)
я поняла, что мне обязательно нужно попробовать такое решение. Поиски в инете чего-то похожего окончились успешно.
$file_name = $_GET["path"];
$conn = mysql_connect ("localhost", "username", "pass")
or die ("Соединение не установлено!");
@mysql_select_db("db_name") or die ("Соединение не установлено!");
if (($handle_f = fopen($file_name, "r")) !== FALSE)
{
// проверяется, надо ли продолжать импорт с определенного места
// если да, то указатель перемещается на это место
if(isset($_GET["ftell"])){
fseek($handle_f,$_GET["ftell"]);
}
$i=0;
if(isset($_GET["x"])){
$x=$_GET["x"];
} else {
$x = 0;
}
// построчное считывание и анализ строк из файла
while (($data_f = fgetcsv($handle_f, 1000, ";"))!== FALSE) {
$insert_q = "insert into temp1 (code,contract,price,amount,dat_time,is_op) values ".
" (\"".$data_f."\",\"".$data_f."\",\"".$data_f."\",\"".$data_f."\",\"".$data_f."\",\"0\")";
@mysql_query($insert_q);
If(!strstr($i/5000,".")){
print "Importing record #: ".$x."
";
flush();
ob_flush();
}
If($i==20000){
print "";
exit;
}
$x++;
$i++;
Fclose($handle_f);
} else {$err = 1; echo "Не получилось открыть файл";}
В параметре path при вызове скрипта передается путь к файлу, из которого надо производить импорт. В скрипте происходит импорт определенного количества строк (в примере - 20000), после чего он перезапускает сам себя с параметрами, среди которых кроме названия файла передается указатель на то место, с которого продолжать импорт (ftell ).
Я протестировала этот скрипт на файле размером 60 Mb. Отработал он правильно, все проимпортировал. Но время работы, все-таки, хотелось бы уменьшить.
В той же ветке форума, откуда я стырила это решение, обсуждалось, что ускорить работу скрипта при импорте данных в базу можно, заменив одиночные инсерты групповыми.
Команда INSERT, использующая VALUES, может быть использована для вставки сразу нескольких рядов. Чтобы сделать это, перечислите наборы значений, которые вам надо вставить. Пример:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
Апгрейдив скрипт на групповую вставку, получила и вправду более подходящий по быстродействию результат. Но думаю, что на этом пока рано останавливаться, буду искать дальше.
Несправедливо было бы обойти вниманием комментарий maxnag-а к предыдущему посту и не упомянуть о возможности
Я искал одно и то же, не используя какой-то неподдерживаемый класс PHP. Excel CSV не всегда использует разделители цитат и избегает цитат с использованием "", потому что алгоритм, вероятно, был возвращен 80 или что-то в этом роде. Посмотрев на несколько парсеров.csv в разделе комментариев на PHP.NET, я видел те, которые даже использовали обратные вызовы или код eval"d, и они либо не работали, как необходимо, либо просто не работали вообще. Итак, я написал для себя свои подпрограммы, и они работают в самой базовой конфигурации PHP. Ключи массива могут быть либо числовыми, либо именованными как поля, заданные в строке заголовка. Надеюсь это поможет.
Function SW_ImplodeCSV(array $rows, $headerrow=true, $mode="EXCEL", $fmt="2D_FIELDNAME_ARRAY") // SW_ImplodeCSV - returns 2D array as string of csv(MS Excel .CSV supported) // AUTHOR: [email protected] // RELEASED: 9/21/13 BETA { $r=1; $row=array(); $fields=array(); $csv=""; $escapes=array("\r", "\n", "\t", "\\", "\""); //two byte escape codes $escapes2=array("\r", "\n", "\t", "\\", "\""); //actual code if($mode=="EXCEL")// escape code = "" { $delim=","; $enclos="""; $rowbr="\r\n"; } else //mode=STANDARD all fields enclosed { $delim=","; $enclos="""; $rowbr="\r\n"; } $csv=""; $i=-1; $i2=0; $imax=count($rows); while($i < $imax) { // get field names if($i == -1) { $row=$rows; if($fmt=="2D_FIELDNAME_ARRAY") { $i2=0; $i2max=count($row); while(list($k, $v) = each($row)) { $fields[$i2]=$k; $i2++; } } else //if($fmt="2D_NUMBERED_ARRAY") { $i2=0; $i2max=(count($rows)); while($i2<$i2max) { $fields[$i2]=$i2; $i2++; } } if($headerrow==true) { $row=$fields; } else { $i=0; $row=$rows;} } else { $row=$rows[$i]; } $i2=0; $i2max=count($row); while($i2 < $i2max)// numeric loop (order really matters here) //while(list($k, $v) = each($row)) { if($i2 != 0) $csv=$csv.$delim; $v=$row[$fields[$i2]]; if($mode=="EXCEL") //EXCEL 2quote escapes { $newv = """.(str_replace(""", """", $v))."""; } else //STANDARD { $newv = """.(str_replace($escapes2, $escapes, $v))."""; } $csv=$csv.$newv; $i2++; } $csv=$csv."\r\n"; $i++; } return $csv; } function SW_ExplodeCSV($csv, $headerrow=true, $mode="EXCEL", $fmt="2D_FIELDNAME_ARRAY") { // SW_ExplodeCSV - parses CSV into 2D array(MS Excel .CSV supported) // AUTHOR: [email protected] // RELEASED: 9/21/13 BETA //SWMessage("SW_ExplodeCSV() - CALLED HERE -"); $rows=array(); $row=array(); $fields=array();// rows = array of arrays //escape code = "\" $escapes=array("\r", "\n", "\t", "\\", "\""); //two byte escape codes $escapes2=array("\r", "\n", "\t", "\\", "\""); //actual code if($mode=="EXCEL") {// escape code = "" $delim=","; $enclos="""; $esc_enclos=""""; $rowbr="\r\n"; } else //mode=STANDARD {// all fields enclosed $delim=","; $enclos="""; $rowbr="\r\n"; } $indxf=0; $indxl=0; $encindxf=0; $encindxl=0; $enc=0; $enc1=0; $enc2=0; $brk1=0; $rowindxf=0; $rowindxl=0; $encflg=0; $rowcnt=0; $colcnt=0; $rowflg=0; $colflg=0; $cell=""; $headerflg=0; $quotedflg=0; $i=0; $i2=0; $imax=strlen($csv); while($indxf < $imax) { //find first *possible* cell delimiters $indxl=strpos($csv, $delim, $indxf); if($indxl===false) { $indxl=$imax; } $encindxf=strpos($csv, $enclos, $indxf); if($encindxf===false) { $encindxf=$imax; }//first open quote $rowindxl=strpos($csv, $rowbr, $indxf); if($rowindxl===false) { $rowindxl=$imax; } if(($encindxf>$indxl)||($encindxf>$rowindxl)) { $quoteflg=0; $encindxf=$imax; $encindxl=$imax; if($rowindxl<$indxl) { $indxl=$rowindxl; $rowflg=1; } } else { //find cell enclosure area (and real cell delimiter) $quoteflg=1; $enc=$encindxf; while($enc<$indxl) //$enc = next open quote {// loop till unquoted delim. is found $enc=strpos($csv, $enclos, $enc+1); if($enc===false) { $enc=$imax; }//close quote $encindxl=$enc; //last close quote $indxl=strpos($csv, $delim, $enc+1); if($indxl===false) { $indxl=$imax; }//last delim. $enc=strpos($csv, $enclos, $enc+1); if($enc===false) { $enc=$imax; }//open quote if(($indxl==$imax)||($enc==$imax)) break; } $rowindxl=strpos($csv, $rowbr, $enc+1); if($rowindxl===false) { $rowindxl=$imax; } if($rowindxl<$indxl) { $indxl=$rowindxl; $rowflg=1; } } if($quoteflg==0) { //no enclosured content - take as is $colflg=1; //get cell // $cell=substr($csv, $indxf, ($indxl-$indxf)-1); $cell=substr($csv, $indxf, ($indxl-$indxf)); } else// if($rowindxl > $encindxf) { // cell enclosed $colflg=1; //get cell - decode cell content $cell=substr($csv, $encindxf+1, ($encindxl-$encindxf)-1); if($mode=="EXCEL") //remove EXCEL 2quote escapes { $cell=str_replace($esc_enclos, $enclos, $cell); } else //remove STANDARD esc. sceme { $cell=str_replace($escapes, $escapes2, $cell); } } if($colflg) {// read cell into array if(($fmt=="2D_FIELDNAME_ARRAY") && ($headerflg==1)) { $row[$fields[$colcnt]]=$cell; } else if(($fmt=="2D_NUMBERED_ARRAY")||($headerflg==0)) { $row[$colcnt]=$cell; } //$rows[$rowcnt][$colcnt] = $cell; $colcnt++; $colflg=0; $cell=""; $indxf=$indxl+1;//strlen($delim); } if($rowflg) {// read row into big array if(($headerrow) && ($headerflg==0)) { $fields=$row; $row=array(); $headerflg=1; } else { $rows[$rowcnt]=$row; $row=array(); $rowcnt++; } $colcnt=0; $rowflg=0; $cell=""; $rowindxf=$rowindxl+2;//strlen($rowbr); $indxf=$rowindxf; } $i++; //SWMessage("SW_ExplodeCSV() - colcnt = ".$colcnt." rowcnt = ".$rowcnt." indxf = ".$indxf." indxl = ".$indxl." rowindxf = ".$rowindxf); //if($i>20) break; } return $rows; }
Bob теперь может вернуться к своим речам
Достаточно высока, заказчики просят сделать импорт товаров из их прайс листов. Им необходимо чтобы была возможность не только добавлять новый товар на сайт, но и обновлять цены и количество на складе уже существующих товаров.
PHP как известно не умеет работать excel-файлами стандартными средствами. Конечно есть множество различных классов, которые могут читать и записывать информацию в excel, но с ними возникает достаточно много проблем (об этом мы напишем в следующих статьях).
Куда проще и удобней работать с csv-файлами .
Для примера сделаем небольшую программку. Она будет обрабатывать csv-файл сотрудников компании OX2.ru.
Создадим новую таблицу в Excel, содержащую следующие поля:
Сохраним таблицу как csv-файл, с разделителем поля «;».
У нас должен получиться такой файл:
"Иванов А.А.";"Программист компании OX2.ru";89255552332 "Сидоров А.Е.";"Дизайне компании OX2.ru";89161231212 "Пирожков А.Б.";"Арт-директор OX2.ru";84951232121 "Кулибин Б.А.";"Менеджер OX2.ru";89031233333
Ниже приведен исходный код с подробным описанием.
Код слелан на ООП с использованием классов , и при небольшой доработки может многократно использоваться в различных приложениях. Если вы не привыкли к программированию на классах, а используете функции или еще что-то, то настоятельно рекомендуем переучиваться))
Так же в коде используется генерация исключений (Exception), о них читайте в следующих наших статьях.
_csv_file = $csv_file; //Записываем путь к файлу в переменную } else { //Если файл не найден то вызываем исключение throw new Exception("Файл "$csv_file" не найден"); } } public function setCSV(Array $csv) { //Открываем csv для до-записи, //если указать w, то ифнормация которая была в csv будет затерта $handle = fopen($this->_csv_file, "a"); foreach ($csv as $value) { //Проходим массив //Записываем, 3-ий параметр - разделитель поля fputcsv($handle, explode(";", $value), ";"); } fclose($handle); //Закрываем } /** * Метод для чтения из csv-файла. Возвращает массив с данными из csv * @return array; */ public function getCSV() { $handle = fopen($this->_csv_file, "r"); //Открываем csv для чтения $array_line_full = array(); //Массив будет хранить данные из csv //Проходим весь csv-файл, и читаем построчно. 3-ий параметр разделитель поля while (($line = fgetcsv($handle, 0, ";")) !== FALSE) { $array_line_full = $line; //Записываем строчки в массив } fclose($handle); //Закрываем файл return $array_line_full; //Возвращаем прочтенные данные } } try { $csv = new CSV("ox2.csv"); //Открываем наш csv /** * Чтение из CSV (и вывод на экран в красивом виде) */ echo "
CSV до записи:
"; $get_csv = $csv->getCSV(); foreach ($get_csv as $value) { //Проходим по строкам echo "Имя: " . $value . ""; echo "Должность: " . $value . "
"; echo "Телефон: " . $value . "
"; echo "--------
"; } /** * Запись новой информации в CSV */ $arr = array("Антонов Б.А.;Админ OX2.ru;89031233333", "Колобков В.Б.;Босс OX2.ru;89162233333"); $csv->setCSV($arr); } catch (Exception $e) { //Если csv файл не существует, выводим сообщение echo "Ошибка: " . $e->getMessage(); } ?>
Основную работу с CSV файлами на себя берет класс CSV , Он имеет следующие методы:
setCSV(Array $csv) - записывает данные в csv-файл. Данные должны быть переданы массивом. Метод может дозаписывать csv файл, и создавать новый (читайте в описании).
Метод getCSV читает данные из csv файла, и возвращает двумерный массив следующего вида:
Array ( => Array ( => Иванов А.А. => Программист компании OX2.ru => 89255552332) => Array ( => Сидоров А.Е. => Дизайне компании OX2.ru => 89161231212) => Array ( => Пирожков А.Б. => Арт-директор OX2.ru => 84951232121) => Array ( => Кулибин Б.А. => Менеджер OX2.ru => 89031233333))
Пример достаточно простой, несмотря на то что получилось достаточно много кода.
Импорт данных из csv файлов будет полезен не только для обновление товаров в интернет-магазинах, но и для подгрузки/обновлении какой-либо информации на обычный сайт.
Например, мы разрабатывали сайт управляющей компании, и им требовалось ежемесячно публиковать информацию по счетчикам горячей и холодной воды. Все данные у них храняться в excel файлах. И тут прекрасно подошел csv формат!
При этом стоимость создания сайта останется не изменой, а функционал и автоматизация сайта будет на уровень выше чем у конкурентов.
Я использую класс parseCSV для чтения данных из файлов csv. Это может обеспечить большую гибкость при чтении csv-файла.
это не проверено … но что-то вроде этого должно сделать трюк:
$row = 1; if (($handle = fopen("xxxxxxxxx.csv", "r")) !== FALSE) { while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { $num = count($data); echo "
$num fields in line $row:
$fp = fopen("ReadMe.csv","r") or die("can"t open file"); print "
| ".$csv_line[$i]." | "; } print "
Подробнее
Попробуй это….
В PHP часто бывает полезно читать CSV-файл и получать доступ к его данным. Вот где функция fgetcsv () пригодится, она будет считывать каждую строку файла CSV и присваивать каждое значение массиву ARRAY. Вы можете определить разделитель в функции, а также просмотреть документы PHP для fgetcsv () для получения дополнительных опций и примеров.
Function readCSV($csvFile){ $file_handle = fopen($csvFile, "r"); while (!feof($file_handle)) { $line_of_text = fgetcsv($file_handle, 1024); } fclose($file_handle); return $line_of_text; } // Set path to CSV file $csvFile = "test.csv"; $csv = readCSV($csvFile); echo "
"; print_r($csv); echo "";
Один лайнер для разбора CSV-файла в массив с помощью str_getcsv .
$csv = array_map("str_getcsv", file("qryWebsite.csv"));
Чтобы создать запрос базы данных, который сразу импортирует все значения в базу данных:
$query = "INSERT INTO tbl_name (a,b,c) VALUES " . implode(",", array_map(function($params) use (&$values) { $values = array_merge((array) $values, $params); return "(" . implode(",", array_fill(0, count($params), "?")) . ")"; }, $csv));
Это создаст подготовленное заявление с заполнителями вопросительных знаков, например:
INSERT INTO tbl_name (a,b,c) VALUES (?,?,?),(?,?,?),(?,?,?),(?,?,?)
А переменные $values будут одномерным массивом, который содержит значения для оператора. Одно из предостережений здесь состоит в том, что файл csv должен содержать менее 65 536 записей (максимальное количество заполнителей).
Один лайнер для разбора CSV-файла в массив
$csv = array_map("str_getcsv", file("data.csv"));
Вы можете попробовать приведенный ниже код. Он отлично подходит для меня. У меня есть комментарий, чтобы сделать его более понятным. Вы можете получить ссылку на этот код.
"; $id++; } echo "
"; fclose($handle); } //close the connection mysql_close($conn); в Entered data having id = " .$id. " successfully
"; $id++; } echo "
Congratulation all data successfully inserted
"; fclose($handle); } //close the connection mysql_close($conn);
