Экспорт MySQL в outfile: CSV экранирование символов

У меня есть таблица таблиц расписаний с некоторыми распространенными feilds.

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

Есть больше, но это суть.

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

Это все работает со мной, пробирая тайм-листы с php и печатая строки в файл.

Проблема заключается в большой базе данных, которая может занять несколько часов, что неприемлемо. Поэтому я переписал его с помощью команды MySQL INTO OUTFILE, и он уменьшил ее до нескольких секунд, чтобы запустить, что было здорово.

Теперь проблема заключается в том, что я не могу избежать всех новых символов строки и т.д. в поле описания. Действительно, пользователь может набирать потенциально любую комбинацию символов здесь, включая возврат каретки/новые строки.

Это фрагмент кода MySQL, который у меня есть:

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

Но...

Когда я пытаюсь посмотреть источник выходного файла, в файле все еще существуют символы новой строки, поэтому импорт CSV для Excel разбивает все причудливые макросы и сводные таблицы, созданные мастером Excel.

Любые мысли о лучшем действии?

Ответы

Ответ 1

Я думаю, ваше выражение должно выглядеть так:

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

В основном без параметра FIELDS ESCAPED BY '""', OPTIONALLY ENCLOSED BY '"' выполнит трюк для полей описания и т.д., и ваши числа будут обрабатываться как числа в Excel (не строки, содержащие числа)

Также попробуйте позвонить:

SET NAMES utf8;

перед выбором outfile, который может помочь получить кодировку символов inline (все UTF8)

Сообщите нам, как вы поживаете.

Ответ 2

Вот что здесь работает: Имитирует Excel 2003 (Сохранить как формат 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';
  • Excel сохраняет \r\n для разделителей строк.
  • Excel сохраняет \n для символов новой строки в столбцах данных
  • Нужно заменить \r\n внутри ваших данных, иначе Excel подумает о начале следующей строки.

Ответ 3

Что произойдет, если вы попробуете следующее?

Вместо вашего двойного оператора REPLACE попробуйте:

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

Кроме того, я думаю, что это должно быть LINES TERMINATED BY '\r\n' вместо просто '\n'

Ответ 4

На самом деле, не видя выходной файл для подтверждения, я предполагаю, что вам нужно избавиться от значения FIELDS ESCAPED BY.

MySQL FIELDS ESCAPED BY, вероятно, ведет себя двумя способами, на которые вы не рассчитывали: (1) это только один символ, поэтому в вашем случае он, вероятно, равен только одной кавычке; (2) он используется перед каждым символом, который MySQL считает нужным экранировать, включая значения FIELDS TERMINATED BY и LINES TERMINATED BY. Это имеет смысл для большей части компьютерного мира, но это не тот способ, которым Excel удается избежать.

Я думаю, что ваш двойной REPLACE работает, и вы успешно заменяете буквальные символы новой строки пробелами (два пробела в случае символов новой строки в стиле Windows). Но если у вас есть какие-либо запятые в ваших данных (литералы, а не разделители полей), им предшествуют кавычки, которые Excel обрабатывает по-разному, чем MySQL. Если это так, то ошибочные символы новой строки, запускающие Excel, на самом деле являются символами новой строки, которые MySQL намеревался использовать в качестве ограничителей строки.

Ответ 5

Вероятно, это не поможет, но вы можете попробовать создать таблицу CSV с этим контентом:

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