topic Re: Leading zeros are removed when using tfileoutputdelimited in Talend Studio

Leading zeros are removed when using tfileoutputdelimited

HemaS1 — Fri, 15 Nov 2024 23:14:55 GMT

Hello,

I have data extracted from SQL server DB table and when it output to CSV file using tfileoutputdelimited the leading zeros are deleted. For example '02193' becomes '2193'.

I verified that the column has a datatype of string from DB to tfileoutdelimited.

Need help on how to fix this.

Thanks,

Hema

Re: Leading zeros are removed when using tfileoutputdelimited

Anonymous — Thu, 03 Feb 2022 21:13:20 GMT

If the value coming from the DB is a String of "02193" it can only be changed to "2193" if it is converted to an int or the leading character is removed. This won't happen by accident. Can you check every component that the data passes through to ensure that everything is set to String? Maybe show us a screenshot of your job and the source and target component schemas.

Re: Leading zeros are removed when using tfileoutputdelimited

HemaS1 — Thu, 03 Feb 2022 21:19:48 GMT

Thank you for responding.

Here are the screen shots:

tdbInput:

tfileoutputdelimited:

Re: Leading zeros are removed when using tfileoutputdelimited

Anonymous — Thu, 03 Feb 2022 21:26:59 GMT

Can you replace the tFileoutputDelimited with a tLogRow and check the output to the System.out window?

Oh, one more thought, are you reading the CSV via Excel? Could this be the formatting in Excel? Check the file using a textpad if you are using Excel.

Re: Leading zeros are removed when using tfileoutputdelimited

HemaS1 — Thu, 03 Feb 2022 21:36:52 GMT

The value read from tdbinput to tlogrow is coming out correctly as shown with the laeading zero:

I am reading the values directly from database and the schema and the screen shot is above.

Here are the settings for this tfileoutputdelimited.

Advanced settings:

Re: Leading zeros are removed when using tfileoutputdelimited

Anonymous — Thu, 03 Feb 2022 22:25:06 GMT

How are you reading the the CSV file?

Re: Leading zeros are removed when using tfileoutputdelimited

HemaS1 — Thu, 03 Feb 2022 22:28:22 GMT

The data comes from DB query and output to CSV file.

Re: Leading zeros are removed when using tfileoutputdelimited

Anonymous — Thu, 03 Feb 2022 22:30:54 GMT

How do you know what is in the file?

Re: Leading zeros are removed when using tfileoutputdelimited

HemaS1 — Thu, 03 Feb 2022 22:34:17 GMT

SOrry, could you clarify?

I look at the tlogrow to see what is coming from DB and look at the output file.

Re: Leading zeros are removed when using tfileoutputdelimited

Anonymous — Thu, 03 Feb 2022 22:35:26 GMT

How do you know that the leading zeros are deleted? What software do you use to look at the CSV file?

Re: Leading zeros are removed when using tfileoutputdelimited

HemaS1 — Thu, 03 Feb 2022 22:38:07 GMT

I just opened the file and look at it.

This is what I see... It is an comma seperated excel file.

Re: Leading zeros are removed when using tfileoutputdelimited

Anonymous — Thu, 03 Feb 2022 22:39:07 GMT

Open the file using a text file reader. Something other than Excel.

Re: Leading zeros are removed when using tfileoutputdelimited

HemaS1 — Fri, 04 Feb 2022 00:02:35 GMT

Yes, Thank You!!

It is how you view the csv file. Instead of directly opening the excel CSV file I imported the data into a blank excel and viewed it and there it is. The values are populated correctly.

Appreciate your help on this.

Re: Leading zeros are removed when using tfileoutputdelimited

Anonymous — Fri, 04 Feb 2022 00:30:06 GMT

No problem. It's always best to check the data sent to flat files in something like Notepad (for Windows) first. That will show you exactly what has been written to the file.