Load Delimited CSV file using ACE Driver

ACE driver will read the comma-delimited file without any issues. But if you have different delimiter it doesn’t work as expected.

Earlier we used to use FORMAT=Delimited(|) as a switch in connection string to load different delimiter CSV file.

To read the file with different delimiter, you need to create Schema.ini file in the same directory as the SCV file. And add the below contents in it.

[SampleFile.csv]

Format=Delimited(|)

ColNameHeader=False

Once you ready with the Schema.ini file run the below command to read the CSV file using ACE driver

SELECT TOP *

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\Temp\;HDR=No',

'SELECT * FROM [SampleFile.csv]')