Need help to convert Excel data to XML

[uakash7]

Hi,

I'm trying to convert Excel data to XML but getting this error message - "Cannot save or export XML data. The XML maps in the workbook are not exportable."
Please help to fix this, enclosing the error screenshot, Excel file and XML mapping file (uploaded as .txt file as I was unable to upload .xml file, please save as .xml). Thanks.







Hello uakash7
Welcome to ExcelFox

Please can you try to upload larger images, minimum 50KB, - your ExportError.jpg is 20.4KB , - this is small in forum upload and is difficult to see.

Thanks,
Alan

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://eileenslounge.com/viewtopic.php?p=244184#p244184 (https://eileenslounge.com/viewtopic.php?p=244184#p244184)
https://eileenslounge.com/viewtopic.php?p=246586#p246586 (https://eileenslounge.com/viewtopic.php?p=246586#p246586)
https://eileenslounge.com/viewtopic.php?p=246112#p246112 (https://eileenslounge.com/viewtopic.php?p=246112#p246112)
https://eileenslounge.com/viewtopic.php?p=246112#p246112 (https://eileenslounge.com/viewtopic.php?p=246112#p246112)
https://eileenslounge.com/viewtopic.php?p=245761#p245761 (https://eileenslounge.com/viewtopic.php?p=245761#p245761)
https://eileenslounge.com/viewtopic.php?p=245722#p245722 (https://eileenslounge.com/viewtopic.php?p=245722#p245722)
https://eileenslounge.com/viewtopic.php?p=245616#p245616 (https://eileenslounge.com/viewtopic.php?p=245616#p245616)
https://eileenslounge.com/viewtopic.php?p=247043#p247043 (https://eileenslounge.com/viewtopic.php?p=247043#p247043)
https://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use (https://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use)
https://eileenslounge.com/viewtopic.php?p=245238#p245238 (https://eileenslounge.com/viewtopic.php?p=245238#p245238)
https://eileenslounge.com/viewtopic.php?p=245131#p245131 (https://eileenslounge.com/viewtopic.php?p=245131#p245131)
https://eileenslounge.com/viewtopic.php?f=18&t=31638 (https://eileenslounge.com/viewtopic.php?f=18&t=31638)
https://eileenslounge.com/viewtopic.php?p=244579#p244579 (https://eileenslounge.com/viewtopic.php?p=244579#p244579)
https://eileenslounge.com/viewtopic.php?p=244648#p244648 (https://eileenslounge.com/viewtopic.php?p=244648#p244648)
https://eileenslounge.com/viewtopic.php?p=244647#p244647 (https://eileenslounge.com/viewtopic.php?p=244647#p244647)
https://eileenslounge.com/viewtopic.php?p=244577#p244577 (https://eileenslounge.com/viewtopic.php?p=244577#p244577)
https://eileenslounge.com/viewtopic.php?p=245201#p245201 (https://eileenslounge.com/viewtopic.php?p=245201#p245201)
https://eileenslounge.com/viewtopic.php?p=243975#p243975 (https://eileenslounge.com/viewtopic.php?p=243975#p243975)
https://eileenslounge.com/viewtopic.php?p=243884#p243884 (https://eileenslounge.com/viewtopic.php?p=243884#p243884)
https://eileenslounge.com/viewtopic.php?p=242439#p242439 (https://eileenslounge.com/viewtopic.php?p=242439#p242439)
https://eileenslounge.com/viewtopic.php?p=243595#p243595 (https://eileenslounge.com/viewtopic.php?p=243595#p243595)
https://eileenslounge.com/viewtopic.php?p=243589#p243589 (https://eileenslounge.com/viewtopic.php?p=243589#p243589)
https://eileenslounge.com/viewtopic.php?p=243589#p243589 (https://eileenslounge.com/viewtopic.php?p=243589#p243589)
https://eileenslounge.com/viewtopic.php?p=243002#p243002 (https://eileenslounge.com/viewtopic.php?p=243002#p243002)
https://www.eileenslounge.com/viewtopic.php?p=242761#p242761 (https://www.eileenslounge.com/viewtopic.php?p=242761#p242761)
https://eileenslounge.com/viewtopic.php?p=242459#p242459 (https://eileenslounge.com/viewtopic.php?p=242459#p242459)
https://eileenslounge.com/viewtopic.php?p=242054#p242054 (https://eileenslounge.com/viewtopic.php?p=242054#p242054)
https://eileenslounge.com/viewtopic.php?p=241404#p241404 (https://eileenslounge.com/viewtopic.php?p=241404#p241404)
https://eileenslounge.com/viewtopic.php?p=229145#p229145 (https://eileenslounge.com/viewtopic.php?p=229145#p229145)
https://eileenslounge.com/viewtopic.php?p=228710#p228710 (https://eileenslounge.com/viewtopic.php?p=228710#p228710)
https://eileenslounge.com/viewtopic.php?p=226938#p226938 (https://eileenslounge.com/viewtopic.php?p=226938#p226938)
https://eileenslounge.com/viewtopic.php?f=18&t=28885 (https://eileenslounge.com/viewtopic.php?f=18&t=28885)
https://eileenslounge.com/viewtopic.php?p=222689#p222689 (https://eileenslounge.com/viewtopic.php?p=222689#p222689)
https://eileenslounge.com/viewtopic.php?p=221622#p221622 (https://eileenslounge.com/viewtopic.php?p=221622#p221622)
https://eileenslounge.com/viewtopic.php?f=27&t=22512 (https://eileenslounge.com/viewtopic.php?f=27&t=22512)
https://eileenslounge.com/viewtopic.php?f=26&t=26183 (https://eileenslounge.com/viewtopic.php?f=26&t=26183)
https://eileenslounge.com/viewtopic.php?f=26&t=26030 (https://eileenslounge.com/viewtopic.php?f=26&t=26030)
https://eileenslounge.com/viewtopic.php?p=202322#p202322 (https://eileenslounge.com/viewtopic.php?p=202322#p202322)
https://www.excelforum.com/word-formatting-and-general/1174522-finding-a-particular-word-phrase-in-word.html#post4604396 (https://www.excelforum.com/word-formatting-and-general/1174522-finding-a-particular-word-phrase-in-word.html#post4604396)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[sandy666]

you can try with

<?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 (https://www.w3.org/2008/xmlsec/Drafts/xml-norm/Overview.html#:~:text=XML%20Normalization%20define s%20a%20means,the%20two%20are%20not%20interchangea ble.)
and
Normalizing XML link (https://www.xml.com/pub/a/2002/11/13/normalizing.html)

[sandy666]

or try this Excel to XML
(https://conversiontools.io/convert/excel-to-xml)
3503
remove .txt and stay with .xml
and after mapping
3504

[uakash7]

Thank you for looking into it, here is a bigger screenshot

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://eileenslounge.com/viewtopic.php?p=244184#p244184 (https://eileenslounge.com/viewtopic.php?p=244184#p244184)
https://eileenslounge.com/viewtopic.php?p=246586#p246586 (https://eileenslounge.com/viewtopic.php?p=246586#p246586)
https://eileenslounge.com/viewtopic.php?p=246112#p246112 (https://eileenslounge.com/viewtopic.php?p=246112#p246112)
https://eileenslounge.com/viewtopic.php?p=246112#p246112 (https://eileenslounge.com/viewtopic.php?p=246112#p246112)
https://eileenslounge.com/viewtopic.php?p=245761#p245761 (https://eileenslounge.com/viewtopic.php?p=245761#p245761)
https://eileenslounge.com/viewtopic.php?p=245722#p245722 (https://eileenslounge.com/viewtopic.php?p=245722#p245722)
https://eileenslounge.com/viewtopic.php?p=245616#p245616 (https://eileenslounge.com/viewtopic.php?p=245616#p245616)
https://eileenslounge.com/viewtopic.php?p=247043#p247043 (https://eileenslounge.com/viewtopic.php?p=247043#p247043)
https://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use (https://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use)
https://eileenslounge.com/viewtopic.php?p=245238#p245238 (https://eileenslounge.com/viewtopic.php?p=245238#p245238)
https://eileenslounge.com/viewtopic.php?p=245131#p245131 (https://eileenslounge.com/viewtopic.php?p=245131#p245131)
https://eileenslounge.com/viewtopic.php?f=18&t=31638 (https://eileenslounge.com/viewtopic.php?f=18&t=31638)
https://eileenslounge.com/viewtopic.php?p=244579#p244579 (https://eileenslounge.com/viewtopic.php?p=244579#p244579)
https://eileenslounge.com/viewtopic.php?p=244648#p244648 (https://eileenslounge.com/viewtopic.php?p=244648#p244648)
https://eileenslounge.com/viewtopic.php?p=244647#p244647 (https://eileenslounge.com/viewtopic.php?p=244647#p244647)
https://eileenslounge.com/viewtopic.php?p=244577#p244577 (https://eileenslounge.com/viewtopic.php?p=244577#p244577)
https://eileenslounge.com/viewtopic.php?p=245201#p245201 (https://eileenslounge.com/viewtopic.php?p=245201#p245201)
https://eileenslounge.com/viewtopic.php?p=243975#p243975 (https://eileenslounge.com/viewtopic.php?p=243975#p243975)
https://eileenslounge.com/viewtopic.php?p=243884#p243884 (https://eileenslounge.com/viewtopic.php?p=243884#p243884)
https://eileenslounge.com/viewtopic.php?p=242439#p242439 (https://eileenslounge.com/viewtopic.php?p=242439#p242439)
https://eileenslounge.com/viewtopic.php?p=243595#p243595 (https://eileenslounge.com/viewtopic.php?p=243595#p243595)
https://eileenslounge.com/viewtopic.php?p=243589#p243589 (https://eileenslounge.com/viewtopic.php?p=243589#p243589)
https://eileenslounge.com/viewtopic.php?p=243589#p243589 (https://eileenslounge.com/viewtopic.php?p=243589#p243589)
https://eileenslounge.com/viewtopic.php?p=243002#p243002 (https://eileenslounge.com/viewtopic.php?p=243002#p243002)
https://www.eileenslounge.com/viewtopic.php?p=242761#p242761 (https://www.eileenslounge.com/viewtopic.php?p=242761#p242761)
https://eileenslounge.com/viewtopic.php?p=242459#p242459 (https://eileenslounge.com/viewtopic.php?p=242459#p242459)
https://eileenslounge.com/viewtopic.php?p=242054#p242054 (https://eileenslounge.com/viewtopic.php?p=242054#p242054)
https://eileenslounge.com/viewtopic.php?p=241404#p241404 (https://eileenslounge.com/viewtopic.php?p=241404#p241404)
https://eileenslounge.com/viewtopic.php?p=229145#p229145 (https://eileenslounge.com/viewtopic.php?p=229145#p229145)
https://eileenslounge.com/viewtopic.php?p=228710#p228710 (https://eileenslounge.com/viewtopic.php?p=228710#p228710)
https://eileenslounge.com/viewtopic.php?p=226938#p226938 (https://eileenslounge.com/viewtopic.php?p=226938#p226938)
https://eileenslounge.com/viewtopic.php?f=18&t=28885 (https://eileenslounge.com/viewtopic.php?f=18&t=28885)
https://eileenslounge.com/viewtopic.php?p=222689#p222689 (https://eileenslounge.com/viewtopic.php?p=222689#p222689)
https://eileenslounge.com/viewtopic.php?p=221622#p221622 (https://eileenslounge.com/viewtopic.php?p=221622#p221622)
https://eileenslounge.com/viewtopic.php?f=27&t=22512 (https://eileenslounge.com/viewtopic.php?f=27&t=22512)
https://eileenslounge.com/viewtopic.php?f=26&t=26183 (https://eileenslounge.com/viewtopic.php?f=26&t=26183)
https://eileenslounge.com/viewtopic.php?f=26&t=26030 (https://eileenslounge.com/viewtopic.php?f=26&t=26030)
https://eileenslounge.com/viewtopic.php?p=202322#p202322 (https://eileenslounge.com/viewtopic.php?p=202322#p202322)
https://www.excelforum.com/word-formatting-and-general/1174522-finding-a-particular-word-phrase-in-word.html#post4604396 (https://www.excelforum.com/word-formatting-and-general/1174522-finding-a-particular-word-phrase-in-word.html#post4604396)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[uakash7]

Thank you Sandy, I tried both but it didn't give me the desired output. I'm looking for output like this -


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


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

[sandy666]

here is a proper xml for mapping
3506
if you will see warning choose first
3507
and here is exported xml from the table
3508

Table

EntityIDdaymonthyearrecIDtimeItemSoldPrice


700
19
2
2021
70018:00
23
119


700
19
2
2021
70018:30
25
120


700
19
2
2021
70019:00
24
121


700
19
2
2021
70019:30
26
110


700
19
2
2021
700110:00
21
119


700
19
2
2021
700110:30
20
119


700
19
2
2021
700111:00
19
120


700
19
2
2021
700111:30
10
119


701
19
2
2021
70028:00
23
121


701
19
2
2021
70028:30
23
123


701
19
2
2021
70029:00
22
119


701
19
2
2021
70029:30
23
119


701
19
2
2021
700210:00
25
119


701
20
2
2021
70028:00
23
145


701
20
2
2021
70028:30
27
121


701
20
2
2021
70029:00
23
119


702
19
2
2021
70038:00
25
128


702
19
2
2021
70038:30
23
123


702
19
2
2021
70039:00
23
124


702
19
2
2021
70039:30
23
119


702
20
2
2021
70038:00
24
119


702
21
2
2021
70039:30
25
128


702
22
2
2021
70039:30
26
119


702
22
2
2021
700310:00
21
120

[uakash7]

Hi Sandy,

The exported xml file looks like -


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


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

[sandy666]

I hate vba so I can't help
Have a nice day

[uakash7]

Hey Sandy,

Is not possible via XML mapping? I'm not restricted to VBA, is there a way to get this done?

[uakash7]

Hi Alan,

Did you get a chance to look into it? I had shared bigger image as you mentioned.

[sandy666]

Create required.xml from the table post#6 where is well-formed xml (required.txt) which can be used to export xml from whole table
attach file or paste raw xml code via [CODE] tags
your xml should contain Start and End xml code not cut in half
btw. comment in xml code looks like: <!--your comment--> not like M-code comment!

for the future:

Issue: the map cannot be exported.

An XML mapping cannot be exported if the mapped element's relationship with other elements cannot be preserved. This relationship may not be preserved for the following reasons:
◾The schema definition of a mapped element is contained within a sequence for which the following are true:
◾The maxoccurs attribute is not equal to 1.
◾The sequence has more than one direct child element defined, or it has another compositor as a direct child.
◾Nonrepeating sibling elements with the same repeating parent element are mapped to different XML tables.
◾Multiple repeating elements are mapped to the same XML table, and the repetition is not defined by an ancestor element.
◾Child elements from different parents are mapped to the same XML table.

Additionally, the contents of an XML mapping cannot be exported if the contents contain one of the following XML schema constructs:
◾ List of lists One list of items contains a second list of items.
◾ Denormalized data An XML table contains an element that has been defined in the schema to occur once (the maxoccurs attribute is set to 1). When you add such an element to an XML table, the table column is filled with multiple instances of the element.
◾ Choice This is a mapped element that is part of a <choice> schema construct.

The following rules about using XML maps are important to know:
◾A workbook can contain one or more XML maps.
◾You can only map one element to one location in a workbook at a time.
◾Each XML map is an independent entity, even if multiple XML maps in the same workbook refer to the same schema.
◾An XML map can only contain one root element. If you add a schema that defines more than one root element, you are prompted to choose the root element to use for the new XML map.

[DocAElstein]

Hi Alan,
Did you get a chance to look into it?.
Hi
Sorry, I know nothing about XML
If you can establish some consistent pattern between
_ what you get
and
_ what you want
then we might be able to develop some VBA solution to do a conversion.
But I have no idea if such a way of doing something relatzred to XML stuff is any use, or sensible, due to my total lack of knowledge about anything at all to do with XML
I don’t even have a clue what XML is. It seems to be some way of storing and transferring data, but I have never done anything with it.

Alan

[uakash7]

Hi Alan,

For now just forget about XML and help me to print data from excel file to text file in a different format -

Data is Excel file


Entity ID day month year time
700 19 2 2021 8:00
700 19 2 2021 8:30
700 20 2 2021 9:00
701 19 2 2021 9:30
Entity ID day month year time
700 19 2 2021 8:00
700 19 2 2021 8:30
700 20 2 2021 9:00
701 19 2 2021 9:30
_____ Workbook: Sample excel file.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F

1Entity IDdaymonthyeartime


2
700
19
2
2021
08:00


3
700
19
2
2021
08:30


4
700
20
2
2021
09:00


5
701
19
2
2021
09:30


6
Worksheet: Sheet1

Just print this in a text file, starting from first row in excel file -

#STEP 1 Start

Print #intFile, "<Forecast>"
Print #intFile, "<Entity>" & Entity ID & "</Entity>"

#STEP 2 Start
Print #intFile, "<Data>"
Print #intFile, "<date>"
Print #intFile, "<day>" & day & "</day><month>" & month & "</month><year>" & yeear & "</year></date>"

#STEP 3 START

Print #intFile, "<time>" & time & "</time>"

#STEP 3 END

Print #intFile, "</data>"

STEP 2 END

Print #intFile, "</forecast>"

STEP 1 END

Check
if Entity ID in first row = Entity ID in 2nd row and date in first row = date in 2nd row then
repeat STEP 3 for 2nd row and so on

if Entity ID in first row = Entity ID in 2nd row and date in first row not equals to date in 2nd row then
repeat STEP 3 for 2nd row and so on

If Entity ID is not same as in previous row repeat STEP 1

The output in text file should look like

<forecast>
<Entity>700</Entity>
<data>
<date>
<day>19</day>
<month>2</month>
<year>2021</year>
</date>
<time>8:00</time>
<time>8:30</time>
</data>
<data>
<date>
<day>20</day>
<month>2</month>
<year>2021</year>
</date>
<time>8:00</time>
</data>
</forecast>
<forecast>
<Entity>701</Entity>
<data>
<date>
<day>19</day>
<month>2</month>
<year>2021</year>
</date>
<time>9:30</time>
</data>
</forecast>
<forecast>

Trying to help you to help me :)



Alan testing....

<forecast>
<Entity>700</Entity>
<data>
<date>
<day>19/<day>
<month>2</month>
<year>2021</year>
</date>
<time>08:00</time>
<time>08:30</time>
</data>
<data>
<date>
<day>20/<day>
<month>2</month>
<year>2021</year>
</date>
<time>09:00</time>
</data>
</forcast>
<forecast>
<Entity>701</Entity>
<data>
<date>
<day>19/<day>
<month>2</month>
<year>2021</year>
</date>
<time>09:30</time>
</data>
</forcast>

[DocAElstein]

Sure , that is quite easy in VBA.
I will post you a solution when I have time.

I expect doing it in some way as sandy suggested might be the more normal and proper way. I expect an optimised built in way would be much more efficient than a VBA coding of mine, but I will do a solution anyway.
Alan

[uakash7]

Thank you Alan.

I tried what Sandy suggested but it's printing all the lines and repeating all the entries.
In fact, I've been doing it in that way unless I got this new format. If you build me the logic in VBA, I would plug it in to get desired outputs.

[DocAElstein]

Hello uakash7
Can you check that I have understood correctly your logic ( I think you have a couple of typos in your explanation and shown output )

??3??

??8:00??




' <forecast> ' #STEP 1 Start Print #intFile, "<Forecast>"
' <Entity>700</Entity> ' #STEP 1 Start Print #intFile, "<Entity>" & Entity ID & "</Entity>"
' <data> ' #STEP 2 Start Print #intFile, "<Data>"
' <date> ' #STEP 2 Start Print #intFile, "<date>"
' <day>19</day> ' #STEP 2 Start Print #intFile, "<day>" & day &
' <month>2</month> ' #STEP 2 Start "</day><month>" & month & "</month>
' <year>2021</year> ' #STEP 2 Start <year>" & yeear & "</year>"
' </date> ' #STEP 2 Start </date>"
' <time>8:00</time> ' #STEP 3 START Print #intFile, "<time>" & time & "</time>"
' Check if Entity ID in first row = Entity ID in 2nd row
' and date in first row = date in 2nd row then
' <time>8:30</time> ' repeat STEP 3 for 2nd row and so on
' </data> ' #STEP 3 END
' Check if Entity ID in first row = Entity ID in 2nd row
' and date in first row IS NOT = date in 2nd row then'
' repeat STEP ??3?? 2 for 2nd row and so on
' <data>
' <date>
' <day>20</day>
' <month>2</month>
' <year>2021</year>
' </date>
' <time> ??8:00?? 9.00 </time>
' </data>
' </forecast> ' STEP 2 END Print #intFile, "</forecast>"


' If Entity ID is not same as in previous row repeat STEP 1
'
' <forecast>
' <Entity>701</Entity>
' <data>
' <date>
' <day>19</day>
' <month>2</month>
' <year>2021</year>
' </date>
' <time>9:30</time>
' </data>
' </forecast>
' <forecast>




Alan

[uakash7]

Yes, you have got it correct.
:)

[DocAElstein]

Hi
There are lots of ways in VBA to manipulate Excel ranges and text files.
The way I am doing it for you is just a way I use sometimes. It’s probably not the most efficient.
Its based on your test data and explanations. It works for your data, at least i think so ...
( I have also assumed that you don’t want the last "<forecast>" that you showed )

I think this is doing what you want, at least with the given test data I think it gets the correct results.
It makes a text file, XML_Stuff.txt , that, at first glance, seems to do what you want.

But I have not tested it thoroughly: I leave it to you to check thoroughly.

Here is the macro and other info:
https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=15365&viewfull=1#post15365


Alan

[uakash7]

Thanks Alan, I'm still testing it, just found one thing. It's not printing time from excel file but 00:00 every time.

<forecast>
<Entity>700</Entity>
<data>
<date>
<day>19/<day>
<month>2</month>
<year>2021</year>
</date>
<time>00:00</time>
<time>00:00</time>
<time>00:00</time>
<time>00:00</time>
<time>00:00</time>
<time>00:00</time>
<time>00:00</time>
<time>00:00</time>

Can you please check.

[DocAElstein]

Hi
I re checked the file I uploaded on a few other systems and still get the correct result.
Time and date formats in Excel often cause problems. But usually we can get over them
If you can pass me an Excel file that demos the problem then I will take a look.
Please keep the file small, just enough data to demo the problem.

Alan

[uakash7]

Hi Alan,

Please see the enclosed excel sheet, I tried with multiple date/text format and different machine. However, it's still giving "00:00" instead of actual time.
Enclosing the text file as well.

Thanks

[DocAElstein]

Hi

Your file is different to the type you told me before. My macro was written to work on the file type you gave. ( You have an extra column in your latest file ). ( I am surprised you did not notice that? )

The macro that I wrote works assuming specific headings, in particular those you gave me.

This is the sort of test data you were talking to me about before ( https://excelfox.com/forum/showthread.php/2710-Need-help-to-convert-Excel-data-to-XML?p=15351&viewfull=1#post15351 )

_____ Workbook: Sample excel file.xls ( Using Excel 2007 32 bit )
Row\ColABCDE
1Entity IDdaymonthyeartime

2700192202108:00

3700192202108:30
Worksheet: Sheet1

The macro I wrote works on that and gives the results you asked for ( https://excelfox.com/forum/showthread.php/2710-Need-help-to-convert-Excel-data-to-XML?p=15351&viewfull=1#post15351 )
( You would have seen that if you had downloaded and tried the macro in the sample workbook I did for you ( https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=15365&viewfull=1#post15365 ) )



Your last uploaded workbook , xlFox1.xlsm , looks like this

_____ Workbook: xlFox1.xlsm ( Using Excel 2007 32 bit )
Row\ColABCDEFG
1Entity IDdaymonthyearrecIDtime

2700192202170018:00

3700192202170018:30
Worksheet: Sheet1



If I look back over the Thread we can see you have had a few different file formats, with different headings.

Maybe you should check and make sure you know what you want., that is to say what headings you are going to have and what output you want.

If you want a macro which will give you the results that you want for any different set of headings, then that probably can be done, but would be quite a long and complicated macro

The macro I wrote was for the specific headings you gave to me in the test data
Entity ID __ day __ month __ year __ time


You have two options
_ (i) A macro or macros, each for a specific set of headings
or
_ (ii) A more complicated single macro that will work on different headings

In case (i) it is essential to know the headings, as obviously I can’t guess.
In case (ii) it would still be helpful to know what might be typical headings


If you just want a macro for your last file, then you might be able to figure out yourself the modifications that you need to do to my macro


Alan

[kanetom]

Hi Uakash,

If you're still in trouble please try to use this online free tool below Excel to XML converter.
https://onlineconvertfree.com/convert-format/xls-to-xml/
It's really great. And I hope this will work for you.

[uakash7]

Thank you Alan for all your help and support, I did final testing on my data today and with just some edits it's working for me as I wanted.
You're a champ. Thanks again and hope to connect with you soon.

[DocAElstein]

Thx for the feedback
Alan