Import CSV into SQL (CODE)

Question

I want to import several CSV files automatically using SQL-code (i.e. without using the GUI). Normally, I know the dimensions of my CSV file. So, in many cases I create an empty table with, let say, x columns with the corresponding data types. Then, I import the CSV file into this table using BULK INSERT. However, in this case I don't know much about my files, i.e. information about data types and dimensions are not given.

To summerize the problem:

I receive a file path, e.g. C:...\DATA.csv. Then, I want to use this path in SQL-code to import the file to a table without knowing anything about it.

Any ideas on how to solve this problem?

You can't, the csv and the table columns have to match exactly. If you don't know the dimensions, you're not going to be able to define the target table. If you must, you could try to insert it all into a one-column table, and try to extract the columns from there. But you should probably write a clever tool for this. — HoneyBadger
– HoneyBadger, Commented Aug 4, 2017 at 12:00
I have stopped trying to use SQL to import CSV files and I have switched to Powershell. IMHO is simply an easier way to accomplish this task and I can leverage my .Net skills. — Mark Kram
– Mark Kram, Commented Aug 4, 2017 at 12:10
@MarkKram That sounds promising, however I don't know much about Powershell tbh. Is this a built-in function? Are there any examples I can use to realise this in PS? — SWDQ
– SWDQ, Commented Aug 4, 2017 at 12:18
sqlteam.com/article/fast-csv-import-in-powershell-to-sql-server — SAS
– SAS, Commented Aug 4, 2017 at 12:19
This, Then, I want to use this path in SQL-code to import the file to a table without knowing anything about it. appears to be an example of wanting to write code without knowing what it's supposed to accomplish. — Dan Bracuk
– Dan Bracuk, Commented Aug 4, 2017 at 12:20

Elliot Worth · Accepted Answer · 2020-07-14 14:01:22Z

5

Use something like this:

BULK INSERT tbl
FROM 'csv_full_path'
WITH
(
FIRSTROW = 2, --Second row if header row in file
FIELDTERMINATOR = ',',  --CSV field delimiter
ROWTERMINATOR = '\n',   --Use to shift the control to next row
ERRORFILE = 'error_file_path',
TABLOCK
)

If columns are not known, you could try with:

select * from OpenRowset

Or, do a bulk insert with only the first row as one big column, then parse it to create the dynamic main insert. Or bulk insert the whole file into a table with just one column, then parse that...

edited Jul 14, 2020 at 14:01

Elliot Worth

578 bronze badges

answered Aug 4, 2017 at 11:57

SAS

4,0852 gold badges33 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SWDQ Over a year ago

Thx; however, this code requires me to create table tbl beforehand, right? Can I do this automatically without creating a table manually for each CSV file?

SAS Over a year ago

You could do it dynamically, I added some suggestions.

SWDQ Over a year ago

Could you perhaps elaborate on how to parse a one big column. This solution sounds very intresting and it might solve my problem. Thank you very much.

SAS Over a year ago

Yes it should solve your problem. Use tsql substring and charindex functions. There are plenty of examples around. Or string_split function on 2016 :)

Rokuto · Accepted Answer · 2017-08-04 12:15:09Z

2

You can use OPENROWSET (documantation).

SELECT * 
INTO dbo.MyTable
FROM 
OPENROWSET(
   BULK 'C:\...\mycsvfile.csv',
   SINGLE_CLOB) AS DATA;

In addition, you can use dynamic SQL to parameterize table name and location of csv file.

answered Aug 4, 2017 at 12:15

Rokuto

8141 gold badge11 silver badges16 bronze badges

Collectives™ on Stack Overflow

Import CSV into SQL (CODE)

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related