MySQL 导出到 outfile:CSV 转义字符

2021-11-20 00:00:00 excel sql mysql into-outfile

我有一个包含一些常见字段的时间表数据库表.

I've a database table of timesheets with some common feilds.

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

还有更多，但这就是要点.

There are more but thats the gist of it.

我在该表上运行了一个导出到 CSV 文件的通宵，以便为用户提供其数据的备份.它还用作带有一些自定义报告的宏 Excel 文件的数据导入.

I have an export running on that table to a CSV file overnight to give the user a backup of their data. It also is used as a data import for a macro Excel file with some custom reports.

这一切都适用于我使用 php 遍历时间表并将行打印到文件中.

This all works with me looping through the timesheets with php and printing the lines to a file.

问题在于大型数据库可能需要数小时才能运行，这是不可接受的.所以我用 MySQL INTO OUTFILE 命令重写了它，它将运行时间缩短到几秒钟，这很棒.

The problem is with a big database it can take hours to run which isn't acceptable. So I rewrote it with the MySQL INTO OUTFILE command and it reduced it down to a few seconds to run which was great.

现在的问题是我似乎无法转义描述字段中的所有换行符等.实际上，用户可以在此处键入任何字符组合，包括回车符/换行符.

The problem now is I can't seem to escape all the new line characters, etc., in the description field. Really, a user can type potentially any combination of characters in here including carriage returns/new lines.

这是我拥有的 MySQL 代码片段:

This is a snippet of the MySQL code I have:

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

可是……

当我尝试查看输出文件的源时，文件中仍然存在换行符，因此 Excel 的 CSV 导入破坏了 Excel 向导创建的所有花哨的宏和数据透视表.

When I try look at the source of the output file, newlines still exist in the file, therefore the CSV import for the Excel breaks all the fancy macros and pivot tables the Excel wizard has created.

对最佳行动方案有什么想法吗?

Any thoughts on a best course of action?

推荐答案

我认为你的陈述应该是这样的:

I think your statement should look like:

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 中的数字(不是由数字组成的字符串)

Mainly without the FIELDS ESCAPED BY '""' option, OPTIONALLY ENCLOSED BY '"' will do the trick for description fields etc and your numbers will be treated as numbers in Excel (not strings comprising of numerics)

也可以尝试调用:

SET NAMES utf8;

在选择输出文件之前，这可能有助于获得内联字符编码(所有 UTF8)

before your outfile select, that might help getting the character encodings inline (all UTF8)

告诉我们您的进展情况.

Let us know how you get on.

相关文章