Excel to XML

Hi @trsali @davidskaife @ShankerV and guys,

Thanks for the example you have attached however can someone guide me to revising the attached worfkflow to contain the following details and format.

XML (output)

Looking forward for your response.

Thanks,

Kamen

ShankerV

@KamenRider

Can you please share the complete .xml in text format.

Many thanks

Shanker V

ShankerV

@KamenRider

When you try to input the .XML file back in Alteryx, it will read the file and the inputs will be taken in the tabular format.

So I wanted to check your input and confirm. Or you can try the same in your system, which will give the output in tabular form.

Use the output tool to save in the .XLSX format.

You can reach your result.

Kindly accept this solution if it provided a solution to your question.

Many thanks

Shanker V

I've got the below output if that's what you're referring to?

I've attached the updated workflow which i got to produce this, i simply copied over your input file into the Text input but it should work with an actual excel file in as well. It will also save the output in the same location that the workflow is saved in.

I tested reading the file generated using Alteryx and it reads it in correctly. Let us know if it's not what you need

XML Output.yxmd

Try posting your data and your needed .xml as files - not screen grabs. This would allow whoever is doing a sample build for you to test the results and see if they can match character for character.

Your data in the screen grab matched the <loan> component of the xml - I assume your header e-mail is non-static? Is there logic to that?

Hi guys - @davidskaife @ShankerV ,

thanks for the sharing of your solutions.

@davidskaife , One thing I am concerned with the solution is the presence of <Root> and GlobalOptions EmailCompletionMessageto... I am not good in xml but the way I look at the sample xml file I have provide, the workflow should have those command to mimic the actual xml we are inserting in the system. Is it possible to add those? Kindly check my sample xml with the presense of <Root> and GlobalOptionsCompletionMessageto command.

Looking forward for your response.

Thanks,

Kamen

Hi @davidskaife @apathetichell @ShankerV @trsali

I change the text Input file to Input Data file to attached my csv file and when I run it and open the xml output file, why is it it gives me a one line sentence instead of the xml format. When I open it in wordpad, all of the commands are there but when i opened it in the browser or xml reader, it gives me a line like the below sample:

BCLEADCGAMEUPD11202200047 DLA KP DEC 2022-11-17 14785965314_KITCHEN 24568 - KITCHEN UTEN Update

Please advise what went wrong and what will be the fix.

Looking forward for your assitance.

Thanks.

Kamen

First point: Try the attached workflow, there are two Text Input tools near the start that allow you to add in headers/footers so the output is looking more like what you're expecting. Have also added in a few other bits such as Loan, and Addresses.

Second point: are you using the original workflow or the one i uploaded? If you remove the final Summerize tool that stops it outputting in one long string

Note: What this workflow is doing is building up a tag for every column you enter, so <JobName>Kitchen Cleaning</JobName> and so forth. Looking at your screenshot it looks like its creating a 'header' line for each row, and then the actual data you provided is all grouped into one long tag. Can you confirm if that is what it should look like, and where you get that header line info from?

XML Output.yxmd

Could you post a screenshot of your Input tool configuration, it sounds like it's something to do with how it's reading in the file?

Hi @davidskaife

Posting the configuration of the input file

Name Value

1 Record Limit

2.File Format Comma Separated Value (*.csv)

3. Search SubDirs

4. Output File Name as Field No

5. Delimiters .

6. First Row Contains Field Rows check

7. Field Length 254

8. Start Data Import on line 1

9. Ignore Delimiters in Quotes

10. Treat Read Errors as Warnings

11. Code page ISO 8859-1 Latin 1

12. Allows Shared Write Access

13. AMP Only: Allow Newlines in Quoted Fields

14. AMP Only: Force Single-threaded Reading

Please advise correction or fix. Thanks,

Kamen

@KamenRideryou really need to post an actual sample of your .xml if you want a good solution here. You have a specific XML schema that you need and to ask people to replicate it off of truncated screen grabs is not really efficient.

Hi @apathetichell

Thanks for the response. The problem is that our system blocks the sending of attachment due to its confidentiality. How I hope there is another way so I could express the problem to my xml solution request.

For now as I said, the output doesn't provide me the xml format except that it produces a one line results which I hope you can find me a solution to it.

Thanks and looking forward for more ideas from you.

Kamen

@KamenRidertry changing the file extension from .xml to .txt. make sure you are using dummy data. The header/footer is really the key part - assuming that specific class types and nesting isn't needed.

My assumption is that this being consumed by an API somewhere. If the API is publicly available - please link to it. That would probably help. @davidskaife has spent a good deal of time on this - and I'd probably have said that you should be talking to your Alteryx rep for ideas.

Your csv import configuration looks ok, when i mirror it on my side the output looks fine:

It possibly could be due to the data coming in, i.e the structure of the data in the csv file itself.

As @apathetichell mentioned there is only so much we can do without seeing the data, or even screenshots, to help identify and fix for you. I'd echo their advice and maybe reach out to your Alteryx Rep for support and ideas.

Hi @davidskaife @apathetichell

Thanks for the continued assistance. I've created a csv file wherein you may try to run if it provides a xml format as output. Appreciate if you can share a screenshot for my reference. I do hope it will help resolve the issue I am encountering. I have also attached a sample of xml format I would like to see if I click the output file to see the xml.

Thanks and looking forward for your response.

Kamen

Sample file.csv

sample xml format run in brower.png

Please find attached an updated workflow for you to try, this has been saved as a package as it includes your sample data. I've also attached the output file i'm getting to allow you to compare.

Note: you won't get the indenting and so forth but the file should still be able to be imported provided all the tags are correct etc

If this is still not working then please share a screenshot of the xml that you need to upload, as the one you have just shared differs to one you shared at the start of this thread. Additionally it would be good to see a screenshot of what you're getting as an output file as well.

I opened the xml file you provided in a browser and below is the results. I am not sure if his is ok because I can see all the tagging in the wordpad. This is not the same in our xml file because it is viewable when you open it in the browser. I haven't open yet the workflow since I am working outside our system. Will let you know more later.

Thanks and looking forward for your thoughts on this.

Kamen

That is caused by a mismatch between the opening tag <Loans> and the closing tags </Loan> which i've based off the screenshot you posted at the start:

It's erroring as there is no corresponding ending for the Loans tag, whereas the remainings tags are call Loan, without the s.

Can you advise if this is the correct format for the xml, if not what should the format be - an example of the actual xml to be loaded would be great.
The actual data is one long xml string, which differs to the current ouput, is that the right format?
where does the header line come from, which contains the info on statusdate, codec, and identification?

HI @davidskaife

First I apologize since as I look the results in our end seems quite different. I mislook the format. I edited it in a notepad and was able to run it in the browser. Please take a look at it. Is it possible to modify the workflow to have this kind of output?

Here's the xml format in the brower.

The Global Options part is automatic when run in the system. I think it is the person who entered the data.

Attaching the xml file.

Looking forward for your continued assistance.

thanks,

Kamen

Output2.xml

I've re-built the workflow from the ground up, and the output I'm getting seems to match your requirements.

I've attached the package and the output for you to review. Let me know how it works for you!

I'm happy that is running using the browser and thanks, however upon checking I missed to something. Please see screenshot below.

I missed the "<LoanCustomData"

I tried adding in it using notepad but I run it, I can no longer see the results in the browser. Would you know what went wrong? I think this the last thing I missed and everything is ok.

Please advised.

Kamen

Will it be because you don't have the end tag of "/LoanCustomData>"? If you can confirm and i can add it in for you and reshare the updated workflow.

Your second question - i'd left the header as is so that will already be there

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">

- <Loans>

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

</Loans>

</Root>

Please advise.

Thanks,

Kamen

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.

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?

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.

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.

Alteryx code.PNG

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

regards,

Kamen

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?

@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:

Loan body...

</Loans>

</Addresses>

</Root>

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

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.