Create XML output from Excel files

[Anonymous]

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

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

[Jesperrekuh]

First unarchive,  because .xlsx files are already xml,

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

Look at attachments

Unzip_Excel.jpg
unzip_xlsxl_browse_sheetname.jpg

[Anonymous]

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

preview on program.preview 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)

the source data

this was my simple jobmapping preview. On the right side was the fix format for specific module after exported into xml file

the result after imported to programthe 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

my 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

[Jesperrekuh]

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.

debcred.jpg
tAggregate.jpg

[Anonymous]

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.

my new resource data

trying the suggestion

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

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

[Jesperrekuh]

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.

[Anonymous]

the 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)

date	jvnumber	glamount	glaccount	Key	glamount_1	glaccount_1	Key_1	glamount_2	glaccount_2	Key_2	glamount_3	glaccount_3	Key_3
09/05/2018	NM/003/05/18	825000	6102.05.41	0	-825000	1114.02.02	1
15/05/2018	NM/001/05/18	38933333	2103.07.04	0	-38933333	4102.01.09	1	3000000	2103.07.04	2	-3000000	4102.01.09	3

[Jesperrekuh]

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.