MySQL LOAD DATA INFILE: работает, но непредсказуемый терминатор линии

MySQL имеет приятную функцию импорта CSV LOAD DATA INFILE.

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

..... кроме.... Я заранее не знаю, что такое терминатор конца строки.

В настоящее время мой код SQL выглядит примерно так:

LOAD DATA INFILE '{fileName}'
 INTO TABLE {importTable}
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 LINES
( {fieldList} );

Это отлично работает для некоторых файлов импорта.

Однако данные импорта поступают из нескольких источников. Некоторые из них имеют терминатор \n; другие имеют \r\n. Я не могу предсказать, какой из них у меня будет.

Есть ли способ использовать LOAD DATA INFILE, чтобы указать, что мои строки могут быть завершены либо с помощью \n, либо \r\n? Как я могу справиться с этим?

Ответ 1

Я бы только предварительно обработал его. Глобальный поиск/замена для изменения \r\n на\n, выполненный из инструмента командной строки как часть процесса импорта, должен быть простым и эффективным.

Ответ 2

Вы можете указать разделитель строк как "\n" и при необходимости удалить конечные разделители "\ r" из последнего поля во время загрузки.

Например -

Предположим, что у нас есть файл "entries.txt". Разделитель строк равен '\ r\n', и только после строки ITEM2 | CLASS3 | DATE2 разделитель равен '\n':

COL1  | COL2   | COL3
ITEM1 | CLASS1 | DATE1
ITEM2 | CLASS3 | DATE2
ITEM3 | CLASS1 | DATE3
ITEM4 | CLASS2 | DATE4

Операция CREATE TABLE:

CREATE TABLE entries(
  column1 VARCHAR(255) DEFAULT NULL,
  column2 VARCHAR(255) DEFAULT NULL,
  column3 VARCHAR(255) DEFAULT NULL
)

Наш запрос LOAD DATA INFILE:

LOAD DATA INFILE 'entries.txt' INTO TABLE entries
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(column1, column2, @var)
SET column3 = TRIM(TRAILING '\r' FROM @var);

Показать результаты:

SELECT * FROM entries;
+---------+----------+---------+
| column1 | column2  | column3 |
+---------+----------+---------+
| ITEM1   |  CLASS1  |  DATE1  |
| ITEM2   |  CLASS3  |  DATE2  |
| ITEM3   |  CLASS1  |  DATE3  |
| ITEM4   |  CLASS2  |  DATE4  |
+---------+----------+---------+

Ответ 3

Я предполагаю, что вам нужна информация только через mysql no ни одним языком программирования. Перед использованием данные загрузки скрывают формат формат Windows\r\n (CR LF), если у вас есть блокнот ++. Затем обработайте запрос Load Load. Убедитесь, что LINES TERMINATED BY '\ r\n'

Edit:

Поскольку редакторы часто не подходят для преобразования больших файлов. Для больших файлов следующая команда часто используется как для windows, так и для linux

1) Преобразование в формат windows в windows

TYPE [unix_file] | FIND "" /V > dos_file

2) Преобразование в формат Windows в linux

unix2dos  [file]

Другие команды также доступны

Файл формата Windows можно преобразовать в формат Unix, просто удалив все символы ASCII CR\r tr-d '\ r' inputfile > outputfile

grep -PL $'\r\n' myfile.txt # show UNIX format  style file (LF terminated)
grep -Pl $'\r\n' myfile.txt # show WINDOS format style file (CRLF terminated)

В linux/unix команда file обнаруживает тип используемого End-Of-Line (EOL). Таким образом, тип файла можно проверить с помощью этой команды

Ответ 4

Вы также можете посмотреть один из пакетов интеграции данных. Talend Open Studio имеет очень гибкие подпрограммы ввода данных. Например, вы можете обработать файл с помощью одного набора разделителей и поймать отклонения и обработать их другим способом.

Ответ 5

Если первая загрузка имеет 0 строк, выполните один и тот же оператор с другим терминатором линии. Это должно быть выполнено с некоторой базовой логикой подсчета.

По крайней мере, он остается в SQL, и если он работает в первый раз, когда вы выигрываете. И может вызвать меньшую головную боль, которая повторно сканирует все строки и удаляет определенный символ.

Ответ 6

Почему бы не сначала просто заглянуть, как заканчиваются линии?

$handle = fopen('inputFile.csv', 'r');

$i = 0;
if ($handle) {
    while (($buffer = fgets($handle)) !== false) {

        $s =  substr($buffer,-50);

        echo $s; 
        echo preg_match('/\r/', $s) ? 'cr ' : '-- ';
        echo preg_match('/\n/', $s) ? 'nl<br>' : '--<br>';          

        if( $i++ > 5)
            break;

    }

    fclose($handle);
}