XML TO .CSV Conversion issue

[Former Member]

Hi All,

I Developed an MDM to 3rd Party FTPscenario with FCC . The data from MDM comes as XML and i placed it at the FTP succesfully with .CSV format.

But the issue is with tirmming of before Zeros in excel file,

eg: The value of a field GTIN_No is 076895464 from source, but its getting placed in the excel file as 76895464.

The before zero is mandatory as of the client requirement.

Please suggest

Regards,

Raghav

[Former Member · Accepted solution]

Do you have the leading zero when opening the csv file a text editor?

[Former Member · Accepted solution]

Yes I have them when opened with text editor

[Former Member · Accepted solution]

Then it is a formatting problem of Excel. I don't know what PI can change for that.

If you don't open it directly with Excel, but with file menu Data -> From Text, in the import wizard you specify column data format and choose text, then it should be fine.

[Former Member · Accepted solution]

No the client opens it directly, do we have any other options instead of using the import wizard

[nipun_shedhani4 · Accepted solution]

Hello Raghavendra,

Can you include a quote ' before and after the numerical value. The Excel will then interpret this number as string from the CSV file you are creating and should solve your problem.

Let me know if you still have issues after this solution.

Regards,

Nipun

[Former Member · Accepted solution]

Hi Nipun,

I tried this, but in the CSV file these quotes are also getting placed.

eg:

SELLING_UNIT_UPC	MIN_CODE
'078000069631'	78000
'078000083637'	78000

Clients needs the values exactly as 078000069631

Kindly let me know if there are any other solutions

Thanks in advance.

Regards,

Raghav

[Former Member · Accepted solution]

Hi Raghav，

you need to explain the client, this is a formatting problem. The users need to be trained to how to format/display the data correctly in Excel program.

Regards

Dingjun

[Former Member · Accepted solution]

Hi Raghav

In the PI mapping set the numeric field value with the below syntax

="078000069631"

Then the CSV file will show the exact value 078000069631

[Former Member · Accepted solution]

Hi Indra,

I tried it, It even didnt work.

It is getting placed without zeros.

[former_member312864 · Accepted solution]

Hi,

I tried with indrajit's solution its working.

concat =" before the number and concat " after the number

Input

-------------------

concat ="

input value 078000069631

concat "

Output in CSV

--------------------

="078000069631"

Output in excel

078000069631

[Former Member · Accepted solution]

Hi Sarojkanta,

My input comes as string :078000069631

Then, concatenated that input with "  using the constant function, also again at the end and mapped it to a integer output field,

Hope I am correct

But i still got the output with out zero.

Please suggest, and also let me know whether u tried for .csv or .xls format

Regards,

Raghav

[former_member184720 · Accepted solution]

Then, concatenated that input with "  using the constant function, also again at the end and mapped it to a integer output field

>>> It's not just the double quotes.. You need to concat with ' =" ' at the beginning and ' " ' at the end.

[former_member312864 · Accepted solution]

can you attach your mapping screenshot

[Former Member · Accepted solution]

Hi Hareesh,

Can you please help me in one more issue,

The client needs the file in .xlsx format.

I tried changing the extension from filename.csv to filename.xlsx and when tried opening the file its erroring as Cannot open the file as invalid file format or file extension.

What should do in this case, Is PI 7.3.1 capable of generating the .xlsx extension, if so, kindly let me know what should done.

[Former Member · Accepted solution]

Just changing the file extension will not work. You need to use a java API to generate an xlsx file.

For example this blog mentioned how to handle xlsx file.

[Former Member]

Thank you Indrajit and Hareesh, I got my issue resolved.

I missed concatinating the = sign.

Very helpful. the excel file has the required zeros.

Thank you again.

Regards,

Raghav.

[Former Member]

Please check the below link.

Regards

Osman