leading zero to numbers in csv in ssis

Question

Hi All,

I am exporting data from the table to flat file destination(.csv  file). One of the field(nvarchar) in the table has numbers with leading zero's to it.

But when i export the data to csv, the leading zero's are not shown in the file when opened in excel. This must be because the excel file is considering the values as numbers and removing the leading zero's to it.

Any work around for this.

Answer 1

The only way I know around this is to open Excel, then select "Get Text" from the Data, Get External data menu.  Select the file.  In the third form of the wizard, select the columns you want to retain leading zeros.  Change the Column Data Format to Text.

---

Russel Loski, MCITP Business Intelligence Developer and Database Developer 2008

Answer 2

The fact is that you have CSV file and you must open it in Note pad NOT in excel

because excel will change the format and displays it they way it wants by default, i.e dates, you may have dates as 2001-07-25 and you have your OS set for USA (North america) then, the date will look like 07/25/2001

anyther issue you may have is if you save it from excel it will change the data acording to excel

---

Sincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 3

Prashant, if your not really worried about data type in csv (retain same datatype as in table) then you can have a derived column and have the expression adding leading single quotation mark ('). I think that way when you load into csv it will have leading zeros. But that column in csv will no longer be NUMERIC. It will be TEXT.

Answer 4

Hi BIRocks,

 

 i waqs following ur step..

 

i am geting values like

 '00023

'00045

but i don'r want leading " ' " in data . i want only 00023.

how can i achieve this.

i have written expression   like     " ' " + code

 

plz let me know any another way...

Answer 5

After you dump data into csv, are you going to do any operations on that data from csv? If not then you should be fine. You won't see '00023 instead it will be 00023. 

 

If you insist then try using excel destination and export to excel instead of csv. I haven't tried it but I have read it somewhere that newer version has fixed this.

 

Or google how to retain leading zeros in excel and you might get some workaround but basically AFAIK every workaround would be to somehow make number in a text format which then retains leading zeros. (" ' " is one example..you can instead have ASCII value appended in the data for example char(32) which translates to hard space)

Good luck