-
3 Attachment(s)
Need help to convert Excel data to XML
-
you can try with
Code:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<DataImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<EntityID>EntityID</EntityID>
<data>
<date>
<day>day</day>
<month>month</month>
<year>year</year>
</date>
<recID>recID</recID>
<time>time</time>
<itemSold>ItemSold</itemSold>
<Price>Price</Price>
</data>
</DataImport>
as XML schema (not XSD!)
but probably you'll see Denormalized Data error
so I suggest
W3C link
and
Normalizing XML link
-
2 Attachment(s)
or try this Excel to XML
Attachment 3503
remove .txt and stay with .xml
and after mapping
Attachment 3504
-
1 Attachment(s)
Error Screenshot
-
Thank you Sandy, I tried both but it didn't give me the desired output. I'm looking for output like this -
Code:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<DataImport>
<forecast>
<Entity>700</Entity>
<data>
<date>
<day>19</day>
<month>1</month>
<year>2021</year>
</date>
<period recID="7001">
<time>8:00</time>
<ItemSold>19</ItemSold>
<Price>219</Price>
</period>
<period recID="7001">
<time>8:30</time>
<ItemSold>21</ItemSold>
<Price>219</Price>
</period>
</data>
</forecast>
<forecast>
<Entity>701</Entity>
<data>
<date>
<day>20</day>
<month>1</month>
<year>2021</year>
</date>
<period recID="7002">
<time>8:00</time>
<ItemSold>20</ItemSold>
<Price>220</Price>
</period>
<period recID="7002">
<time>8:30</time>
<ItemSold>23</ItemSold>
<Price>220</Price>
</period>
</data>
</forecast>
</DataImport>
However I'm getting output like this -
Code:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<records>
<record>
<EntityID>700</EntityID>
<ItemSold>23</ItemSold>
<Price>119</Price>
<day>19</day>
<month>2</month>
<recID>7001</recID>
<time>8:00</time>
<year>2021</year>
</record>
<record>
<EntityID>700</EntityID>
<ItemSold>25</ItemSold>
<Price>120</Price>
<day>19</day>
<month>2</month>
<recID>7001</recID>
<time>8:30</time>
<year>2021</year>
</record>
<record>
<EntityID>700</EntityID>
<ItemSold>24</ItemSold>
<Price>121</Price>
<day>19</day>
<month>2</month>
<recID>7001</recID>
<time>9:00</time>
<year>2021</year>
</record>
and continues..
so the entity IDs are not repeated and date is not repeated within entity tag if that helps.
-
3 Attachment(s)
here is a proper xml for mapping
Attachment 3506
if you will see warning choose first
Attachment 3507
and here is exported xml from the table
Attachment 3508
Table
| EntityID |
day |
month |
year |
recID |
time |
ItemSold |
Price |
700 |
19 |
2 |
2021 |
7001 |
8:00 |
23 |
119 |
700 |
19 |
2 |
2021 |
7001 |
8:30 |
25 |
120 |
700 |
19 |
2 |
2021 |
7001 |
9:00 |
24 |
121 |
700 |
19 |
2 |
2021 |
7001 |
9:30 |
26 |
110 |
700 |
19 |
2 |
2021 |
7001 |
10:00 |
21 |
119 |
700 |
19 |
2 |
2021 |
7001 |
10:30 |
20 |
119 |
700 |
19 |
2 |
2021 |
7001 |
11:00 |
19 |
120 |
700 |
19 |
2 |
2021 |
7001 |
11:30 |
10 |
119 |
701 |
19 |
2 |
2021 |
7002 |
8:00 |
23 |
121 |
701 |
19 |
2 |
2021 |
7002 |
8:30 |
23 |
123 |
701 |
19 |
2 |
2021 |
7002 |
9:00 |
22 |
119 |
701 |
19 |
2 |
2021 |
7002 |
9:30 |
23 |
119 |
701 |
19 |
2 |
2021 |
7002 |
10:00 |
25 |
119 |
701 |
20 |
2 |
2021 |
7002 |
8:00 |
23 |
145 |
701 |
20 |
2 |
2021 |
7002 |
8:30 |
27 |
121 |
701 |
20 |
2 |
2021 |
7002 |
9:00 |
23 |
119 |
702 |
19 |
2 |
2021 |
7003 |
8:00 |
25 |
128 |
702 |
19 |
2 |
2021 |
7003 |
8:30 |
23 |
123 |
702 |
19 |
2 |
2021 |
7003 |
9:00 |
23 |
124 |
702 |
19 |
2 |
2021 |
7003 |
9:30 |
23 |
119 |
702 |
20 |
2 |
2021 |
7003 |
8:00 |
24 |
119 |
702 |
21 |
2 |
2021 |
7003 |
9:30 |
25 |
128 |
702 |
22 |
2 |
2021 |
7003 |
9:30 |
26 |
119 |
702 |
22 |
2 |
2021 |
7003 |
10:00 |
21 |
120 |
-
1 Attachment(s)
Hi Sandy,
The exported xml file looks like -
Code:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<DataImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<forecast>
<Entity>700</Entity>
<data>
<date>
<day>19</day>
<month>2</month>
<year>2021</year>
</date>
<period recID="7001">
<time>8:00</time>
<ItemSold>23</ItemSold>
<Price>119</Price>
</period>
</data>
</forecast>
<forecast>
<Entity>700</Entity>
<data>
<date>
<day>19</day>
<month>2</month>
<year>2021</year>
</date>
<period recID="7001">
<time>8:30</time>
<ItemSold>25</ItemSold>
<Price>120</Price>
</period>
</data>
</forecast>
However, what I need is -
Code:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<DataImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<forecast>
<Entity>700</Entity>
<data>
<date>
<day>19</day>
<month>2</month>
<year>2021</year>
</date>
<period recID="7001">
<time>8:00</time>
<ItemSold>23</ItemSold>
<Price>119</Price>
</period>
<period recID="7001">
<time>8:30</time>
<ItemSold>25</ItemSold>
<Price>120</Price>
</period>
</data>
</forecast>
<forecast>
<Entity>701</Entity>
<data>
<date>
<day>19</day>
<month>2</month>
<year>2021</year>
</date>
<period recID="7002">
<time>8:00</time>
<ItemSold>23</ItemSold>
<Price>121</Price>
</period>
</data>
</forecast>
</DataImport>
Only one entry for each unique entity ID and one date entry for each unique date within each entity tag.
Adding another sample file with few examples, hope it helps.
Is there a way we can do this via VBA if not possible by just using XML mapping?
-
I hate vba so I can't help
Have a nice day
-
Hey Sandy,
Is not possible via XML mapping? I'm not restricted to VBA, is there a way to get this done?
-
Hi Alan,
Did you get a chance to look into it? I had shared bigger image as you mentioned.
