Insert .csv file to SQL Server using OpenRowSet

Question

I have a .csv file, called File1 that I would like insert into my already existing table, called Table1 within SQL Server. The .csv file has the following structure and values:

 ID        Location        Name        Date

 2         New York        Sally       20191010
 3         California      Ben         20191110

My table within SQL server has the same structure:

 ID         Location        Name         Date

 1          Houston         Kelly        20200810

I wish for output to look like the value below, I wish for the date to be in order as well.

ID     Location    Name     Date

1      Houston     Kelly    20200810
2      New York    Sally    20191010
3      California  Ben      20191110

I am doing this:

INSERT INTO dbo.Table1(Microsoft.ACE.OLEDB12.0; Database = 'Data', 
'SELECT * FROM OpenRowSet' Dir = C:\downloads, 'SELECT * FROM File1.csv')

I am still researching to see what it is I am doing wrong with the above command. Any suggestion is appreciated

Amin Pashna · Accepted Answer · 2020-09-30 00:31:20Z

1

hope this helps:

BULK INSERT Table_1
FROM 'C:\Folder\file.csv'
WITH
(
FIELDTERMINATOR = ',',  --CSV field delimiter
ROWTERMINATOR = '0x0a',   --Use to shift the control to next row
FORMAT = 'CSV'
)

answered Sep 30, 2020 at 0:31

Amin Pashna

1551 gold badge1 silver badge7 bronze badges

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

10 Comments

Lynn Over a year ago

Ok thanks, I will try. Is this better than doing the INSERT command?

Amin Pashna Over a year ago

I'm pretty sure there are different methods to insert from csv file into database, but I like BULK insert better because it can be minimally logged operation so would be faster

Lynn Over a year ago

Hi, I tried this but it is saying: Operating system error code 5(Access is denied.) I am researching this

Amin Pashna Over a year ago

this error is because sql does not have access to the folder you have the csv file, move it from Downloads to another folder

Lynn Over a year ago

Ok, I created new folder and same error. I am still researching this

|

Collectives™ on Stack Overflow

Insert .csv file to SQL Server using OpenRowSet

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related