One Star

tFileOutputExcel - Formatting Numbers as Text

Hi,
I am reading an Excel file and writing the contents to another excel file using tFileOutputExcel (using TOS 2.3 with a PERL project). I have rows that are formatted as text in the source as below:
Col1 Col2 Col3 etc.
121 001 0535
When I write the file, I am losing the leading zeros. At this time of testing, I am just reading and directly writing (no components in the middle). The schema defines these columns as STRING on both ends.
I tried various format options in the advance section of tFileOutputExcel but could not figure out the magic combination. Can someone please direct me to a solution? I am a newbie to TOS (having used mainly Informatica over several years).
Thanks in advance.
Sean.
Tags (1)
4 REPLIES
Community Manager

Re: tFileOutputExcel - Formatting Numbers as Text

Hi Sean
In Java version, defining all the columns as String can get the right result, but Perl version is failed. I don't know why, I will check it with Plegall.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Employee

Re: tFileOutputExcel - Formatting Numbers as Text

I agree there is a problem. The correct format in Perl tFileOutputExcel advanced settings is "@". But it fails. If I modify the generated code and write:
$column_formats_tFileOutputExcel_1->->set_num_format('000');

it works as expected. I ask rbillerey (Perl and Excel master) to work on this issue. Can you open a bug notification in the bugtracker please?
One Star

Re: tFileOutputExcel - Formatting Numbers as Text

I tried the "@" with the excel spreadsheet and as you point out, it does not work at this moment. Right now my workaround will be to create csv files with TXT extension. This way when user imports it, they can mark the columns as to what they want.
I'll start a bug tracker (my first, wow)! I may have questions about this.
Thanks.
Employee

Re: tFileOutputExcel - Formatting Numbers as Text

Thanks for creating 3255