Excel to XML

[KamenRider]

Hi guys,

I seeking you assistance if you have any sample how we can convert excel to xml. I tried searching but I can't find one I can understand.

Hoping for your kind assistance on this matter.

thanks,

Kamen

[KamenRider]

HI @DavidSkaife 

I have checked the xml file and it doesn't contain /LoanCustomData>. It is started by <LoanCustomData UniqueLoanNumber="14578966"  and ended with Processor="Ana Perez"/>

I can't find the reason why or how to debug it. Below is how it look like.

        - <Loans>

           - <Loan LoanNumber="BUN_4874596" LoanStatus="HKG DE BAK" LSDate="10/15/2022">

             <LoanCustomData UniqueLoanNumber="14578966" Jobname="Kitchen Cleaning"ProcessLeadOfficer="Andrade Quirino" ScheduledManager="Quereda                   Munoz"  ProcessLevel="Thunderbird" LOC="HKG - Special Forces" ETADate="10/15/2022" HongkongWorkDate="10/14/2022" BCAnalystCode="T3065478"
             Processor="Ana Perez"/>
            </Loan>

         - <Loans>

           - <Loan LoanNumber="BUN_4874596" LoanStatus="HKG DE BAK" LSDate="10/15/2022">

             <LoanCustomData UniqueLoanNumber="14578966" Jobname="Kitchen Cleaning"ProcessLeadOfficer="Andrade Quirino" ScheduledManager="Quereda                   Munoz"  ProcessLevel="Thunderbird" LOC="HKG - Special Forces" ETADate="10/15/2022" HongkongWorkDate="10/14/2022" BCAnalystCode="T3065478"
             Processor="Ana Perez"/>
            </Loan>

        </Loans>

</Root>

Please advise. 

Thanks,

Kamen

[apathetichell]

the .xml file you posted did not include the following:

>

             <LoanCustomData 

Back to my original post way at the start of this thread - including the XML formatting you need and the header is KEY to create any .xml file. I think you're getting there. But all of these iterations @DavidSkaife is having to build are because he doesn't have a full set of XML headers/tags.

[DavidSkaife]

Hi @KamenRider 

Another query as the below doesn't add up. Have highlighted some sections as you have two <Loans> but only one </Loans>

Can you review and provide an updated version, with at least two records as you have shown here so i can see how it should work fully?

[KamenRider]

Hi @DavidSkaife 

Apology for the confusion. I am not good in xml and I am trying to understand the xml output I am seeing. I am mimicking the way it is being posted. Here's the correct one. As you can see it is being posted group by group.

<?xml version="1.0" encoding="UTF-8"?>
<Root>
<GlobalOptions EmailCompletionMessageTo="jamesdier@yahoo.com" ImportOption="Ignore"/>
<Addresses></Addresses>

- <Loans>
- <Loan LoanNumber="BUN_4874596" LoanStatus="HKG DE BAK" LSDate="10/15/2022">

<LoanCustomData UniqueLoanNumber="14578966" Jobname="Kitchen Cleaning"ProcessLeadOfficer="Andrade Quirino" ScheduledManager="Quereda Munoz" ProcessLevel="Thunderbird" LOC="HKG - Special Forces" ETADate="10/15/2022" HongkongWorkDate="10/14/2022" BCAnalystCode="T3065478"
Processor="Ana Perez"/>
</Loan>

- <Loan LoanNumber="BUN_4874596" LoanStatus="HKG DE BAK" LSDate="10/15/2022">
<LoanCustomData UniqueLoanNumber="14578966" Jobname="Kitchen Cleaning"ProcessLeadOfficer="Andrade Quirino" ScheduledManager="Quereda Munoz" ProcessLevel="Thunderbird" LOC="HKG - Special Forces" ETADate="10/15/2022" HongkongWorkDate="10/14/2022" BCAnalystCode="T3065478"
Processor="Ana Perez"/>
</Loan>

</Loans>

</Root>

In line to the LoanCustomData, I'm not sure why it is there without close tagging. I can't understand why it did run. Maybe you can find some clue to solve the issue.

Please advised.

Kamen

[DavidSkaife]

Hi @KamenRider 

no problem, we're getting there though! Please find attached an updated workflow package and output i'm getting, it appears to match what you have posted.

[KamenRider]

Hi @DavidSkaife 

Glad to see the results is viewable. However, I noticed that the sequence of those in the workflow does not match with the results. I tried feeding the xml in the system and there are some fields that failed to fed. Plus the address in the results does not have an open tagging. Maybe I think  these are the caused why some fields in the system are empty after I insert and run the xml in the system. Can you still fix this? Please see below.

 

 

Thanks for your continued assistance and looking forward for your response.

regards,

Kamen

[DavidSkaife]

Hi @KamenRider 

My output does have the <Addresses> open tag on it, so not sure what you're meaning?

Can you explain a bit more what you mean by the sequence does not match the results?

[apathetichell]

@KamenRiderthis is very long winded. Can you mark @DavidSkaife 's solution as correct? The footer you provided on Tuesday was incorrect. The footer would contain something like </Addresses> - @DavidSkaife matched your output

@DavidSkaife:

<Addresses/>

Loan body...

</Loans>

</Addresses>

</Root>

[KamenRider]

Hi @DavidSkaife 

What I mean is that your workflow does have the tagging <addresses></addresses> but the xml only shows <addresses/>. Isn't the correct tagging that the XML must show is <addresses></addresses>?

In line to sequence, the workflow shows correct sequence as presented in the excel or sample. The excel file field sequence are:

Loan Number - Loan Status - LS Date

Unique Loan Number - Jobname - Process Lead Officer - Scheduled Manager - Process Level - LOC - ETA Date - Hongkong Work Date - BC Analyst Code - Processor

The XML output sequence shows not in order as follows:

LSDate - Loan Status - Loan Number

Processor - BC Analyst Code - Hongkong Work Date  - ETADate - LOC - ProcessLevel - ScheduledManager - ProcessLeadOficer - Jobname - UniqueLoanNumber

The problem with the XML output when we inserted in the system is that some fields does not contain data and we thought that maybe the sequence has a factor of contributing empty fields. Why we says this is that because the file we have in the office and XML has the same sequence (order) and position.

Please let me know your thoughts about this. I do hope we can figure this out as I know we are nearing to solve the problem.

Thanks and let me know if still have any questions.

Kamen

[DavidSkaife]

Hi @KamenRider 

The XML sequence is correct as per the input file you provided, and the Address tagging is there in the output, as per the below screenshot:

Are you reading the file using your browser? If you try opening it with Notepad or similar the full data structure is there so the file is correct.

The only way that the sequence will change is if the input file has the sequence in a different order, no where within the workflow does it change the sequence around (it doesn't need to)

I suggest accepting this solution as @apathetichell has mentioned above. I've got the output to match your screenshots, and when i read it back into Alteryx it picks everything up correctly.