Skip to main content
  • EN

Hi All,

 

I'm trying to write Bag-ids (adress ids of the Netherlands) to an Excel or CSV file. However I'm unable to write it to these formats without destroying the data.

 

Source data example

 Bag_id _

0735200000017598

1735200000017598

 

If I write it to CSV i'm losing the prefix 0 (while using varchar type).

If I write it to Excel the numbers without the prefix 0 are rounded (while using the string type)

 

See appendices.

 

The only fix i'm able to come up with is prefixing everything with " ' ". But that of course transforms the source data. This I don't want.

 

Does anyone have a solution for this?

 

I have added my test sample in the appendix aswell (e.g. source_data_v3)

The leading zeros are in your attached csv file, if you inspect with notepad or similar text editor you will see them.

image

@jpa_v​ In the FME Excel writer, perhaps make sure you are writing the field as text and not an integer. Or, use the Excel writer formatting options to pad the integer value with leading zeros

The leading zeros are in your attached csv file, if you inspect with notepad or similar text editor you will see them.

image

Thank you @ebygomm​ ! This solves a large part of my problems. Yet this solution is not perfect, because after me this CSV is adapted and opened many times (by different pepole) in an Excel viewer. People are then copy pasting the bag id in the online adres viewer, where then they will find a mismatch. It is a small inconvenience, but if possible I would like to avoid that.

@jpa_v​ In the FME Excel writer, perhaps make sure you are writing the field as text and not an integer. Or, use the Excel writer formatting options to pad the integer value with leading zeros

Hi @Mark Stoakes​ , I have tried to write it as string. It does not work (see Excel_test). Also the custom formats as proposed in: 'https://community.safe.com/s/question/0D54Q000080hYYtSAM/stop-the-numbers-from-rounding '

does not solve the problem. To me it looks like a bug in FME or Excel, because this should work.

Excel_test_writer_options

Hi @Mark Stoakes​ , I have tried to write it as string. It does not work (see Excel_test). Also the custom formats as proposed in: 'https://community.safe.com/s/question/0D54Q000080hYYtSAM/stop-the-numbers-from-rounding '

does not solve the problem. To me it looks like a bug in FME or Excel, because this should work.

Excel_test_writer_options

What version of FME are you using? If i write your data to excel with a string type the IDs are kept as is with leading zeros

image

Hi @ebygomm​ , I use 2021.2.2. In Excel the problem lies with the automatic rounding of the data of the numbers without prefix 0. However, I just noticed that in the Excel online preview of this site, the data is showed correctly. See image below. So the problem is in Excel itself 😅. Any tips to avoid this are still welcome! This, however, confirms the problem is not within FME. Excel online preview compared to Excel windows

Reply


Community Stats

30,167
Posts
114,520
Replies
37,824
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.