SqlBulkCopy throws a System.FormatException when WriteToServer (DataTable) is fired

Question

I am currently writing a method to read data from a CSV file and import into an SQL table.

DataTable dt = new DataTable(); String line = null; int i = 0; while ((line = reader.ReadLine()) != null) { String[] data = line.Split(','); if (data.Length > 0) { if (i == 0) { foreach (object item in data) { DataColumn c = new DataColumn(Convert.ToString(item)); if (Convert.ToString(item).Contains("DATE")) { c.DataType = System.Type.GetType("System.DateTime"); } else { c.DataType = System.Type.GetType("System.String"); } dt.Columns.Add(c); } i++; } else { DataRow row = dt.NewRow(); for (int j = 0; j < data.Length; j++) { if (dt.Columns[j].DataType == System.Type.GetType("System.DateTime")) { row[j] = Convert.ToDateTime(data[j]); } else { row[j] = data[j]; } } dt.Rows.Add(row); } } } SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[Constant.CONNECTION_STRING_NAME].ConnectionString); SqlBulkCopy s = new SqlBulkCopy(con); s.DestinationTableName = "abc"; con.Open(); s.WriteToServer(dt); 

The problem when running this method exception is always raised on s.WriteToServer (dt); speaking

System.FormatException: The string was not declared a valid DateTime. There is an unknown word starting at index 0.

I debugged and saw that all the data was loaded correctly in the DataTable. Below is an example of a data line in a CSV file

 DATA_VENDOR_ID,DATA_VENDOR_SUB_ID,DATA_VENDOR_CLIENT_ID,DATA_VENDOR_ACTIVITY_CODE,ACTIVITY_NAME,EFFECTIVE_DATE,ACTIVITY_LEVEL1,ACTIVITY_LEVEL2,ACTIVITY_LEVEL3,ACTIVITY_LEVEL4,ACTIVITY_LEVEL5,PARTICIPANT_ID,DATA_VENDOR_ALT_ID,FILE_CREATION_DATE,INC_VALUE V01,,22097,ABCD01,Physical Activity,10/01/2010,Entertain Kiosk,ABCD - EFG 54,30,,AB01,W1234567891,,08/07/2006,100 ACTIVITY_NAME, EFFECTIVE_DATE, ACTIVITY_LEVEL1, ACTIVITY_LEVEL2, ACTIVITY_LEVEL3, ACTIVITY_LEVEL4, ACTIVITY_LEVEL5, PARTICIPANT_ID, DATA_VENDOR_ALT_ID, FILE_CREATION_DATE, INC_VALUE DATA_VENDOR_ID,DATA_VENDOR_SUB_ID,DATA_VENDOR_CLIENT_ID,DATA_VENDOR_ACTIVITY_CODE,ACTIVITY_NAME,EFFECTIVE_DATE,ACTIVITY_LEVEL1,ACTIVITY_LEVEL2,ACTIVITY_LEVEL3,ACTIVITY_LEVEL4,ACTIVITY_LEVEL5,PARTICIPANT_ID,DATA_VENDOR_ALT_ID,FILE_CREATION_DATE,INC_VALUE V01,,22097,ABCD01,Physical Activity,10/01/2010,Entertain Kiosk,ABCD - EFG 54,30,,AB01,W1234567891,,08/07/2006,100 

and my SQL table schema:

 RowID int Unique/AutoIncrement DataVendorId varchar(32) DataVendorSubId varchar(32) DataVendorClientId varchar(32) DataVendorActivityCode varchar(32) ActivityName varchar(64) EffectiveDate datetime ActivityLevel1 varchar(253) ActivityLevel2 varchar(253) ActivityLevel3 varchar(253) ActivityLevel4 varchar(253) ActivityLevel5 varchar(253) ParticipantID varchar(32) DataVendorAltId varchar(32) FileCreationDate datetime IncValue varchar(5) CreatedDate datetime optional/allow null ModifiedDate datetime optional/allow null 

Answer 1

The first problem I see is that you will have problems with the RowID column; I expect that he is trying to compensate your data for one column at the moment - he does not know that you omit it. You can either encounter mappings, or (in your data table) add a RowID column (at index 0) - but note that SQL Server ignores the values ​​if you do not enable the insert for identification.

Perhaps try a more explicit date / time conversion:

 row[j] = DateTime.ParseExact(data[j], "dd/MM/yyyy", CultureInfo.InvariantCulture); 

Please note that I cannot determine the data if it is dd / MM or MM / dd, so you may need to configure it.

Answer 2

I had a similar problem when the columns were turned off, and as soon as I determined the display, no problem:

 using (SqlBulkCopy sbc = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["SQLDatabase"].ConnectionString, SqlBulkCopyOptions.KeepIdentity)) { sbc.DestinationTableName = "DestinationTable"; sbc.ColumnMappings.Add("foo", "bar"); sbc.ColumnMappings.Add("hello", "world"); sbc.ColumnMappings.Add("col1", "col2"); sbc.WriteToServer(data); } 

In addition, I have a List to DataTable converter extension that I use to convert my list.

 public static DataTable ToDataTable<T>(this IEnumerable<T> data) { PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T)); DataTable table = new DataTable(); foreach (PropertyDescriptor prop in properties) table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType); foreach (T item in data) { DataRow row = table.NewRow(); foreach (PropertyDescriptor prop in properties) row[prop.Name] = GetDataValue(prop.GetValue(item)); table.Rows.Add(row); } return table; } 

The GetDataValue () method clears my data for MinValue dates, etc.:

 private static object GetDataValue(object value) { if (value == null || (value.GetType() == typeof(DateTime) && Convert.ToDateTime(value) == DateTime.MinValue) || (value.GetType() == typeof(DateTime) && Convert.ToDateTime(value) < Convert.ToDateTime("01/01/1753"))) { return DBNull.Value; } return value; } 

Answer 3

The fields in your data file do not match the table, i.e. you are trying to insert a DataVendorId into the RowId column, which throws an exception, since you cannot convert varchar(32) to int ,

Move the identity column to the end of the table. Now Bulk Insert will be able to match all fields until it reaches the identity column.