How to create and populate a table in a single step as part of a CSV import operation?

Question

I am looking for a quick-and-dirty way to import CSV files into SQL Server without having to create the table beforehand and define its columns.

Each imported CSV would be imported into its own table.

We are not concerned about data-type inferencing. The CSV vary in structure and layout, and all of them have many many columns, yet we are only concerned with a few of them: street addresses and zipcodes. We just want to get the CSV data into the SQL database quickly and extract the relevant columns.

I'd like to supply the FieldTerminator and RowTerminator, point it at the CSV, and have the utility do the rest. Is there any way to create the table and populate it, all in one step, using BULK INSERT and/or OpenRowset(BULK ... ) ?

Trilemma

Answer 1

Referencing SQLServerPedia, I think this will work:

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

select TerritoryID
      ,TotalSales
      ,TotalCost
INTO CSVImportTable
from openrowset('MSDASQL'
               ,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}'
               ,'select * from C:\csvtest.CSV')

Jempty

Thank you. I had to use Microsoft Text Driver instead of Microsoft Access Text Driver, but the import worked. However I did get this error message ( though the import worked despite it): Msg 15123, Level 16, State 1, Procedure sp_configure, Line 79 The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option. – Trilemma

You have to run the 'RECONFIGURE' for the advanced options before you can configure the ad-hoc option. There should be another GO after the first RECONFIGURE. I'll re-edit. – Jempty

This code is specific to the data/scenario in the article, and isn't particularly useful aside from that as you have to explicitly declare the columns you want to insert. – Calgary

Answer 2

The worker code needed a double "\" instead of a single for me to avoid a "file not found" error. And you don't have to specify the fields; they will be inferred from the first row of the file:

select *
into   CsvImportTable
from   openrowset(
           'MSDASQL',
           'Driver={Microsoft Access Text Driver (*.txt, *.csv)}',
           'select * from C:\\csvtestfile.csv')

I had no problems with the Access driver.

UPDATE: If you have trouble with the types being inferred incorrectly, insert a few rows at the top of the file with data of the type you want in the table so you get, say text -> VARCHAR instead of text-> INT and then delete those rows after the import.

As the final icing, add a PK to the table so you can manipulate the data - delete the dummy rows, etc:

alter table CsvImportTable add Id int identity(1, 1)

Cardenas

This answer add any new solution, and doesn't directly answer the question OP asked. It should have been a comment on an existing answer (if you don't have the reputation needed to comment, "posting an answer instead" is not the right path forward). – Calgary

Answer 3

Updated answer if you're using SQL Server Management Studio 17.

Right click on Database -> Tasks -> Import Flat File...

It will automatically infer the first row of the data as the column names. It should automatically pick up the terminators. You will get the option to set primary keys, allowing nulls, and specify data types for the columns as well.

Blende