outfile - exportar consulta mysql a txt con php

Exportación de MySQL a archivo externo: CSV escapando de los caracteres

Question

Tengo una tabla de base de datos de hojas de tiempo con algunos campos comunes.

id, client_id, project_id, task_id, description, time, date

Hay más, pero eso es lo esencial.

Tengo una exportación ejecutándose en esa tabla en un archivo CSV durante la noche para darle al usuario una copia de seguridad de sus datos. También se utiliza como una importación de datos para un archivo de macro Excel con algunos informes personalizados.

Todo esto funciona conmigo yendo a través de las hojas de tiempo con php e imprimiendo las líneas a un archivo.

El problema es con una gran base de datos que puede tardar horas en ejecutarse, lo que no es aceptable. Así que lo reescribí con el comando MySQL INTO OUTFILE y lo redujo a unos segundos para ejecutarse, lo que fue genial.

El problema ahora es que parece que no puedo escapar de todos los nuevos caracteres de línea, etc., en el campo de descripción. Realmente, un usuario puede escribir potencialmente cualquier combinación de caracteres aquí, incluidos los retornos de carro / nuevas líneas.

Este es un fragmento del código MySQL que tengo:

SELECT id,
client,
project,
task,
REPLACE(REPLACE(ifnull(ts.description,''''),''/n'','' ''),''/r'','' '') AS description,
time,
date
INTO OUTFILE ''/path/to/file.csv''
FIELDS ESCAPED BY ''""''
TERMINATED BY '','' ENCLOSED BY ''"''
LINES TERMINATED BY ''/n''
FROM ....

Pero...

Cuando intento ver el origen del archivo de salida, todavía existen nuevas líneas en el archivo, por lo tanto, la importación de CSV para Excel rompe todas las macros y tablas dinámicas que el asistente de Excel ha creado.

¿Alguna idea sobre el mejor curso de acción?

Answer 1

¿Qué pasa si pruebas lo siguiente?

En lugar de su doble declaración REPLACE , intente:

REPLACE(IFNULL(ts.description, ''''),''/r/n'', ''/n'')

Además, creo que deberían ser LINES TERMINATED BY ''/r/n'' lugar de simplemente ''/n''

Answer 2

Aquí está lo que funcionó aquí: Simula Excel 2003 (Guardar como formato CSV)

SELECT
REPLACE( IFNULL(notes, ''''), ''/r/n'' , ''/n'' ) AS notes
FROM sometables
INTO OUTFILE ''/tmp/test.csv''
FIELDS TERMINATED BY '','' ENCLOSED BY ''"'' ESCAPED BY ''"''
LINES TERMINATED BY ''/r/n'';

1. Excel guarda / r / n para separadores de línea.
2. Excel guarda / n para caracteres de nueva línea dentro de datos de columna
3. Tiene que reemplazar / r / n dentro de sus datos primero; de lo contrario, Excel pensará que es el comienzo de la próxima línea.

Answer 3

Creo que su declaración debería verse así:

SELECT id,
client,
project,
task,
description,
time,
date
INTO OUTFILE ''/path/to/file.csv''
FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY ''"''
LINES TERMINATED BY ''/n''
FROM ts

Principalmente sin la FIELDS ESCAPED BY ''""'' , OPTIONALLY ENCLOSED BY ''"'' hará el truco para los campos de descripción, etc. y sus números se tratarán como números en Excel (no cadenas que comprenden números)

También intente llamar:

SET NAMES utf8;

antes de seleccionar su archivo de salida, eso podría ayudar a obtener las codificaciones de caracteres en línea (todas UTF8)

Háganos saber cómo le va.

Answer 4

Probablemente no ayude, pero podría intentar crear una tabla CSV con ese contenido:

DROP TABLE IF EXISTS foo_export;
CREATE TABLE foo_export LIKE foo;
ALTER TABLE foo_export ENGINE=CSV;
INSERT INTO foo_export SELECT id,
client,
project,
task,
REPLACE(REPLACE(ifnull(ts.description,''''),''/n'','' ''),''/r'','' '') AS description,
time,
date
FROM ....

Answer 5

Sin ver tu archivo de salida para confirmación, supongo que debes deshacerte del valor FIELDS ESCAPED BY.

Es probable que FIELDS ESCAPED BY de MySQL se comporte de dos formas con las que no contaba: (1) solo pretende ser un personaje, por lo que en su caso probablemente sea solo una comilla; (2) se usa para preceder a cada personaje que MySQL cree que necesita escaparse, incluidos los valores de CAMINOS TERMINADOS POR y LÍMITES TERMINADOS POR. Esto tiene sentido para la mayoría del mundo de la informática, pero no es la forma en que Excel escapa.

Creo que su doble REPLACE está funcionando, y que está reemplazando con éxito las nuevas líneas literales por espacios (dos espacios en el caso de las líneas nuevas del estilo de Windows). Pero si tiene comas en sus datos (literales, no separadores de campo), estos van precedidos por comillas, que Excel trata de manera muy diferente a MySQL. Si ese es el caso, entonces las líneas nuevas erróneas que están tropezando con Excel son en realidad nuevas líneas que MySQL había pretendido como terminadores de línea.