MySQL LOAD DATA INFILE with special deliminators

Question

I have data that has fields deliminated by \u0001 and records deliminated by \u0002\n. I want to use LOAD DATA INFILE to import all of the data at once into a MySQL database. I know that \u0001 can be written as X'01', but I am not sure how to write `\u0002\n'.

This is what I have so far.

LOAD DATA LOCAL INFILE 'myfile.txt' INTO TABLE my_table 
    FIELDS TERMINATED BY X'01'
    LINES TERMINATED BY '\n'
    (col1, col2, col3);

The above SQL statement works but I'm afraid that the \u0002 might be imported into the database along with the data.

I know that \u0002 can be written as X'02' but I'm not too sure how I can combine X'02' with \n.

Any suggestions?

huh, my suggestion is go with your current method, then do a replacement of `\u00002` after the import is completed (so, you don't have to wait for perfect solution) — ajreal, May 23 '11 at 03:28

score 8 · Answer 1 · edited May 08 '15 at 16:04

It looks like you are trying to implement iTunes EPF :), X'01' + '\n' => X'020a' So your query can be written as:

LOAD DATA LOCAL INFILE 'myfile.txt' INTO TABLE my_table 
    FIELDS TERMINATED BY X'01'
    LINES TERMINATED BY X'020a'
    (col1, col2, col3);

If you are really working on implementing an iTunes EPF integration, you can use next query:

LOAD DATA LOCAL INFILE '<path to your file>' INTO TABLE `<table name>` CHARACTER SET UTF8MB4 FIELDS TERMINATED BY X'01' LINES TERMINATED BY X'020a' IGNORE 34 LINES;

Such as apple provide text data in 4-byte UTF8 which can be stored in MySQL just with UTF8MB4 charset (I mean to correct port all text records). First 34 lines will be ignored ( as should — its is 'legal policy' data). and need to remove last line from file. (I remove it with script before porting)

Hopes it helps someone.

MySQL LOAD DATA INFILE with special deliminators

1 Answers1