CODEWITHSUNDEEP
mysqlescapingload-data-infile
Richard Herron
Richard Herron

Reputation: 10102

Handling escaped field separators with MySQL's LOAD DATA INFILE

I am using LOAD DATA INFILE to import into a MySQL table twenty |-delimited .dat files. But some of the | field terminators are escaped with a backslash. The second field below is an example:

1001604|EMERITUS CORP\WA\|SC 13G|1996-02-13|edgar/data/1001604/0000728757-96-000006.txt
1001604|EMERITUS CORP\WA\|SC 13G|1996-02-14|edgar/data/1001604/0000903949-96-000038.txt

I get an error because the last field clashes with the DATE type declared for the next to last field. I can open the .dat file and escape the escape, but is there a better way?

I could use a stream editor to double all backslashes, but this seems like a bad idea. Can I safely change the FIELDS ESCAPED BY option to something other than "\", or is that a bad idea? Thanks!

Here is my LOAD DATA INFILE command:

LOAD DATA INFILE 'C:/users/richard/research/data/edgar/masterfiles/master_1996.dat'
    INTO TABLE edgar.master
    FIELDS TERMINATED BY '|'
    IGNORE 1 LINES;

Upvotes: 0

Views: 2223

Answers (1)

Richard Herron
Richard Herron

Reputation: 10102

Adding ESCAPED BY '' to my FIELDS clause allows the query to complete without error. I will update if I find that this caused a silent fail.

Upvotes: 4

Related Questions