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?

Answer 1

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.