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

from Recent Questions - Stack Overflow https://ift.tt/2ShhE3H
https://ift.tt/eA8V8J