开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜