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.
Example,
"abcd", "efgh\", "ijk"
it goes to table as col1 | col2 | col3 abcd | efghijk | null
where I want this to go as col1 | col2 | col3 abcd | efgh | ijk
any pointer would be helpful.
Thanks, Ashish
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.
The best solution is to properly escape backslashes in your CSV file, e.g.:
"abcd", "efgh\\", "ijk"
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.
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.
See MySQL LOAD DATA INFILE Syntax for more information about file format options.
Hope that helps.
