CODEWITHSUNDEEP
sql-serverexcelcsvssis
DJs
DJs

Reputation: 186

SSIS: CSV extracts opened in excel - some string values shown as date

I have a SSIS package that generates csv extracts.
there is column in the extract say Col1 which have values in the format integer/integer or just integer
e.g.
22
5/27
9/114

The flat file connection datatype of Col1 is String[DT_STR] width 100. col1 is loaded from SQL DB table column of datatype varchar(100).
The extracts are generated fine and everything looks ok when the extracts are opened in text editor.
But the client who receives the extracts opens it in excel and complains that some values are shown as date.
e.g. above values are shown as
22
May-27
9/114

I know about the existing issues when csv opened with excel.
Is there any option in ssis that can force the column shows the values as string even when opened in excel?
or any other possible solution for this issue can anyone please suggest.

Thanks!

Upvotes: 2

Views: 136

Answers (2)

K4M
K4M

Reputation: 952

If you could wrap the column with double quotes and put an = sign before it, Excel will show it as normal. Instead of 1/1, you write ="1/1" into CSV. See a sample in the screenshot below.

Example of how it looks in Excel

But, this has its own side effects: although it looks as intended inside Excel to the user, the actual data stored in the CSV file is now slightly different than what user would expect and that could be an issue if user edits the file manually or if this file is meant to be fed into another application for further processing. Your user may not realize this discrepancy.

If these side effects are not desired, then the only proper solution is to train the customer on how to import a CSV file into Excel rather than just opening a CSV file in Excel, which messes up data-formats. One way to enforce this is saving the file with a *.csv.txt extension instead of *.csv. That way, the user can't simply double click it to open it in Excel.

You can see a more in depth discussion here

Stop Excel from automatically converting certain text values to dates

Upvotes: 1

cco
cco

Reputation: 6281

If you open the file using "From Text/CSV" on the Data tab, it will correctly (in my limited tests) choose to set the column data type to Text. I don't see any way to use File/Open or double clicking in File Explorer and have Excel open the file as you want.
If the file is tab delimited, opening a blank worksheet, setting the column type to Text, then pasting the data in (from, e.g. Notepad) will be OK too.
I think the Data tab is the easiest way.

Upvotes: 1

Related Questions