[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Convert Excel to XML

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
abooysen2
Asked:
abooysen2
  • 7
  • 2
1 Solution
 
h3nnysCommented:
0
 
abooysen2Author Commented:
The requirement is I have to do it in SSIS
0
 
h3nnysCommented:
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
abooysen2Author Commented:
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
 
Sikhumbuzo NtsadaCommented:
If you have excel, open the file and save it as in xml format and upload it, everything should work.
0
 
abooysen2Author Commented:
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
 
abooysen2Author Commented:
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
 
abooysen2Author Commented:
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
 
abooysen2Author Commented:
I manage to get this to work. Took a long time but it works
0
 
abooysen2Author Commented:
I manage to get this to work. Took a long time but it works
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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