Extract data from few XML file into Excel.

Posts
14
Registration date
Thursday May 30, 2019
Status
Member
Last seen
June 13, 2019
- - Latest reply: ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
- Jun 14, 2019 at 09:16 AM
Hello, i hope someone will be able to assist me, and i hope it is possible to do what i need.

I need to extract specific data from a few XML files into excel.

<?xml version="1.0" encoding="utf-8"?>
<io:root xmlns:io="http://www.website.com/ss/ff/exchange">
<io:entry>
<io:version>
17
</io:version>
<io:entry_ref>
166699
</io:entry_ref>
<io:entry_id>
spec-dd-info
</io:entry_id>
<io:category>
en-spec-off
</io:category>

it is just the head of the file, but i need only "entry_ref" and "entry_id".

so, for example, I have 5 files in the folder, and I need transfer this refNR and ID from all of them into the specific row. lets say ID into C1 - C2 - C3 - C4 - C5 and refNR into D1 - D2 - D3 - D4 - D5.

Thank you.



System Configuration: Windows / Chrome 74.0.3729.157
See more 

3 replies

Best answer
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1530
1
Thank you
Click on data, connections, new, browse for more files, load the file you want.

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 6133 users have said thank you to us this month

ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1530 -
I am not certain why it is repeating, except that there might be two files in the directory?

To place them on one sheet, you will need a call to another subroutine, that counts the cell rows, and places the latest upload on the cellcount PLUS ONE!

FUNCTION COUNTROWS (whatsheet)
Countrows=cells(thisworkbook.worksheets (whatsheet).rows.count, 1).end (xlup).row
End FUNCTION

now to use it, you set it up like this in your loop:
Startrow=COUNTROWS("thesheetnameyouwanttocount")

NOW, you have a variable of Startrow that cooresponds with the last line entry. Make certain you increment the Startrow variable by one afyer it returns the count as to not rewrite the last cell, with the first kine of the newest upload. And rememeber, youmust count the rows in each iteration of the loop.
GizmoIE
Posts
14
Registration date
Thursday May 30, 2019
Status
Member
Last seen
June 13, 2019
-
dont have access to PC atm , but as i understood, this one will count the rows, and start adding data after the last one ? for example, we have data in 1-2-3-4-5 rows, its count the rows and will continue adding information on the 6th, or something like this?
ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1530 -
Yes, exaclty!
GizmoIE
Posts
14
Registration date
Thursday May 30, 2019
Status
Member
Last seen
June 13, 2019
-
for this reason i have this line there -
Append = MsgBox("Add to existing LIST (YES). Create new list (NO)", vbYesNo) = vbYes

which offer for me add information from the scratch or continue adding to already existing.

The issue here now, is that no matter what i chose ( it is working) and no matter how many files i am adding, the last file is added twice, and if selected 1 file the data from the file as well entered twice. if i will not gonna find the reason :) i will just add the line which will delete the last row, as it is duplicated.
ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1530 -
I will test and let you know what I find.
Respond to ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1530
1
Thank you
Running the code that you already have, without counting the rows, places the xml entries correctly on the sheet, one by one. I removed the last line of .databinding.refresh and it does not produce the last line being duplicated.


Give it a go!

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 6133 users have said thank you to us this month

GizmoIE
Posts
14
Registration date
Thursday May 30, 2019
Status
Member
Last seen
June 13, 2019
-
Yeah you right looks like it should work now, will check once back home :) thanks alot.
ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1530 -
Let us know! Thanks for staying in there, and learning the the process, as opposed to demanding I write code for you to cut and paste from, and not learning anything!!!!!
Respond to ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1530
0
Thank you
Just to clarify on my example; I used the following XML file and schema to map my sheet. I made three copies of the same file, naming them ....1.xml, ...2.xml, ....3.xml.

I only altered one line in your posted code; databindings.refresh.

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<Root>
<EmployeeInfo>
<Name>Jane Winston</Name>
<Date>2001-01-01</Date>
<EMPCODE>0001</EMPCODE>
</EmployeeInfo>
<ExpenseItem>
<Date>2001-01-01</Date>
<Description>Airfare</Description>
<Amount>500.34</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-01</Date>
<Description>Hotel</Description>
<Amount>200</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-01</Date>
<Description>Taxi Fare</Description>
<Amount>100.00</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-01</Date>
<Description>Long Distance Phone Charges</Description>
<Amount>57.89</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-01</Date>
<Description>Food</Description>
<Amount>82.19</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-02</Date>
<Description>Food</Description>
<Amount>17.89</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-02</Date>
<Description>Personal Items</Description>
<Amount>32.54</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-03</Date>
<Description>Taxi Fare</Description>
<Amount>75.00</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-03</Date>
<Description>Food</Description>
<Amount>36.45</Amount>
</ExpenseItem>
<ExpenseItem>
<Date>2001-01-03</Date>
<Description>New Suit</Description>
<Amount>750.00</Amount>
</ExpenseItem>
</Root>



With this code:


Sub Import_Xml()
Dim arrFiles, bAppend As Boolean, XMLFilePath
arrFiles = Application.GetOpenFilename("XML files,*.xml", , "Upload New xml", , True)
If Not IsArray(arrFiles) Then MsgBox "No file selected. Process will be canceled.": Exit Sub
bAppend = MsgBox("Add to existing LIST (YES). Create new list (NO)", vbYesNo) = vbYes
With ActiveWorkbook.XmlMaps("Root_Map")
For Each XMLFilePath In arrFiles
MsgBox ("Sheet: " & XMLFilePath)
.Import XMLFilePath, bAppend
bAppend = False
Next
'.DataBinding.Refresh
End With
End Sub


So, if your code, still does not produce what was shown in the above image of my sheet, then you need to look at the source of your data, as the schema that was published does not look to be complete, specifically there is no close to the ROOT (</root>)
Respond to ac3mark