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
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:
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