ETL - Generate xml files from excel data

I have a task where I need to generate a bunch of individual xml files based on data in an excel spreadsheet. The outputted xml is well defined in that there are a set of instructions that each individual xml file requires.

For example, the requirements include:

If Column F is empty, pass. If it contains a Value, proceed.

Take house Number from Column T & Enter into Filename.

Open XML Record.

Take house Number + "_SP" from Column T & Enter into and all attributes.

etc...

Essentially it will need to make a new .xml file for each row in the excel sheet with a value in column F.

I have previously used Visual Studio to do data transformation, but have been unable to find anything regarding producing xml files from VS. Is VS a good tool for this task?

Or is there a better option?
Member_2_6539617Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike in ITIT System AdministratorCommented:
Excel has built-in XML conversion. There is a simple walkthrough here that takes you through the steps. Basically, if you don't already have the Developer tab then you need to get it (how to do this is explained in the walkthrough).

  • First you create an XML schema (an example is given in the walkthrough)
  • Then on the Developer tab open the "Source" control and add your schema to it
  • Then you just have to drag and drop each column over tot he XML Source that it corresponds with (they should be done in order)
  • Then click on export and an XML file is created

You will probably want to create a master page that pulls all the data needed without line breaks so you only have to do it once. And based on what you describe in your question you'll be doing this for many lines so you will probably have to modify the process to make it work for you.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Member_2_6539617Author Commented:
Thanks Mike I will check this out! Surprised google didn't help me find this feature.. Thanks again!
0
Mike in ITIT System AdministratorCommented:
No problem, glad to help. If you have problems getting it to work for you make sure to post another question and we will help with more specifics.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ETL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.