I am trying to select from a csv file which has some data fields with leading zeros that need to be preserved. I need the header data so that I can join to the csv data to other tables in my database. This is going to be a recurring job with new csv files delivered daily.
My actual query is selecting fields from the csv or from other tables that it's joined to but, for simplicity's sake, I'll show examples using select *.
My Code is:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Text;Database=E:\ImportExport\Admissions\Slate_Test;CharacterSet=65001;HDR=Yes;IMEX=1', 'SELECT * FROM [MyFile.csv]') csv LEFT OUTER JOIN [SomeTable] ON csv.[Field] = [SomeTable].[Field]
I've tried different file formats with different results but none of which I'm looking for (Header names as shown in the file, leading zeros being preserved, data with commas not being pushed forward).
Issues: comma-delimited file with no quotes around text: Drops leading zeros even when I use CAST or CONVERT around the field. Also records with comma's in the data are not being read in correctly.
pipe-delimited with no quotes around text: Only reads the first column
comma-delimited with quoted text: Reads the data correctly but changes my header fields to [blank], F1, F2, etc.
At this point, the comma-delimited with quoted text is my best bet but I'll have to rewrite all of my fields names and joins to use the [blank], F1, F2 header names which I'd prefer not to do.
Hoping someone can help me! I've seen some references to using a schema.ini file but haven't found any tutorials to walk me through trying it out.
Thank you in advance!