Flat file connection manager exports all values into column A (CSV file)

Question

When using a flat file connection in a SSIS package in VS, all the different values go into column A, instead of being separated into different columns. The preview in the flat file manager dialogue box looks OK. I read a suggestion to use ; as a column delimiter, and open the CSV file created using excel, but that did not do the job. Any suggestions please?

To get a CSV file exported with each value in its own column and not all values in column A separated by the delimiter.

Answer 1

You need to use Excel to convert the text to columns because you used ; and not comma (Excel does comma automatically.

Then

Then select semicolin:

Answer 2

If I understand you correctly, there is no problem.

You are creating a Delimited Flat File and you chose the delimiter of a semicolon. If you open the resulting output file with a text editor (Notepad) you'll see data like

Col1;Col2;Col3
ABCD;DEFG;HIJK
LMNO;PQRS;TUVW

If the data looks like that, then you have successfully created a delimited file.

The "problem" comes from Excel making assumptions about the data. Excel sees a file with .CSV, Comma Separated Values, extension and as part of reading that data in, splits it into columns based on the comma character and finding none, it presents the data as you see it in one column.

If you change the file extension to .txt, Excel walks you through a brief wizard to understand how it should interpret the data.

Here I unchecked the previous value and select semicolon

Here you can see Excel understands the columns and I have what I expected to see.

The challenge around using a different file extension is the user needs to either Right Click on the file and choose Open with Excel or Open Excel and then Open a file, change the default filter from All Excel Files to All Files or Text Files and then pick the correct option.

Or, you change your SSIS package to use the comma delimiter, leave the extension as .csv and Excel will correctly interpret the data when it loads.

If you have embedded data, like an Address of "100 Main St, Kansas City, MO" you'd need to modify your Flat File Connection Manager to indicate how you'd like to handle embedded delimiters. Otherwise, you'd end up with something like

Col1,Col2,Col3,Address
ABCD,DEFG,HIJK,100 Main St,Kansas City,MO
LMNO,PQRS,TUVW,Antartica

That'd result in 6 columns on row 2 instead of the intended 4.