Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

XML / CSV Data Manipulation

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
mparfitt
Asked:
mparfitt
1 Solution
 
Ray PaseurCommented:
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
 
mparfittAuthor Commented:
Please mark this thread as closed as I have now spotted my mistake.

Thanks for your time.
0
 
Martin LissRetired ProgrammerCommented:
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now