SqlBulkCopy From CSV to SQL Datatable

Question

I'm using SQL Server 2005, VB.NET 2005. I want to be able to import a very large excel file into a SQL table called "XYZ"

I've done this by doing the following:
1. Save the excel file as csv.(Using SaveAs XLCSV option)
2. Build a datatable "ABC" From CSV.(using ODBC Connection and Select * from '*'.csv command)
3. copy the datatable"ABC" into database table "xyz" (using sqlBulkCopy.WriteToServer())

It works fine without any error but when i checked my database i found that data type for some columns has been changed and hence it didn't copy some of the records.Any help would be appreciated

Answer 1

I think you would be better off creating the target table in SQL server and using T-SQL BULK INSERT to insert the CSV. The data types are then explicit in your table. Using an intermediate database runs the risk of inferring the wrong datatype, as well as redundantly copies the data.