Error using load data local infile using c#

Question

I have this file in csv trying to import to using load data infile syntax. but I am stuck with this error in syntax. I tried every thing but no luck.Any help would be appreciated.

string strLoadData = "LOAD DATA LOCAL  INFILE 'C:/xampp/htdocs/explortest.csv' INTO  test.tickets FIELDS  terminated by ',' ENCLOSED BY '\"'  lines terminated by '\r\n' ignore 1 lines  ";

ERROR

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test.tickets FIELDS  terminated by ',' ENCLOSED BY '"'  lines terminated by '
'' at line 1

Max Yakimets · Accepted Answer · 2014-01-23 14:47:02Z

1

string strLoadData = "...";

The C# compiler will look for escape sequences in strLoadData and replace them with special characters. Thus your \r\n would be interpreted by C# as "new line" and "line feed" characters. But you need MySQL to read "\r\n" not "new line" and "line fead" characters - to apply it's own escape sequences interpretation.

Long story short: replace \r\n with \\r\\n

answered Jan 23, 2014 at 14:47

Max Yakimets

1,2358 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abdul Manaf · Accepted Answer · 2014-01-23 13:47:37Z

0

Add TABLE After INTO

LOAD DATA LOCAL  INFILE 'C:/xampp/htdocs/explortest.csv' 
INTO TABLE test.tickets FIELDS  terminated by ',' ENCLOSED BY '\"'  lines terminated by '\r\n' ignore 1 lines

answered Jan 23, 2014 at 13:47

Abdul Manaf

4,8883 gold badges29 silver badges34 bronze badges

Comments

miltos · Accepted Answer · 2014-01-23 14:02:03Z

0

Check the line terminates character in file. Are you sure that is \r\n? Maybe you have \n or \r as line termination on your file.

You can read more here. I'm copying a part of this page below:

Note

If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r'.

answered Jan 23, 2014 at 14:02

miltos

1,0191 gold badge6 silver badges10 bronze badges

Collectives™ on Stack Overflow

Error using load data local infile using c#

ERROR

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

ERROR

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related