CODEWITHSUNDEEP
sqlsql-server
Lynn
Lynn

Reputation: 4408

Insert .csv file to SQL Server using OpenRowSet

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

Upvotes: 0

Views: 3082

Answers (1)

Amin Pashna
Amin Pashna

Reputation: 155

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'
)

Upvotes: 1

Related Questions