CSV文件中的反斜杠 [英] Back slash in the csv file

问题描述

在我的应用程序中，我正在通过sql中的"load data local infile"文件名命令将csv文件读取到DB中.如果反斜杠出现在某个字段中，则相邻字段会合并.将文件读入数据库时​​如何忽略反斜杠.

In my application I am reading a csv file into DB through "load data local infile" filename command in sql. In case when a back slash comes in one of the field the adjacent field get merged. How to ignore the back slash when reading a file into DB.

示例

"abcd"，"efgh \"，"ijk"

"abcd", "efgh\", "ijk"

它进入表 col1 | col2 | col3 abcd | efghijk |空

it goes to table as col1 | col2 | col3 abcd | efghijk | null

我想在哪里 col1 | col2 | col3 abcd | efgh | ijk

where I want this to go as col1 | col2 | col3 abcd | efgh | ijk

任何指针都会有所帮助.

any pointer would be helpful.

谢谢， 灰烬

推荐答案

默认情况下，LOAD DATA使用\作为转义字符.考虑您的输入:

By default LOAD DATA uses \ as the escape character. Consider your input:

"abcd", "efgh\", "ijk"

该序列\"被解释为原义的不带引号的引号，而不是反斜杠后加引号.

That sequence \" is interpreted as a literal non-enclosing quote, not a backslash followed by a quote.

最佳解决方案是正确地在CSV文件中转义反斜杠，例如:

The best solution is to properly escape backslashes in your CSV file, e.g.:

"abcd", "efgh\\", "ijk"

如果无法执行此操作，则可以通过在语句中添加ESCAPED BY ''来禁用LOAD DATA INFILE语句中的转义.这将阻止它将\识别为转义字符，但是请记住，它也会禁用输入文件中的所有其他转义序列.这也将导入efgh\，反斜杠将不会被忽略.

If you cannot do that, you can disable escaping in your LOAD DATA INFILE statement by adding ESCAPED BY '' to the statement. That will prevent it from recognizing \ as an escape character, but keep in mind it will disable all other escape sequences in your input file as well. That will also import efgh\, the backslash will not be ignored.

如果不能导入efgh\，则必须修复输入文件的格式，或者稍后在应用程序逻辑或其他SQL查询中删除尾随\.

If importing efgh\ is unacceptable then you will have to fix the format of your input file, or remove the trailing \ later on in your application logic or with another SQL query.

有关更多信息，请参见 MySQL LOAD DATA INFILE语法有关文件格式选项的信息.

See MySQL LOAD DATA INFILE Syntax for more information about file format options.

希望有帮助.

这篇关于CSV文件中的反斜杠的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！