Solved

Convert Excel to XML

Posted on 2011-02-22
10
524 Views
Last Modified: 2012-05-11
Hi

It will be my first time but I need to upload a Excel sheet and convert it to a XML is SSIS. Is there some references
0
Comment
Question by:abooysen2
  • 7
  • 2
10 Comments
 
LVL 4

Expert Comment

by:h3nnys
ID: 34950761
0
 

Author Comment

by:abooysen2
ID: 34950799
The requirement is I have to do it in SSIS
0
 
LVL 4

Expert Comment

by:h3nnys
ID: 34950882
If you have a well formatted xls document then there is no reason to convert it to xml all you need to do is import the xls document into ssis.
0
 

Author Comment

by:abooysen2
ID: 34951027
Well users will drop it on a share and I SSIS package will need to pick up the Excel sheet that the users dropped.
0
 
LVL 17

Expert Comment

by:Sikhumbuzo Ntsada
ID: 34951158
If you have excel, open the file and save it as in xml format and upload it, everything should work.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:abooysen2
ID: 34951465
Can I not upload the Excel and then convert it to XML. Because I already propose that and they want to save it to a excel file
0
 

Author Comment

by:abooysen2
ID: 34951755
I tried this VB code but it does not work

         Dim dt As New DataTable()
        Dim ds As New DataSet
        dt.Columns.Add("Currency", String.Empty.GetType())
        dt.Columns.Add("Legal Entity", String.Empty.GetType())
        dt.Columns.Add("Account id", String.Empty.GetType())
        dt.Columns.Add("Movement", String.Empty.GetType())
        dt.Columns.Add("Value date", String.Empty.GetType())
        dt.Columns.Add("Narration", String.Empty.GetType())
        dt.Columns.Add("Counterparty", String.Empty.GetType())
        ds.Tables.Add(dt)
        Dim FILE_NAME As String = "C:\Users\anthonio.booysen\xmltest.xml"
        Dim objWriter As New System.IO.StreamWriter(FILE_NAME)
        objWriter.Write(ds.GetXml)
        objWriter.Close()
0
 

Accepted Solution

by:
abooysen2 earned 0 total points
ID: 34958790
Hi now can extract my data in a xml but i need for for loop can you perhaps assist
writer.WriteStartDocument()

        ' Add elements to the file.
        writer.WriteStartElement("transferupload")
        writer.WriteStartElement("Currency", "")
        writer.WriteString(Row.Currency.ToString)
        writer.WriteEndElement()
        writer.WriteStartElement("Legal Entity", "")
        writer.WriteString(Row.LegalEntity.ToString)
        writer.WriteEndElement()
        writer.WriteStartElement("Accountid", "")
        writer.WriteString(Row.Accountid.ToString)
        writer.WriteEndElement()
        writer.WriteStartElement("Movement", "")
        writer.WriteString(Row.Movement.ToString)
        writer.WriteEndElement()
        writer.WriteStartElement("Value Date", "")
        writer.WriteString(Row.Valuedate.ToString)
        writer.WriteEndElement()
        writer.WriteStartElement("Narration", "")
        writer.WriteString(Row.Narration.ToString)
        writer.WriteEndElement()
        writer.WriteStartElement("Counterparty", "")
        writer.WriteString(Row.Counterparty.ToString)
        writer.WriteEndElement()



        ' Ends the document 
        writer.WriteEndDocument()
        writer.Close()

Open in new window

0
 

Author Comment

by:abooysen2
ID: 35067638
I manage to get this to work. Took a long time but it works
0
 

Author Closing Comment

by:abooysen2
ID: 35120542
I manage to get this to work. Took a long time but it works
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now