2

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!

DJs
  • 176
  • 1
  • 2
  • 11
  • From the Excel side, in general, CSV files should be **imported**, not **opened** in Excel. If you have the newer wizard, you can create a query that will properly assign data types each time you refresh it. If you are using the legacy wizard, you will have to assign those data types each time (or provide your client with a macro). – Ron Rosenfeld Aug 06 '20 at 10:28

2 Answers2

1

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.

cco
  • 5,873
  • 1
  • 16
  • 21
1

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

K4M
  • 1,030
  • 3
  • 11