Create XML output from Excel files

Five Stars

Create XML output from Excel files

Hello,

 

I'm pretty new here and I really need help.

 

I already knew a little bit simple job to create xml output but here I have an excel file looks like below

Capture1.PNG

 

 

 

 

and I want to convert it into xml format like this :

 

 
- <JV operation="Add" REQUESTID="1">
  <TRANSACTIONID>22868</TRANSACTIONID> 
- <ACCOUNTLINE operation="Add">
  <KeyID>0</KeyID> 
  <GLACCOUNT>2103.07.04</GLACCOUNT> 
  <GLAMOUNT>38933333</GLAMOUNT> 
  <DESCRIPTION /> 
  <RATE>1</RATE> 
  <PRIMEAMOUNT /> 
  <TXDATE /> 
  <POSTED /> 
  <CURRENCYNAME>IDR</CURRENCYNAME> 
  </ACCOUNTLINE>
- <ACCOUNTLINE operation="Add">
  <KeyID>1</KeyID> 
  <GLACCOUNT>4102.01.09</GLACCOUNT> 
  <GLAMOUNT>-38933333</GLAMOUNT> 
  <DESCRIPTION /> 
  <RATE>1</RATE> 
  <PRIMEAMOUNT /> 
  <TXDATE /> 
  <POSTED /> 
  <CURRENCYNAME /> 
  </ACCOUNTLINE>
- <ACCOUNTLINE operation="Add">
  <KeyID>2</KeyID> 
  <GLACCOUNT>2103.07.04</GLACCOUNT> 
  <GLAMOUNT>30000000</GLAMOUNT> 
  <DESCRIPTION>PT INDO - SEWA ADVERTISING NEON BOX ROOFTOP SKYBRIDGE TGL 15 NOV 17 - 14 NOV 18</DESCRIPTION> 
  <RATE>1</RATE> 
  <PRIMEAMOUNT /> 
  <TXDATE /> 
  <POSTED /> 
  <CURRENCYNAME>IDR</CURRENCYNAME> 
  </ACCOUNTLINE>
- <ACCOUNTLINE operation="Add">
  <KeyID>3</KeyID> 
  <GLACCOUNT>4102.01.09</GLACCOUNT> 
  <GLAMOUNT>-30000000</GLAMOUNT> 
  <DESCRIPTION>PT INDO - SEWA ADVERTISING NEON BOX ROOFTOP SKYBRIDGE TGL 15 NOV 17 - 14 NOV 18</DESCRIPTION> 
  <RATE>1</RATE> 
  <PRIMEAMOUNT /> 
  <TXDATE /> 
  <POSTED /> 
  <CURRENCYNAME /> 
  </ACCOUNTLINE>
  <JVNUMBER>NM/001/05/18</JVNUMBER> 
  <TRANSDATE>2018-05-15</TRANSDATE> 
  <SOURCE>GL</SOURCE> 
  <TRANSTYPE>journal voucher</TRANSTYPE> 
  <TRANSDESCRIPTION>PAMERAN LT. DASAR LUAS 25 M2 (REVISI PERIODE TGL 04 DES 17 - 03 DES 18) A/N PENATA</TRANSDESCRIPTION> 
  <JVAMOUNT>68933333</JVAMOUNT> 
  </JV>
- <JV operation="Add" REQUESTID="1">
  <TRANSACTIONID>27069</TRANSACTIONID> 
- <ACCOUNTLINE operation="Add">
  <KeyID>0</KeyID> 
  <GLACCOUNT>6102.05.41</GLACCOUNT> 
  <GLAMOUNT>825000</GLAMOUNT> 
  <DESCRIPTION /> 
  <RATE>1</RATE> 
  <PRIMEAMOUNT /> 
  <TXDATE /> 
  <POSTED /> 
  <CURRENCYNAME /> 
  </ACCOUNTLINE>
- <ACCOUNTLINE operation="Add">
  <KeyID>1</KeyID> 
  <GLACCOUNT>1114.02.02</GLACCOUNT> 
  <GLAMOUNT>-825000</GLAMOUNT> 
  <DESCRIPTION /> 
  <RATE>1</RATE> 
  <PRIMEAMOUNT /> 
  <TXDATE /> 
  <POSTED /> 
  <CURRENCYNAME>IDR</CURRENCYNAME> 
  </ACCOUNTLINE>
  <JVNUMBER>NM/003/05/18</JVNUMBER> 
  <TRANSDATE>2018-05-09</TRANSDATE> 
  <SOURCE>GL</SOURCE> 
  <TRANSTYPE>journal voucher</TRANSTYPE> 
  <TRANSDESCRIPTION>REALISASI BY PERMOHONAN PEMBUATAN SIGNAGE APAR 50PC (BP45/2018/02/045)</TRANSDESCRIPTION> 
  <JVAMOUNT>825000</JVAMOUNT> 
  </JV>

I don't know if this proper enough to use this as example, so please forgive me.

 

I think I can't use simple job to execute the job. I really need help for this, I hope someone can help me

 

Best regards & Thank you very much

Ten Stars

Re: Create XML output from Excel files

 

First unarchive,  because .xlsx files are already xml,

so after unzip .xlsx file...  navigate/browse to xl\worksheets 

 

Look at attachments

Five Stars

Re: Create XML output from Excel files

Hello Dijke thank you for your respond.

 

I'm so sorry, honestly I can't understand what you mention.

My intention for doing this is to get an xml file so I could import it to program like below

fw1.PNGpreview on program.fw2.PNGpreview on program

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

On this program the debit and credit field must be integer/big decimal value also date must be date value. And there was a fix format/template for specific module. Can I create an xml file so I can import it as a same value that the program have?

 

I have a simple job before (please look below)

xlskcd.PNGthe source data

 

 

 

tosjvjob.PNGthis was my simple jobtosjvmap.PNGmapping preview. On the right side was the fix format for specific module after exported into xml file

kcd1.PNGthe result after imported to programkcd2.PNGthe result after imported to program

my case on previous post was on jvnumber fields that have a same value must be merge into 1 field/row

fwtosmap.PNGmy expeted mapping. The multiple rows that have same value on jvnumber fields all can process into accountline field

 

Would you mind to give me a method using Talend?

 

Thanks for your concern

Ten Stars

Re: Create XML output from Excel files

I understand now, sorry, so you want to redesign rows into XML ... 

" my case on previous post was on jvnumber fields that have a same value must be merge into 1 field/row "

 

> Sounds like a 'grouping' based on non calculating fields? like AccountID
> You want probably the last known values, so you need to retain Debt and Cred because of the transactionlog with current state.
> You could use tAggregate first to create single rows. Look at example. Use last function and make sure check ignore null values.

 

 

 

 

 

Five Stars

Re: Create XML output from Excel files

I'm sorry, maybe I can't use that because I need all the values of deb, cred, glaccount and keyid (except null value) not just the last values.

Capture1.PNGmy new resource data

tos.PNGtrying the suggestion

tos1.PNGresult. only have last values. need all values of deb, cred, glaccount and keyid

Maybe I will create multiple sub element at xml tree? But I don't know how to merge it without eliminate some values on specific columns

 

xlskcd.PNGHere is the example with just 2 fields in single row. I want more than 2

Ten Stars

Re: Create XML output from Excel files


Within tAggregate, why dont you use GLAccount as grouping??? this will correctlly merge the deb-cred data, you loose data because of wrong grouping. Dont group on JVNumber, you will use that for your xml.
Five Stars

Re: Create XML output from Excel files

tos.PNGthe result base on glaccount grouping

 

I'm so sorry I think I can't use this grouping too, because it give me 4 rows which is I got 4 xml fields while I want it just 2 xml fields. That's why I want to group it based on jvnumber. If I import it to the program, 2 fields will be rejected because the same jvnumber and sometimes there will be same values on glaccount so I think some values will be eliminated.

 

Can I make it looks like this? maybe i can manage it somehow with the xml fields (in the future I will need 30 'glamount' etc for each row)

datejvnumberglamountglaccountKeyglamount_1glaccount_1Key_1glamount_2glaccount_2Key_2glamount_3glaccount_3Key_3
09/05/2018NM/003/05/188250006102.05.410-8250001114.02.021      
15/05/2018NM/001/05/18389333332103.07.040-389333334102.01.09130000002103.07.042-30000004102.01.093

 

 

Ten Stars

Re: Create XML output from Excel files

No worries, your output with just two rows is, I think, possible by doing a Transpose/Pivot
There's a component called tPivotColumnsDelimited, take a look at that. Hope it works.