Seems pretty simple to do... Just execute a query like this one:
SELECT * INTO #TESTCSV
FROM OPENROWSET (
'Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\;HDR=YES',
'SELECT * FROM test.csv')
In my case I was getting the following error message, which got me baffled for quite some time:
Msg 492, Level 16, State 1, Line 1
Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "test#csv.NoName" is a duplicate.
This really didn't make any sense to me. There were no duplicate column names in the csv file and surely there were no columns named 'NoName', nor did I forget to name a column...
Then it came to me: what if
UNICODE isn't parsed correctly? Maybe that could cause empty columns headers? The file was exported by ReportViewer and I had not looked at the encoding of the resulting csv file. Guess what!? The encoding was UNICODE. So, after saving the file in
ANSI encoding, the query executed like a charm.
