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:
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.
Posted by Twan Jacobs in SQL Comments: (0) Trackbacks: (0)

Trackbacks
Trackback specific URI for this entry

No Trackbacks

Comments
Display comments as (Linear | Threaded)

No comments


Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
You can use [geshi lang=lang_name [,ln={y|n}]][/geshi] tags to embed source code snippets.
Standard emoticons like :-) and ;-) are converted to images.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA
 
Submitted comments will be subject to moderation before being displayed.