How do you use mysqlimport to parse a document that uses special characters as delimiters?
I've tried:
mysqlimport --local --fields-optionally-enclosed-by='\x254'
--fields-terminated-by='\x14' testdb messages.txt
as well as:
mysqlimport --local --fields-optionally-enclosed-by='\xFE'
--fields-terminated-by='\cT' testdb messages.txt
and I get :
mysqlimport: Error: 1083, Field separator argument is not what is expected; check the manua开发者_StackOverflow社区l, when using table: messages.
I've tried double quotes, no quotes as well as single quotes like above. Anyone know what the correct syntax should be?
According to the documentation (http://dev.mysql.com/doc/refman/5.0/en/load-data.html), only some escape sequences are available with mysqlimport, those are :
Character Escape Sequence
\0 An ASCII NUL (0x00) character
\b A backspace character
\n A newline (linefeed) character
\r A carriage return character
\t A tab character.
\Z ASCII 26 (Control+Z)
\N NULL
Therefore, if you got unusual line endings (like "\x02\n"), you have no other choice than pre formatting your file. Luckily, it's pretty easy with a bit of sed. For example, with a line ending "\x02\n" and field ending "\x01", you may use the following bash script :
#!/bin/sh
FILE="$1.tmp"
# Copying
cp $1 $FILE
# Removing comments from file
sed -i '/^#/d' $FILE
# Replacing field separator
sed -i 's/\x01/\x00%/g' $FILE
# Replacing lineends
sed -i ':a;N;$!ba;s/\x02\n/\x00\n/g' $FILE
Then shot the following mysqlimport
mysqlimport --fields-terminated-by="\0%" --lines-terminated-by="\0\n" [...] $FILE
Shall work nicely.
精彩评论