CODEWITHSUNDEEP
c#mysqlsql
Carlos Yasuda
Carlos Yasuda

Reputation: 121

LOAD DATA INFILE: can't ignore 1st row while skipping columns?

I have an application which basically reads data from a csv file and insert it into a MySQL database.

Initially everything was going fine with this (my sql knowledge is a mess, but I'm trying):

LOAD DATA INFILE 'filename.csv' INTO TABLE tablename 
FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' IGNORE 1 LINES

But recently I had to validate which columns from the file that were going to be inserted into de database, so I tried something like this:

LOAD DATA INFILE 'filename.csv' INTO TABLE tablename FIELDS TERMINATED
BY ';' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (@col1,@col2,@col3,@ignore,@ignore)
SET col1= @col1, col2= @col2, col3= @col3

If I take off the "IGNORE 1 LINES" part, the command works fine, but I don't get why this is messing with everything else. I assume it's some syntax mistake, but I tried everything i could think off and still nothing works. Does anyone have a clue?

EDIT: If I keep the "IGNORE 1 LINES" I get the "You have an error with your SQL syntax; check the manual yadda yadda yadda to use near 'IGNORE 1 LINES' at line 1."

For testing, I'm using a simple file, here's the first 5 lines:

col1;col2;col3;col4;col5
test;test;test;test;test
test2;test2;test2;test2;test2
test3;test3;test3;test3;test3
test4;test4;test4;test4;test4

Upvotes: 2

Views: 1344

Answers (2)

Nunoxz
Nunoxz

Reputation: 11

I had the same issue. As it turned out, between IGNORE and LINES I somehow had an invisible character instead of just spaces[1].

Erasing the line and writing it again solved the issue.

[1] Aside from nonprinting control characters there are several Unicode punctuation characters that are invisible.

Upvotes: 1

peterm
peterm

Reputation: 92845

Syntactically your statement is correct and should work as expected.

mysql> create table tablename 
    -> (
    ->   col1 varchar(32), 
    ->   col2 varchar(32), 
    ->   col3 varchar(32), 
    ->   col4 varchar(32), 
    ->   col5 varchar(32)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> LOAD DATA INFILE '/tmp/filename.csv' 
    -> INTO TABLE tablename 
    ->   FIELDS TERMINATED BY ';' 
    ->   LINES TERMINATED BY '\n' -- I'm on a Mac so I'm using \n instead of \r\n
    ->   IGNORE 1 LINES 
    -> (@col1, @col2, @col3, @ignore, @ignore) 
    -> SET col1 = @col1, col2 = @col2, col3 = @col3
    -> ;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from tablename;
+-------+-------+-------+------+------+
| col1  | col2  | col3  | col4 | col5 |
+-------+-------+-------+------+------+
| test  | test  | test  | NULL | NULL |
| test2 | test2 | test2 | NULL | NULL |
| test3 | test3 | test3 | NULL | NULL |
| test4 | test4 | test4 | NULL | NULL |
+-------+-------+-------+------+------+
4 rows in set (0.00 sec)

Now since you're getting a syntax error my guess is that the problem is most likely not in the LOAD DATA statement itself but rather in C# code that you use to build and execute the query string.

Upvotes: 1

Related Questions