tFileOutputExcel - Formatting Numbers as Text

Highlighted
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.
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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download