MySql command "load data infile" execution error in C#

Question

I am trying to import a text file into MySql database using C# code but getting errors. My table structure is: Mysql database table

and the C# code that I'm executing is:

 fileQuery =
                "load data infile '{0}' into table dgl.deliveries fields terminated by '\t' lines terminated by \r\n' (@ImagePath, Delivery_Note, Shipment_Number, @Delivery_Date, Deliver_To_Code, Deliver_To_Name, Sold_To_Code, Sold_To_Name, Material_Number, Doctype) set Delivery_Date = tr_to_date(@Delivery_Date, '%d/%m/%Y'), ImagePath = Concat('USERFILES/', @ImagePath)";

            string q = string.Format(fileQuery,fileName);

            MySqlConnection conn = new MySqlConnection(dglConnection.ConnectionString);
            MySqlCommand command = conn.CreateCommand();
            command.CommandText = q;
            conn.Open();
            command.ExecuteNonQuery();

and the error is:

An exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.DLL but was not handled in user code

Additional information: 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 '%d/%m/%Y'), ImagePath = Concat('USERFILES/', @ImagePath)' at line 2

The following is a line from source input file:

123.pdf 802661341   1061611 18/02/2015 00:00:00 22280   ABC LIMITED 22280   XYZ LIMITED 30679795 30744488   DELIVERY NOTE   1

Can you should us the actual data you are trying to insert, which is causing the problem ? — neildt, Feb 23 '15 at 10:10
So what is this error relating to '%d/%m/%Y'), ImagePath = Concat('USERFILES/', @ImagePath)' at line 2' — neildt, Feb 23 '15 at 10:14

score 0 · Answer 1 · edited May 23 '17 at 12:06

Your problem is that Your Date that you are passing from your C# code is 18/02/2015. Mysql only excepts a date in the format YYYY-MM-DD. You need to adjust that data so that it formats to the way Mysql will except a date if you want to store it as a date.

I actually wrote a stored procedure that you maybe able to use (or at least get an idea of what needs to be done): Here is the link.

Also when in doubt just refer to dev.mysql its a great resource also.

MySql command "load data infile" execution error in C#

1 Answers1