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

Answer

hope this helps:

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

Advertisement

Answer