Need help to convert Excel data to XML

[uakash7]

Hi Alan,

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

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