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?

woa, what a strange cirmumstance! :) – markus Sep 29 '09 at 16:42 — markus, Sep 29 '09 at 16:42
hah.. tell me about it... sometimes I hate vendors :) – danb Sep 29 '09 at 16:52 — danb, Sep 29 '09 at 16:52

score 8 · Accepted Answer · answered Sep 29 '09 at 17:20

8

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);

answered Sep 29 '09 at 17:20

danb

10,239
14
60
76

1

That's great that that works -- I didn't expect the parser to accept anything but a literal quote char after "TERMINATED BY". As a reference, the docs on this are at http://dev.mysql.com/doc/refman/5.1/en/hexadecimal-values.html – Ian Clelland Sep 29 '09 at 19:05
Miraculously, this still works, even with Aurora MySQL 5.8 on AWS. – ijoseph Apr 14 '20 at 22:15

score 1 · Answer 2 · answered Sep 29 '09 at 16:44

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').

answered Sep 29 '09 at 16:44

Seth

45,033
10
85
120

I was excited for a sec, but it didn't work.. LOAD DATA wants a string literal... I guess I need to pre-process.. thanks – danb Sep 29 '09 at 17:05

score 1 · Answer 3 · answered Jul 13 '10 at 08:57

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

score 0 · Answer 4 · answered Sep 29 '09 at 17:16

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.

score 0 · Answer 5 · answered Dec 02 '15 at 15:12

0

FIELDS TERMINATED BY X'01'

works for me

answered Dec 02 '15 at 15:12

Feng Xu

13
3

Using MySQL LOAD DATA INFILE with nonprintable character delimiters

5 Answers5

Linked