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

[apathetichell]

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?

[KamenRider]

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

[KamenRider]

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

[DavidSkaife]

Hi @KamenRider 

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?

[KamenRider]

Hi @DavidSkaife 

I run your workflow and I wondering why it gives me a one line results instead of xml format. Can you please help me this? Results below.

Kitchen Cleaning Andrade Quirino Quereda Munoz DEC Dead End/Escalation HKG - Special Forces 10/22/2022 10/14/2022 K365478 Santander Bart T365478 Ana Perez

Looking forward for your response

Thanks, 

Kamen

[DavidSkaife]

Hi @KamenRider 

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?

[KamenRider]

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

[apathetichell]

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

[KamenRider]

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

[apathetichell]

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