Using the SQL bulk copy in C# and deleting the string headers

Question

Using the SQL bulk copy in C# and deleting the string headers

c# datetime sql sqlbulkcopy sql-server

Question

The code below is what I use for inserting the data stored in txt file to sql:

 public void extract_data()
    {
        openFileDialog1.ShowDialog();
        DataTable dt = new DataTable();
        StreamReader sr = new StreamReader(openFileDialog1.FileName);
        string input;
        dt.Columns.Add(new DataColumn("Counter", typeof(string)));
        dt.Columns.Add(new DataColumn("Machine", typeof(string)));
        dt.Columns.Add(new DataColumn("Employee_Number", typeof(string)));
        dt.Columns.Add(new DataColumn("In_Out", typeof(string)));
        dt.Columns.Add(new DataColumn("DateTime", typeof(string)));
        while ((input = sr.ReadLine()) != null)
        {
            string[] s = input.Split(new char[] { '\t' });
            DataRow dr = dt.NewRow();
            dr["Counter"] = s[0];
            dr["Machine"] = s[1];
            dr["Employee_Number"] = s[2];
            dr["In_Out"] = s[5];
            dr["DateTime"] = s[6];
            dt.Rows.Add(dr);
        }

        using (SqlBulkCopy sqbc = new SqlBulkCopy(@"Data Source=DBASE;Network Library=DBMSSOCN;Initial Catalog=DB;User ID=sa;Password=123"))
        {
            sqbc.BatchSize = 10000;
            sqbc.BulkCopyTimeout = 10000;
            sqbc.ColumnMappings.Add("Counter", "Counter");
            sqbc.ColumnMappings.Add("Machine", "Machine");
            sqbc.ColumnMappings.Add("Employee_Number", "Employee_Number");
            sqbc.ColumnMappings.Add("In_Out", "In_Out");
            sqbc.ColumnMappings.Add("DateTime", "DateTime");
            sqbc.DestinationTableName = "tblExtract";
            sqbc.WriteToServer(dt);
        }
    }

As you can see I can't convert my column DateTime to datetime in sqlserver because of the header in the txtfile which we can classify as strings. The bulk copy will return an error because it cannot insert string in datetime format column so I was forced to declare my DateTime column to string to accommodate this. But I want to change it. I want to declare it to datetime. How can I ignore the header text while doing the sqlbulk copy? I want to skip the header text and go copying the data only

Lucas Juan

Popular Answer

Try this :

string format = "MM/dd/yyyy hh:mm:ss.fff";
DateTime d =DteTime.ParseExact(s[6],format,System.Globalization.CultureInfo.InvariantCulture);
dr["DateTime"]=d;

Change the format as you want like "MM/dd/yyyy" or "MM/dd/yyyy hh:mm" etc

tHiNk_OuT_oF_bOx

View more on Stack Overflow

Solomon Rutzky · Accepted Answer · 3/24/2014 4:29:41 AM

If you will always have a single header line that you want to skip, just read that row without processing it by wrapping your existing while() inside of an if().

if ((input = sr.ReadLine()) != null)
{
     // already read line #1 (i.e. header line),
     // now start processing data, if any lines exist
     while ((input = sr.ReadLine()) != null)
     {
        ...
     }
}

General Notes:

I hope the "sa" account in the connection string is just an example. If not, use another account. Using "sa" is a bad idea.
Depending on the size of the input file, this could be rather inefficient with memory since it needs to read the entire file into the DataTable first. You would be better off creating a TVP (table-valued parameter) in SQL Server, using it as an input parameter to a proc that does this insert by reading from the TVP, and updating this code so that as each line of the file is read and split on tabs, each line is sent to SQL Server so you never have more than 1 row of the input file in memory.

Using the SQL bulk copy in C# and deleting the string headers

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator