CSV (or sheet in XLS) to SQL create (and insert) statements with .Net?

Question

Does anyone have a technique for generating SQL table create (and data insert) commands pragmatically from a CSV (or sheet in a .xls) file?

I've got a third party database system which I'd like to populate with data from a csv file (or sheet in a xls file) but the importer supplied can't create the table structure automatically as it does the import. My csv file has lots of tables with lots of columns so I'd like to automate the table creation process as well as the data importing if possible but I'm unsure about how to go about generating the create statement...

Answer 1

In SQL server it is as easy as

SELECT * INTO NewTablenNmeHere
FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet1$]')

Answer 2

BULK INSERT CSVTest FROM 'c:\csvtest.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

Answer 3

Unfortunately I'm using an SQLEngine for embedded systems so it does not support BULK INSERT or OLEDB datasources, which is why I was thinking of taking the sql statement generation approach.

Answer 4

Thinking about SQLMenace's answer a bit more would there be any way to do that import and then save out the new table as SQL statements? And would there be any way to do it with out having to have the server e.g. just with OLEDB locally?