wake-up-neo.com

MySQL-Export in Outfile: CSV-Zeichen werden ignoriert

Ich habe eine Datenbanktabelle mit Arbeitszeittabellen mit einigen häufigen Fehlern.

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

Es gibt mehr, aber das ist der Kern.

Ich habe einen Export für diese Tabelle über Nacht in eine CSV-Datei, um dem Benutzer eine Sicherungskopie seiner Daten zu geben. Es wird auch als Datenimport für eine Makro-Excel-Datei mit einigen benutzerdefinierten Berichten verwendet. 

Das alles funktioniert, wenn ich die Timesheets mit PHP durchläuft und die Zeilen in eine Datei drucke.

Das Problem ist bei einer großen Datenbank, deren Ausführung einige Stunden dauern kann, was nicht akzeptabel ist. Also habe ich es mit dem MySQL-Befehl INTO OUTFILE umgeschrieben, und es wurde auf wenige Sekunden reduziert. Das war großartig.

Das Problem ist jetzt, dass ich scheinbar nicht alle neuen Zeilenzeichen usw. im Beschreibungsfeld entgehen kann. Wirklich kann ein Benutzer hier möglicherweise eine beliebige Kombination von Zeichen einschließlich Wagenrücklauf/neue Zeilen eingeben.

Dies ist ein Ausschnitt des MySQL-Codes, den ich habe:

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 ....

Aber...

Wenn ich die Quelle der Ausgabedatei anschaue, sind noch Newlines in der Datei vorhanden. Daher bricht der CSV-Import für Excel alle ausgefallenen Makros und Pivot-Tabellen, die der Excel-Assistent erstellt hat. 

Irgendwelche Gedanken zu einer besten Vorgehensweise?

39
Derek Organ

Ich denke, Ihre Aussage sollte so aussehen:

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

Hauptsächlich ohne die FIELDS ESCAPED BY '""'-Option führt OPTIONALLY ENCLOSED BY '"' den Trick für Beschreibungsfelder usw. aus, und Ihre Zahlen werden in Excel als Zahlen behandelt (keine Zeichenfolgen, die aus Ziffern bestehen).

Versuchen Sie auch mal anzurufen:

SET NAMES utf8;

bevor Sie Ihre Outfile auswählen, kann dies dazu beitragen, die Zeichenkodierungen inline zu erhalten (alle UTF8).

Lassen Sie uns wissen, wie es Ihnen geht.

66
Question Mark

Folgendes funktionierte hier: Simuliert Excel 2003 (Als CSV-Format speichern).

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 speichert\r\n für Zeilentrennzeichen.
  2. Excel speichert\n für Zeilenvorschubzeichen in Spaltendaten
  3. Müssen Sie zuerst\r\n in Ihren Daten ersetzen, da sonst Excel den Anfang der nächsten Zeile für möglich hält.
14
Felix Z

Ohne die Ausgabedatei tatsächlich zur Bestätigung zu sehen, muss der Wert FIELDS ESCAPED BY entfernt werden.

MySQLs FIELDS ESCAPED BY verhält sich wahrscheinlich auf zwei Arten, auf die Sie nicht gerechnet haben: (1) Es soll nur ein Zeichen sein. In Ihrem Fall entspricht es wahrscheinlich nur einem Anführungszeichen. (2) Es wird verwendet, um vor jedem Zeichen vorzugehen, von dem MySQL eine Flucht erwartet, einschließlich der Werte FIELDS TERMINATED BY und LINES TERMINATED BY. Dies ist für den Großteil der Computerwelt sinnvoll, aber es ist nicht die Art und Weise, wie Excel entkommt.

Ich denke, Ihr doppeltes REPLACE funktioniert, und Sie ersetzen buchstäbliche Zeilenumbrüche erfolgreich durch Leerzeichen (zwei Leerzeichen im Fall von Windows-Zeilen). Wenn in Ihren Daten jedoch Kommas (Literale, keine Feldtrennzeichen) vorhanden sind, werden Anführungszeichen vorangestellt, die Excel anders behandelt als MySQL. Wenn dies der Fall ist, dann sind die fehlerhaften Zeilenumbrüche, die Excel auslöst, tatsächlich Zeilenumbrüche, die MySQL als Zeilenabschlusszeichen vorgesehen hatte.

2
John Y

Was passiert, wenn Sie Folgendes versuchen?

Versuchen Sie statt Ihrer doppelten REPLACE-Anweisung Folgendes:

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

Ich denke auch, es sollte LINES TERMINATED BY '\r\n' statt nur '\n' sein.

2
devuxer

Wahrscheinlich hilft nichts, aber Sie könnten versuchen, eine CSV-Tabelle mit diesem Inhalt zu erstellen:

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 ....
0
Jorge Bernal