Clever Geek Handbook

MySQL LOAD DATA INFILE usage with non-printable character delimiters

I have some vendor data, there is SOH (ASCII character 1) as field separator and STX (ASCII character 2) as the record separator. Is it possible to load this data using LOAD DATA INFILE without preprocessing the file and replacing those characters with something more common?

+2


source to share


5 answers


I understood.



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


source


You can try FIELDS TERMINATED BY _ascii 0x02

. I don't know if this will work for LOAD DATA INFILE

, but it does work in SELECT

(i.e. SELECT _ascii 0x61

gives 'a').



+1


source


If you are using mysqlimport, the format for hexadecimal values ​​in fields ending in and ending with lines, etc:

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

+1


source


You can try just sending the ascii char directly inside the string literal .. if your connection doesn't have an encoding or encoding then mysql can just accept it as a valid string. You will need to do this over a network connection or transfer data to the mysql client. I don't think you will be able to type this into the console.

0


source


FIELDS CHECKED X'01 '

works for me

0


source






More articles:


All Articles