IMPORTANT:
this is not a Support Forum! Experienced users might answer from time to time questions posted here. If you need a professional and reliable answer, or if you want to report a bug, please contact Altova Support instead.

Excel to XML - Break comma delimited values in a cell into distinct data points in target Options · View
ShawnL
Posted: Wednesday, February 17, 2021 3:42:54 PM
Rank: Member

Joined: 2/2/2021
Posts: 10
Location: United States
Hi There -

This forum was really helpful a few weeks ago for me so I thought I'd throw another question out there I need to solve.

I have a column in excel that can have either one or multiple values in it. When there are multiple values they will be comma separated (or any separation. I can control this part)

Example:



I have this ALMOST working the way I need it to (although I'll need to do this slightly differently later in the XML that I'll probably need some assistance with too)

Each value (including the 101, 201 and 202) need to be represented ONCE in the XML output. Very similar to an issue I was helped with previously.

I'm close to a solution right now but instead of the 101, 201 and 202 showing up as distinct values it's just showing up as 101201202. I think I'm using tokenize wrong.

Here's my setup and current output.

SETUP:


OUTPUT:



The output is correct for the Excel rows above. I only want each value represented once in <Assignment_Level_3>

The issue is that when I tried to use tokenize with (,) as delimiter it's just removing the commas and jamming the data together (the 101201202)

Desired result is two more nodes for 201 and 202 since those don't exist.

Any help would be greatly appreciated!

Thanks,

Shawn
that
Posted: Wednesday, February 17, 2021 4:18:49 PM
Rank: Advanced Member

Joined: 6/16/2006
Posts: 451
Location: AT
Are you sure that your cell doesn't contain the value of 101201202, which is 101 millions, 201 thousands and 202?
Maybe try a different separator.
ShawnL
Posted: Wednesday, February 17, 2021 8:00:42 PM
Rank: Member

Joined: 2/2/2021
Posts: 10
Location: United States
that wrote:
Are you sure that your cell doesn't contain the value of 101201202, which is 101 millions, 201 thousands and 202?
Maybe try a different separator.


You're right. That's exactly how excel was treating 101,201,202. I feel silly for not realizing that. I used a semicolon as the delimiter instead. The data in excel now reads 101;201;202

The results aren't what I expected after that though. If you look at the screenshot of the XML generated results the results are the same except the node with Assignment_Level_3 Code="101201202" is missing all together now.

Anything else you can think of? I'm plugging away at this in my spare time but this is a hard requirement on the mapping I'm going to have to do eventually. I had luck in the forum last time so I thought I'd try here again before I employ Altova's consulting services.

Thanks!

Shawn

that
Posted: Thursday, February 18, 2021 12:57:04 PM
Rank: Advanced Member

Joined: 6/16/2006
Posts: 451
Location: AT
ShawnL wrote:
The results aren't what I expected after that though. If you look at the screenshot of the XML generated results the results are the same except the node with Assignment_Level_3 Code="101201202" is missing all together now.


What should happen here is that the line with multiple room numbers is added to multiple groups. So you should have separate elements for 101, 201, and 202 that are grouped together with other rows having those values. I don't see 201 and 202 on your screenshot, but that's maybe just because the bottom is cut off.
ShawnL
Posted: Thursday, February 18, 2021 4:13:26 PM
Rank: Member

Joined: 2/2/2021
Posts: 10
Location: United States
that wrote:
ShawnL wrote:
The results aren't what I expected after that though. If you look at the screenshot of the XML generated results the results are the same except the node with Assignment_Level_3 Code="101201202" is missing all together now.


What should happen here is that the line with multiple room numbers is added to multiple groups. So you should have separate elements for 101, 201, and 202 that are grouped together with other rows having those values. I don't see 201 and 202 on your screenshot, but that's maybe just because the bottom is cut off.


Nope, that's the problem. The 201 and 202 aren't showing up at all unfortunately.

Shawn
that
Posted: Thursday, February 18, 2021 5:41:10 PM
Rank: Advanced Member

Joined: 6/16/2006
Posts: 451
Location: AT
It works for me. Make sure that you define column G ("Room#") in the Excel component as type string and not numeric as it might have been before.
ShawnL
Posted: Thursday, February 18, 2021 9:09:52 PM
Rank: Member

Joined: 2/2/2021
Posts: 10
Location: United States
that wrote:
It works for me. Make sure that you define column G ("Room#") in the Excel component as type string and not numeric as it might have been before.


BEAUTIFUL!

You're right. I had it defined as numeric. When I started this I was told that there would only be one value in the cell. So I defined that field as numeric. Obviously my problem.

Thank you SO MUCH.

On to the next problem now. They have to be defined again, one node for each of the values in the cell further down in our schema. I'm going to attempt it myself before I reach back out.

Seriously, Thank you for your help!

Shawn
Users browsing this topic
guest

Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Use of the Altova User Forum(s) is governed by the Altova Terms of Use.