Incorrect Format In Flat File CSV File

Question

Incorrect Format In Flat File CSV File

jn93 551

Hi I have SSIS package take source from DB to flat file Destination. However, the format I get in CSV file for REFERENCENO is in general and I try to convert the data type to text or numbers is still getting wrong (seems like data missing when convert to numbers). Apart from that, I also try do data conversion from string to text in ssis package, still didnt get the correct format in CSV. Anyone can please help? Please refer the image below for better overview of my problem.

Answer 1

ZoeHui-MSFT 20,101

Hi @jn93 ，

You can add a apostrophe in front to force Excel to show it as text instead of a numeric value.

Use SSIS Derived Column Transformation with below expression:

 "'" + <yourColumnName>

Or you may open the csv file with notepad, it will not show the Incorrect Format.

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.

ZoeHui-MSFT 20,101 Reputation points

2022-08-22T09:28:44.057+00:00

Do you have further question on this , could we offer more support?
If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)
jn93 551 Reputation points

2022-08-26T07:10:21.53+00:00

No more question. Thanks for the answer.

Answer 2

Imran Rauf 1

Have you tried the advance editor to control column types...

https://www.sqlshack.com/ssis-data-types-change-from-the-advanced-editor-vs-data-conversion-transformations/

jn93 551 Reputation points

2022-08-19T06:51:59.78+00:00

Got tried one too but still same
Imran Rauf 1 Reputation point

2022-08-19T14:43:07.993+00:00

HI @jn93

After opening the CSV file in excel, select the whole column and press Ctrl+1 then select cell format Text.

By default excel treats number values as Number type when importing csv file.

There are other ways for instance if you just change csv extension to .txt then when you import .txt file excel will ask you to define type of each column this should also solve the problem.

Incorrect Format In Flat File CSV File

1 additional answer

Activity