CSV Import SQL

Question

Hi All,

I'm looking for some information on how MS Excel delimits CSV files as a default (CR)(LF), (CR), (LF) comma etc..

The issue that I am encountering is in regards to importing into SQL Server as no matter which combination of delimiters I use it does not preview correctly however, Excel appears to display the information without issue. I have checked the CSV in wordpad and there doesn't appear to be any sort of logic that I can see?

When importing into SQL Server via SSMS I do this via tasks, I select import data to use the import export wizard.

I choose the data source as flat file, then the file click next where I see the delimiter options. I know that there is a comma to separate the columns within the csv however, I have tried all the other options for the row delimiter and nothing appears to work.

When I open the same file within excel, everything is displayed as expected

Please can someone help?

Answer 1

Typically a CSV has the definition of , separates the columns and " (double quote) is the Text Qualifier and CRLF is the row delimiter. What can happen is if you do not put the delimiters in SQL Import (most often the " qualifier is the missing piece), then SQL won't be able to preview it correctly, but Excel will because it automatically does the standard CSV with ,"CRLF as appropriate delimiters.

So in the SQL Import make sure that you put the Text Qualifier in and see how it goes.

Answer 2

Hi @Rob Redders ,

Please use Flat File Source to extract csv file in Data Flow Task in ssis package.

And then set the qualifier character in Flat File Connection Manager.

Specify a text qualifier character. Each column can be configured to recognize a text qualifier.
The use of a qualifier character to embed a qualifier character into a qualified string is supported by the Flat File Connection Manager. The double instance of a text qualifier is interpreted as a literal, single instance of that string. For example, if the text qualifier is a single quote and the input data is 'abc', 'def', 'g'hi', the output data is abc, def, g'hi. However, an instance of a qualifier embedded in a qualified string causes the Flat File Source to fail with the error DTS_E_PRIMEOUTPUTFAILED.

Best Regards,
Mona