CODEWITHSUNDEEP
mysqlload-data-infiledelimiternon-printable
danb
danb

Reputation: 10379

Using MySQL LOAD DATA INFILE with nonprintable character delimiters

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?

Upvotes: 3

Views: 5046

Answers (5)

Feng Xu
Feng Xu

Reputation: 13

FIELDS TERMINATED BY X'01'

works for me

Upvotes: 0

jono2010
jono2010

Reputation: 719

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

Upvotes: 2

danb
danb

Reputation: 10379

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

Upvotes: 8

Ian Clelland
Ian Clelland

Reputation: 44132

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.

Upvotes: 0

Seth
Seth

Reputation: 46423

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

Upvotes: 1

Related Questions