18

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 ... ) ?

Tim
  • 8,669
  • 31
  • 105
  • 183

3 Answers3

11

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')
Turnerj
  • 4,258
  • 5
  • 35
  • 52
TyT
  • 291
  • 2
  • 5
  • 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. – Tim May 02 '12 at 20:36
  • 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. – TyT May 02 '12 at 21:07
11

Annoying, I don't have the rep points yet to just comment, so I'll add an answer based on TyT's (that handle looks terrible in possessive, btw ...)

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)
SteveCinq
  • 1,920
  • 1
  • 17
  • 22
1

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.

Datanova Scientific
  • 19
  • 5