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
abooysen2Asked:
Who is Participating?
 
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
 
h3nnysCommented:
0
 
abooysen2Author Commented:
The requirement is I have to do it in SSIS
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
 
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 NtsadaSenior IT TechnicianCommented:
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:
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
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.

All Courses

From novice to tech pro — start learning today.