?
Solved

Update Excel Spreadsheet with Data from XML File

Posted on 2013-11-20
8
Medium Priority
?
808 Views
Last Modified: 2013-11-21
Hello -

I have an XML file that is generated by an Access database.  I need to update the existing rows with specific data from the xml file.

The Excel columns are
A - LnName
B - LnID
C - SiteInfo
D - Client

The fields in the XML file I need are
SiteInfo and Client

I need to look up the LnID from column B in the XML file and update columns C and D with the corresponding data from the XML file.  Similar to using a lookup formula.

I tried just importing the data from the XML and using an offset/match formula...  however, when I do that the data from the xml file loses it's formatting even though it looks right in the sheet that I imported the file to.  

So instead of looking like the below...

url - xxxxxxx
gd - xxxxxxx
dt - xxxxxxx

The data comes in like this...

url - xxxxxxxgd - xxxxxxxdt - xxxxxxx


I am somewhat familiar with VBA, but am definitely a novice, so any help you can provide would be awesome!

Thanks!
0
Comment
Question by:rsburge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39665149
It looks as though the xml data is not being imported correctly with the different elements in separate cells. It would really help if you could post an example of your xml file and the workbook you are using to import it it to. You should not have to use VBA for this provided your import splits up the data correctly.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39665172
On a different tack, if you are exporting xml data from Access, could you not export it in Excel format, or csv, which may be easier to handle?
0
 

Author Comment

by:rsburge
ID: 39665758
Thank you for your feedback.
Let me look into the excel export.  The original reason we didn't do that is because it was truncating the data from the memo field and the only way to get around that was to do an xml export.

The data in the two fields is combined using VBA and the VBA inserts the vbcrlf to get the next line in the cell.

Originally it was going from Access to another access database on another system.

Now, several of the users do not have Access, so the next solution that they are willing to work with is an Excel spreadsheet.

I am not able to post the xml file because it contains usernames and passwords.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 17

Expert Comment

by:andrewssd3
ID: 39665807
OK - but I can't help without at least seeing the structure of the file - could you just post a section with sensitive data removed?
0
 

Author Comment

by:rsburge
ID: 39665891
Attached is a sample of the xml, and the workbook sample.  

The Import tab is what the data looks like when I just do a straight import of the xml.

The Login tab is what the data looks like when I use the offset/match formula to pull the data in from the import tab.  You can see the data looks correct in the Import tab, but when the formula pulls it in, it is in a single line; I need it to stay formatted as it is in the Import tab.

Ultimately, when this is all complete, the user will be able to click on the lender name in the Active Lenders tab that will take them to the matching row on the Logins tab.  I think I know how to do this piece however, I may end up asking a new question if it doesn't work as I hope.

Thank you for your help.
xmlExportSample.xml
ExcelSample.xls
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 2000 total points
ID: 39666700
OK I see the problem - it's not quite what I thought - your MATCH seems to lose the linefeed characters from the looked up value.  Luckily all you need to do to fix this is to format the cell alignment as 'Wrap text' on the format cells...alignment dialog.

Hope this helps

Stuart
0
 

Author Comment

by:rsburge
ID: 39667043
Oh my gosh...  now don't I feel really dumb.  :)

I can't believe I didn't check that.  I am usually REALLY good with Excel.

Thank you so much for catching this my mistake!
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39667157
No it wasn't stupid - Excel automatically applies that formatting when you import or put a line feed in manually with alt-enter, but it doesn't know to do it when the data comes from a reference in a formula. It puzzled me for a while

Glad it's working now
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question