SqlBulkCopy From CSV to SQL Datatable

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

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.

The schema.ini can have more than one csv file defined, like this:

[customer.csv]
Format=CSVDelimited
CharacterSet=ANSI
ColNameHeader=True
Col1=customerID Long
Col2=customerName Text Width 128
Col3=comments Memo

[order.csv]
Format=CSVDelimited
CharacterSet=ANSI
ColNameHeader=True
Col1=orderID Long
Col2=customerID Long
Col3=orderDate DateTime

[orderDetail.csv]
Format=CSVDelimited
CharacterSet=ANSI
ColNameHeader=True
Col1=orderID Long
Col2=itemID Long
Col3=Quantity Short

For more information on the schema.ini and supported data types, visit http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx