Previous Topic Next Topic

Extract data from few XML file into Excel.

Closed
GizmoIE Posts 14 Registration date Thursday May 30, 2019 Status Member Last seen June 13, 2019 - May 30, 2019 at 06:32 AM
 Blocked Profile - 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
Related:

3 replies

Answer 1 / 3
Blocked Profile
May 30, 2019 at 07:01 AM
Click on data, connections, new, browse for more files, load the file you want.
1
GizmoIE Posts 14 Registration date Thursday May 30, 2019 Status Member Last seen June 13, 2019
Jun 6, 2019 at 03:15 AM
and how do i enter specific data into the cell ? as i can not build any connections on XML file . or i am doing something wrong ?
0
Blocked Profile
Updated on Jun 6, 2019 at 06:27 AM
Not certain what is stopping you from making connections. Any specific errors?

Why are you entering data into a cell? It is supposed to be imported by thw connection.
0
GizmoIE Posts 14 Registration date Thursday May 30, 2019 Status Member Last seen June 13, 2019
Jun 7, 2019 at 04:58 AM
It do not do anything.
0
GizmoIE Posts 14 Registration date Thursday May 30, 2019 Status Member Last seen June 13, 2019
Jun 7, 2019 at 06:06 AM
ok looks like i was able to pull data after doing the Maping of xml file. BUT its pull data only from one file. Every file have only 1 ID and RefNr. and i need to pull data from all files, so lets say 10 files, and i need to pull from each file 1 ID and 1 REFnr. is that possible to do it automatically without doing maping for each xml file ?
0
GizmoIE Posts 14 Registration date Thursday May 30, 2019 Status Member Last seen June 13, 2019
Jun 7, 2019 at 06:34 AM
heh i am a spammer . ok looks like i was able to do it , but in some reason when i have been doing the mapping, and when i pulling xml element into the worksheet it doesnt create the table in some reason, so i have created the table and droped elemt inside the table, and by doing the import it pull data from every imported files. But why its doesnt create the table automaticaly as far as i know, it should create.
0
Answer 2 / 3
Blocked Profile
Updated on Jun 13, 2019 at 09:31 AM
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!

1
GizmoIE Posts 14 Registration date Thursday May 30, 2019 Status Member Last seen June 13, 2019
Jun 13, 2019 at 09:33 AM
Yeah you right looks like it should work now, will check once back home :) thanks alot.
0
Blocked Profile
Jun 13, 2019 at 09:40 AM
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!!!!!
0
Answer 3 / 3
Blocked Profile
Jun 14, 2019 at 09:16 AM
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>)
0