To see less ads Register or Login ----- Daily Fantasy Sports games 18+

XML and Excel

A forum to discuss or share knowledge of spreadsheets.
Post Reply
User avatar
crispybits
Dumbledore
Posts: 5499
Joined: 10 Aug 2010, 09:18
FS Record: FISO Apprentice Champion 2011-2012, Xpert11 FBT winner season 7

XML and Excel

Post by crispybits »

Ello peeps

I have a problem that I just want to check the viability of my solution for before I try and make it actually work....

I have a lot of address lists to work with at work. Each one is in a different format and each time they need to be used they need to be converted to a specific template format.

I can't just keep them once they are converted because they change from month to month (they are subscriber lists for magazines and journals) so each time I need to use one of them I need to convert it fresh from a fresh data download.

I can't get them sent to me in the correct format because there are well over 300 different customers, all of whom have established databases that run into their other systems and getting them all to change would be a nightmare (plus there are new customers coming in all the time)

So... I need to find some way to at least mostly automate the variable data coming in to go into a set structure that is the same every time. Within this structure the town, state/county and country all need to be in specific cells.

I have found Yahoo Placefinder which seems to me a good way to automate pulling out these fields as part of the xml string it outputs looks like the following:

Code: Select all

<city>Stevenage</city>
<county>Hertfordshire</county>
<state>England</state>
<country>United Kingdom</country>
<countrycode>GB</countrycode>
<statecode>ENG</statecode>
<countycode>HRT</countycode>
<uzip>AL4 0</uzip>
What I would need to do to access this is to create a query that is a web address for each line of data by concatenating several fields into a predefined cell later in the row, and then have excel be able to read the xml code returned by that URL (as above) to populate several fields within the same row from the XML structure.

So in one row, columns A-M might be data, I would create a web address to call up the XML output by concatenating selected cells from that in column N, which would then automatically fill in columns O, P, Q and R with city, county, state, and country.

Then the plan would be to have a second worksheet where a deduplication setup is active to remove any of the information pulled by this XML query from the original cells in the data, and a third tab to copy/paste the final result into to do the final manual checking and refining stage. Then I can copy/paste that data into our other programs having cleaned it all up nicely but with a fraction of the mind-numbing manual checking that I have to do right now (and which is very error-prone for obvious reasons)

I'm not looking for a detailed guide as I know there are plenty out there right now for similar-ish tasks, but before I spend massive amounts of time trying to build a very complicated spreadsheet to do this I want to run it past a few people to check that what I'm going to ask the software to do is actually possible. Failing it being possible this way it seems to me my only other option would be to learn a programming language such as Python to the extent needed to get this done, and that's a whole barrel-load more work than it would be getting this stuff from Excel like this.

Fingers crossed you agree that this sounds technically possible and any handy tips for good websites to get more detailed excel help from for projects of this complexity (because as I say I'm not expecting that level of help on a fantasy football forum) would be much appreciated.

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: XML and Excel

Post by Surprised »

will you be using a Visual Basic script to run the updates?

User avatar
crispybits
Dumbledore
Posts: 5499
Joined: 10 Aug 2010, 09:18
FS Record: FISO Apprentice Champion 2011-2012, Xpert11 FBT winner season 7

Re: XML and Excel

Post by crispybits »

I'm not sure - I will be using whatever works best (sorry, I know that's probably not helpful but I've never attempted to write something this complex before)

I don't mind having to learn VBA or Python or anything else if it's the best way to get it done, I just need pointers so I don't waste time learning something un-necessary (I've convinced them to outsource this work to me as a private contractor so the more I can streamline this the less I have to do every evening after my normal working hours to get paid a nice little extra wodge of cash each month)

All I have at the moment is a whole bunch of subscriber files and a template of cell column headers to put it into - the rest I am trying to simply find the most efficient way to deal with things.

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: XML and Excel

Post by Surprised »

Once you get the data in a standard format from yahoo placefnder then it is fairly easy to extract the bits you want if the fields that placefinder gives are the same each time.
Building a web address for the web query by taking data from different cells is far more complicated but I think it is achievable. It would eed a separate query for each request though. I'm not sure how many variables you would need to send to placefinder or how many it requires.

User avatar
crispybits
Dumbledore
Posts: 5499
Joined: 10 Aug 2010, 09:18
FS Record: FISO Apprentice Champion 2011-2012, Xpert11 FBT winner season 7

Re: XML and Excel

Post by crispybits »

Yeah that's the bit I'm struggling to work out - is it even possible to do a separate query for each row and without saving a separate xml file each time to then auto-import just placing data from that straight into the same workbook? I've seen tutorials about how to import xml tables, but this would require me to either save as many files as there are addresses or build some sort of macro to continually paste the xml from each query onto the bottom of an existing xml document and then import that at the very end.

I've signed up for a few different specialist excel forums and am in the process of spamming them all with the query so hopefully will get answers soon. FISO always has been amazing at having people who respond quickly on just about any topic on earth tho so i figured I'd see if we had anyone here who knew :P 8-)

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: XML and Excel

Post by Surprised »

I think the only way to run multiple web queries on one sheet is to eithey write a macro or set up each query separately with a different name. A bit beyond my abilities I'm afraid. I did it a while ago with help when the telegraph had a database of premiership footballers all set up with the fantasy codes, goals, assistes etc for the last 3 seasons. It allowed you to compare 2 players side by side but I downloaded the entire database into 1 excel sheet with a constantly changing web query that pulled data from 2 cells. It didn't help me do well though.

View Latest: 1 Day View Your posts
Post Reply

Return to “Spreadsheets”