Question SqlBulkCopy Issue with Datetime fields

[iloveorangesoda]

I am trying the bulk import a csv file into a sql server 2005 table but I am getting errors with the datetime field. The csv file holds the date in the following format e.g. ccyymmdd - 20100401 and in the table the field is a datetime. I cannot change the file format or the table field format. The error when I bulk import is field is not a valid datetime. How can I resolve this? Sample code that I am using is as follows:

csv.Columns.Add("datetime", "currentEffDate")


bulkCopy = New SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran)

bulkCopy.DestinationTableName = "SqlTable"

bulkCopy.BulkCopyTimeout = 600


bulkCopy.ColumnMappings.Add("currentEffDate", "CurrentEffDate")


bulkCopy.WriteToServer(csv)

 

[Tom]

With sqlBulkCopy you dont need to write directly from file to server, you can instead import/read your file into dataset and then BulkCopy from the DataSet to the Database. I would suggest doing this and while reading the file into the dataset, converting the field to a DateTime datatype and then do the bulkcopy.

Using bcpSql As New SqlBulkCopy(con, SqlBulkCopyOptions.Default, trans)
    bcpSql.BatchSize = 0 
    bcpSql.DestinationTableName = "SqlTable"
    bcpSql.WriteToServer(dsMyDataSet.TableName)
End Using
trans.Commit()

 

[iloveorangesoda]

Thanks - Could you give me an example of how do write it to a dataset and then convert the date? I am new to all this.

Thanks

 

[Tom]

Take a look at the TextFieldParser class in the help file. It has an example of reading delimited files. The only addition you need to make is that actual adding of that line to your dataset rows.