How do I import a specific line from a text file into MySQL

Question

I'm building an app to import data from a text file to a database.

I want to import only specific lines from the text file. With Navicat I found that we could limit number of line when importing data.

I also found an article saying that we could limit it, so I tried this:

load data infile 'c:/coop/wkwk.txt' into table jo fields 
    terminated by '|' lines terminated by '\n' limit 10,100;

But I get an error in SQL syntax, what is the QUERY to make this work?

score 2 · Accepted Answer · answered Sep 10 '12 at 15:24

If you are using Unix/Linux you could try using a fifo buffer:

mkfifo /tmp/mysql.pipe 
# Select rows 101 to 110
sed -n 101,110p filename > /tmp/mysql.pipe & 
mysql –uusername -p -Ddatabasename -e "LOAD DATA LOCAL INFILE ' /tmp/mysql.pipe ' INTO TABLE tablename"

rm -f /tmp/mysql.pipe

score 1 · Answer 2 · answered Aug 10 '12 at 04:23

You can't use LIMIT on LOAD DATA INFILE. The only thing you can do, with regard to your question, is to skip a certain number of rows:

LOAD DATA INFILE ... INTO TABLE ... IGNORE 17 LINES

But you then have no way stopping until the end of file. See documentation.

How do I import a specific line from a text file into MySQL

2 Answers2