Can C# MYSQL MySqlBulkLoader convert data types on the fly?

Question

I have a Comma Separated Value text file read and parsed into a C# List with four strings. One string has the string date format of:

"20090501 00:00:00.308"

Another string contains a float datatype of something like 10.00001.

I want to use the following C# code from How to bulk insert into MySQL using C#

   var bl = new MySqlBulkLoader(connection);
   bl.TableName = "mytable"; 
   bl.FieldTerminator = ","; 

   bl.LineTerminator = "\r\n";
   bl.FileName = "myfileformytable.csv";
   bl.NumberOfLinesToSkip = 1;

   var inserted = bl.Load();

   Debug.Print(inserted + " rows inserted.");

The problems is I want to convert the CSV string data types to the proper MySQL datatypes of DECIMAL and DATETIME. Can I use the above source code example and still convert on the fly using it? If so, how can it be done. If not, do I still have to use SQL insert one at a time and convert on each individual insert?

Answer 1

The problem is the format stored inside the CSV file is 'dd/MM/yyyy' however in MySQL it is formatted as 'yyyy/MM/dd' so I believe this is now the problem. You find a solution here https://social.msdn.microsoft.com/Forums/vstudio/en-US/1a280038-4c0f-4fb1-950d-1529269a1c73/issue-with-datetime-format-in-bulkloader-program?forum=csharpgeneral

Here is my new generating csv file methode :

public static void CreateCSVfile(DataTable dt, string strFilePath)
    {

        #region Export Grid to CSV



        // Create the CSV file to which grid data will be exported.

        StreamWriter sw = new StreamWriter(strFilePath, false);

        // First we will write the headers.

        //DataTable dt = m_dsProducts.Tables[0];

        int iColCount = dt.Columns.Count;

        for (int i = 0; i < iColCount; i++)

        {

            sw.Write(dt.Columns[i]);

            if (i < iColCount - 1)

            {

                sw.Write(",");

            }

        }

        sw.Write(sw.NewLine);

        // Now write all the rows.
        Type _datetype = typeof(DateTime);
        foreach (DataRow dr in dt.Rows)

        {

            for (int i = 0; i < iColCount; i++)

            {

                if (!Convert.IsDBNull(dr[i]))

                {
                    sw.Write(
                        dt.Columns[i].DataType == _datetype
                            ? ((DateTime)dr[i]).ToString(CultureInfo.InvariantCulture.DateTimeFormat)
                            : dr[i].ToString());
                }

                if (i < iColCount - 1)

                {

                    sw.Write(",");

                }

            }

            sw.Write(sw.NewLine);

        }

        sw.Close();



        #endregion

    }