MySQL导出到outfile:CSV转义字符 [英] MySQL export into outfile : CSV escaping chars

问题描述

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

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.

对最佳行动有何想法?

推荐答案

我认为您的声明应类似于:

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.

这篇关于MySQL导出到outfile:CSV转义字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！