outfile - MySQL LOAD DATA INFILE: funciona, pero terminador de línea impredecible

load infile csv mysql

Question

MySQL tiene una buena función de importación CSV LOAD DATA INFILE .

Tengo un gran conjunto de datos que se debe importar de CSV de forma regular, por lo que esta característica es exactamente lo que necesito. Tengo un script de trabajo que importa mis datos perfectamente.

..... excepto ... No sé de antemano cuál será el terminador de fin de línea.

Mi código SQL actualmente se ve algo como esto:

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

Esto funciona muy bien para algunos archivos de importación.

Sin embargo, los datos de importación provienen de múltiples fuentes. Algunos de ellos tienen el terminador /n ; otros tienen /r/n . No puedo predecir cuál tendré.

¿Hay alguna manera de usar LOAD DATA INFILE para especificar que mis líneas pueden terminar con /n o /r/n ? ¿Cómo trato con esto?

Answer 1

¿Por qué no primero echar un vistazo a cómo terminan las líneas?

$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);
}

Answer 2

Puede especificar el separador de línea como ''/ n'' y eliminar los separadores ''/ r'' finales si es necesario del último campo durante la carga.

Por ejemplo -

Supongamos que tenemos el archivo ''entries.txt''. El separador de línea es ''/ r / n'', y solo después de la línea ITEM2 | CLASS3 | DATE2 ITEM2 | CLASS3 | DATE2 ITEM2 | CLASS3 | DATE2 el separador es ''/ n'':

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

Declaración CREATE TABLE:

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

Nuestra consulta 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);

Mostrar resultados:

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

Answer 3

Puede usar las LÍNEAS QUE COMIENZAN para separar los finales de línea habituales en el texto y una nueva fila:

LOAD DATA LOCAL INFILE ''/home/laptop/Downloads/field3-utf8.csv''
IGNORE INTO TABLE Field FIELDS
TERMINATED BY '';''
OPTIONALLY ENCLOSED BY ''^''
LINES STARTING BY ''^''
TERMINATED BY ''/r/n''
(Id, Form_id, Name, Value)

Para los archivos CSV habituales con "caracteres adjuntos, será:

...
LINES STARTING BY ''"''
...

Answer 4

Si la primera carga tiene 0 filas, haga la misma declaración con el otro terminador de línea. Esto debería ser posible con alguna lógica básica de conteo.

Al menos permanece todo en SQL, y si funciona la primera vez que ganas. Y podría causar menos dolor de cabeza que al volver a escanear todas las filas y eliminar un carácter en particular.

Answer 5

Simplemente lo pre-procesaba. Una búsqueda / reemplazo global para cambiar / r / n a / n realizado desde una herramienta de línea de comandos como parte del proceso de importación debe ser simple y eficaz.

Answer 6

Suponiendo que necesita información solo a través de mysql no por cualquier lenguaje de programación. Antes de usar los datos de carga, convierta el formato a formato de Windows / r / n (CR LF) si tiene u notepad ++. Y luego procesar la consulta de carga de datos. Asegúrese de que las LÍNEAS TERMINADAS POR ''/ r / n''

Editar:

Dado que los editores a menudo no son adecuados para convertir archivos más grandes. Para archivos más grandes, el siguiente comando se usa a menudo tanto en windows como en linux

1) Convertir en formato windows en windows.

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

2) Convertir a formato windows en linux.

unix2dos [file]

Los otros comandos también disponibles.

Un archivo de formato de Windows se puede convertir a formato Unix simplemente eliminando todos los caracteres ASCII CR con tr -d ''/ r'' <archivo de entrada> archivo de salida

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)

En linux / unix, el comando de archivo detecta el tipo de fin de línea (EOL) utilizado. Así que el tipo de archivo se puede verificar usando este comando

Answer 7

También puede mirar en uno de los paquetes de integración de datos que hay. Talend Open Studio tiene rutinas de entrada de datos muy flexibles. Por ejemplo, podría procesar el archivo con un conjunto de delimitadores y capturar los rechazos y procesarlos de otra manera.