Using MySQL LOAD DATA INFILE with nonprintable character delimiters

Question

I have some vendor data that has the SOH (ASCII character 1) as the field delimiter and STX (ASCII character 2) as the record delimiter. Is it possible to load this data with LOAD DATA INFILE without pre-processing the file and replacing those characters with something more common?

Answer 1

You might try FIELDS TERMINATED BY _ascii 0x02. I don't know if it will work for LOAD DATA INFILE, but it works in SELECT (i.e., SELECT _ascii 0x61 yields 'a').

Answer 2

You can try just sending the ascii char directly inside the string literal.. if your connection doesn't have a charset or encoding assigned, then mysql may simply accept it as a valid string. You'd have to do it through a network connection, or piping data to the mysql client. I don't think you're going to be able to type that in at a console.

Answer 3

I got it.

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

Answer 4

If you're using mysqlimport the format for hex values in fields-terminated-by and lines-terminated-by etc is:

mysqlimport --local --user=username --password=secret --ignore-lines=4 --default-character-set=UTF8 --fields-terminated-by=0x01 --lines-terminated-by=0x02 --verbose databasename thefiletoimport