by Amol
14. December 2012 10:20
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 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]')