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

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

score 1 · Answer 1 · answered Mar 24 '14 at 04:16

1

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

answered Mar 24 '14 at 04:16

Revan

1,104
12
27

score 0 · Answer 2 · answered Mar 24 '14 at 04:09

0

You can just convert the value to DateTime:

dr["DateTime"] = Convert.ToDateTime(s[6]);

This would work but could be dangerous if you ever run into something that isn't a datetime. If that's possible in your situation, you would want to do something with DateTime.TryParse most likely.

answered Mar 24 '14 at 04:09

KJ3

5,168
4
33
53

thanks sir. My problem is in the txtfile where the data is coming from. It has headers as text. I want to ask if there is a way for me to skip the header and straight to the data? – Lucas Juan Mar 24 '14 at 04:15

Solomon Rutzky · Accepted Answer · 2014-03-24T04:29:41.083

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.

How can I do it sir? Sorry I am really a newbie. The table-valued parameter?? — Lucas Juan, Mar 24 '14 at 05:30
@LucasJuan: check out this article I wrote several years ago on the topic of Streaming Data into SQL Server From an App ( http://www.sqlservercentral.com/articles/SQL+Server+2008/66554/ ). The site requires registration but it is free. I have a full example of creating the TVP, using it in a Stored Proc, and calling it from C#. Follow the "Full Streaming" example. — Solomon Rutzky, Mar 24 '14 at 14:32

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

3 Answers3