Проблема разрыва строки из CSV в MySQL

Я импортирую файл .csv в MySQL, и все работает нормально, за исключением разрывов строк, которые находятся в файле.

Одна из моих строк .csv выглядит так:

42,E-A-R™ Classic™ Earplugs,ear,images/ear/classic.jpg,5%,"Proven size, shape, and foam
3M's most popular earplug
Corded and uncorded in a variety of individual packs
NRR 29 dB / CSA Class AL",312-1201,,"E-A-R™ Classic™ Uncorded Earplugs, in Poly Bag",310-1001,,E-A-R™ Classic™ Uncorded Earplugs in Pillow Pack,311-1101,,"E-A-R™ Classic™ Corded Earplugs, in Poly Bag"

Шестое поле должно переходить в новую строку при вызове, но этого не происходит. При импорте файла .csv я выбираю строки , заканчивающиеся на \r. Я пробовал \n и авто, но безуспешно.

Странно то, что поле выглядит правильно в базе данных со всеми соответствующими разрывами. Если я вручную войду чтобы вставить разрывы строк в phpMyAdmin, он печатает правильно. Каждое поле также имеет значение UTF-8.

Есть какие-нибудь идеи по этому поводу? Спасибо.

Редактировать: вот инструкция MySQL

LOAD DATA LOCAL INFILE '/tmp/php89FC0F' REPLACE INTO TABLE `ohes_flyer_products`
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r'
Author: Carson, 2011-01-13
Source

5 answers

Может быть, вы могли бы использовать fgetcsv для разбора каждой строки csv в массив, а затем выгрузить этот массив в базу данных?

Что-то вроде 

$fd = fopen($csvfile, "r");
while ($line = fgetcsv($fd))
{
    $sql = sprintf("INSERT INTO tablename (...) VALUES ('%s', ...)", $line[0], ...);
    $res = mysql_query($sql);
}

Примечание 1: код не готов к производству, проверьте SQL-инъекции!

Примечание 2: пожалуйста, используйте подготовленные операторы, так как их использование значительно ускорит процесс (или сделайте один многорядный оператор вставки).

Примечание 3: оберните все в транзакцию.

 3
Author: Carlos Campderrós, 2011-02-21 15:27:39 
LOAD DATA LOCAL INFILE '/tmp/php89FC0F' REPLACE INTO TABLE `ohes_flyer_products`
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
 8
Author: Krunal, 2011-11-24 16:40:06

Ваш CSV-файл обладает некоторыми качествами, которые вы могли бы использовать.

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

Зная это, вот некоторые вещи вы можете попробовать:

  1. С помощью такой программы, как UltraEdit (или Notepad++), и ее функций поиска/замены (которые включают обработку регулярных выражений):

    • Найдите все возвраты каретки, которым предшествует кавычка, и замените их уникальным символом или строкой. Я предлагаю использовать символ канала "|", но сначала убедитесь, что они нигде не используются в файле CSV. Они будут представлять собой окончание записи.
    • Затем замените все возвраты каретки пробелами. Это будет приведите ваши поля с нежелательными возвратами каретки в соответствие с другими данными.
    • Наконец, замените все специальные символы конца записи на возврат каретки. Конечным результатом, в котором присутствуют только возвраты каретки, являются индикаторы окончания записи.

  2. Учитывая, что возврат каретки отображается в поле, заключенном в разделитель (кавычки), вы можете указать, что механизм импорта должен учитывать только разделители полей и записей вне цитат. (Синтаксис ФАЙЛА ЗАГРУЗКИ ДАННЫХ MySQL) В частности, посмотрите на параметр ENCLOSED BY 'char'. Поскольку не во всех ваших полях используется разделитель, вам нужно будет указать OPTIONALLY. Теоретически вы должны иметь возможность указать, как создается CSV-файл, и вам не нужно анализировать его заранее. Однако я придерживаюсь мнения, что возвраты каретки в поле, вероятно, следует удалить, чтобы текст был правильно обернут при выводе в новом контексте.

 1
Author: JYelton, 2011-02-08 21:01:01

Ваш CSV-файл кажется нестандартным, но это часто реальность работы с наборами данных клиентов.

Поскольку такие инструменты, как оператор ЗАГРУЗКИ ДАННЫХ MySQL, предназначены для обработки только идеального варианта использования, я обнаружил, что для работы с нестандартными наборами данных, подобными этому, требуется код.

Один из способов справиться с этим - сначала очистить CSV-файл, заменив разрывы строк в середине поля специальной уникальной строкой (например, ===MIDFIELD_LINE_BREAK===). Затем я бы написал пользовательский синтаксический анализатор CSV на языке сценариев (Python, Ruby, PHP, Perl и т.д.).

В вашем анализаторе CSV выполните итерацию по строкам в файле. Для каждой строки:

  • Замените символы \n или \r обратно на символы ===MIDFIELD_LINE_BREAK===.
  • Создайте и выполните инструкцию INSERT.
 0
Author: Kyle Wild, 2011-01-13 17:36:05

Это сработало для меня:

$query = <<<EOT

LOAD DATA LOCAL INFILE '$file' REPLACE INTO TABLE `$table`
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\\'
LINES TERMINATED BY '\\\n'
IGNORE 1 ROWS;

EOT;

Мне пришлось изменить ответ @Krunal из-за ошибок, добавив несколько дополнительных косых черт.

Кстати, строка Unix возвращает используемые здесь значения.

DOS:     \\\r\\\n
Old Mac: \\\r
Unix:    \\\n
 0
Author: bloodyKnuckles, 2016-06-09 19:27:25