Apr 13: T-SQL: Import csv in temp table
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:
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.
SELECT * INTO #TESTCSV
FROM OPENROWSET (
'Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\;HDR=YES',
'SELECT * FROM test.csv')
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.
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.
« previous page
(Page 1 of 1, totaling 1 entries)
next page »
