Solved

XML / CSV Data Manipulation

Posted on 2014-01-18
4
202 Views
Last Modified: 2014-11-03
I have 4 separate sets of data, each in 3 formats (.csv, .xml & .xls) and 1 excel spread sheet which contains a complete dump of a supplier's product list (over 35,000 items), the other 4 data sets contain the following information for each specific item contained within the product list dump:-

- Styles
- Icons
- Image filenames
- Descriptive data

The separate data sets can be linked together using a "style ID" which is replicated across each data set, I would like to link all data together and ensure the data is matched to the correct product.

The method I'm currently using (from within MS Excel) is to dump all data in to one "master" spread sheet and use the VLOOKUP function to pull back the relevant values. Although this is useful, it's still extremely laborious to perform for over 20,000 product ID's on 35,000 items.

The process needs to be automated and some products share the same style ID (e.g. 20 T-Shirts have style ID ABC1234, information for Style ID ABC1234 is stored in separate spread sheet and has adjacent cell containing description for product).

--------------------------------------
EXAMPLE
--------------------------------------

I need formulae / functionality to do the following:-

- Lookup style ID
- Pull back descriptive data contained in adjacent cell and dump into specified cell reference
- Move on to next style ID and perform same action in loop

Also, the style ID's have no sequential order, they are a numeric/text mixture and I'm unsure how to automate this as Excel has no way of knowing which style ID to move on to next as they are custom values, as mentioned above there are over 20,000)

What's the easiest / most automated way or achieving this?

Cheers,

Craig
0
Comment
Question by:mparfitt
[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
4 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39791562
Please reduce the data sets to about 5-10 data elements and post the data, in the form of the SSCCE.  We can probably show you the principles that will be applicable to the larger data set.
0
 

Accepted Solution

by:
mparfitt earned 0 total points
ID: 39798226
Please mark this thread as closed as I have now spotted my mistake.

Thanks for your time.
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40419008
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This article discusses how to implement server side field validation and display customized error messages to the client.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

717 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