Need help to convert Excel data to XML

**sandy666** · 02-15-2021, 01:55 PM

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:  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** · 02-15-2021, 02:55 PM

Originally Posted by uakash7

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** · 02-17-2021, 12:08 PM

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

Code:

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
1	Entity ID	day	month	year	time
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** · 02-17-2021, 12:51 PM

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** · 02-17-2021, 01:35 PM

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** · 02-17-2021, 03:24 PM

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

Code:

'    <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** · 02-17-2021, 04:46 PM

Yes, you have got it correct.

**DocAElstein** · 02-17-2021, 07:31 PM

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/showthrea...ll=1#post15365

Alan

**uakash7** · 02-19-2021, 02:43 PM

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** · 02-19-2021, 05:14 PM

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