read excel/csv sheet and write into xml?

Posted on 2008-10-20
Medium Priority
Last Modified: 2013-11-26
i am trying to read an excel sheet (csv file) in visual studio 2008 and write it into an xml file. but i need to give each xml line its own custom tag. can someone help explain this in the simplest way possible. i am very limited in my knowledge of this area. thanks so much.
Question by:psuscott
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
LVL 63

Expert Comment

by:Fernando Soto
ID: 22761123
Hi psuscott;

What you posted was a Excel sheet in excel format - .xls, will the file be in csv text format?

Will the file have a header for each column in the first line of the csv file as is show in the Excel file?

What programming language Visual Basic .Net or C#?

What version of the .Net Framework 2005 or 2008?



Author Comment

ID: 22761145
i am using .net 2008 i think. i have visual studio 2008 and i picked to use a new console application. if this is not the correct approach please let me know. the sheet will be in csv format with headers. but the headers do not matter i would like to give each xml its own tag that the user specifies. thanks so much.
LVL 63

Expert Comment

by:Fernando Soto
ID: 22761264
Do you mean that each column of the csv will have a header defined by the user at runtime like each header in a different text box?

The language is VB correct?
Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

LVL 63

Expert Comment

by:Fernando Soto
ID: 22761276
Sorry you stated a Console application so all header defined in command line?

Author Comment

ID: 22761299
in the xls file i attached above is the file that is automatically created. it has headers in it but i dont need to use them. i want to read say cell B2 and write it into an xml marked <company> cell B2 </company> or however the syntax should be. thanks again. yes vb is the language.

Author Comment

ID: 22761367
im sorry i am not sure what you mean about defining it in command line. i am still very new with this. i would like to just write the values which are separated by commas into an xml tag for each value
LVL 63

Expert Comment

by:Fernando Soto
ID: 22761562
A Console application has no GUI interface all it has a DOS console window that the user types into. So for example if the user is going to tell the app what to call all the fields there are a couple of options:

  1. In the DOS window you type the name of the progrmam with all the fields names
    this is called the command line - For example:
        myapp.exe /field1 /field2 /field3 ... /fieldn
  2. When the program starts have the app ask the user to give all the field name
  3. Have the user create a text file with all the field name and have the program read the text file and read all the field names
LVL 63

Accepted Solution

Fernando Soto earned 2000 total points
ID: 22762861
Hi psuscott;

The code snippet below is a Console application that reads the csv file into memory parses the fields and builds the XML document and writes it out to a file. The csv file you posted has spaces in the header and cannot be used as is to create the XML document. The spaces need to be removed. I have attached the csv file I used to test the code. When the program is run it will produce the following file also attached. The file names need to be renamed as outlined in the file Description below.


Imports System.IO
Imports System.Xml.Linq
Imports System.Xml
Module Module1
    Sub Main()
        ' Create an XML document with an XML declaration and a root element
        Dim doc As New XDocument(New XDeclaration("1.0", "uth-8", "yes"), _
                         New XElement("Root"))
        ' Get a refernce to the Root node to add additional element nodes
        Dim root As XElement = doc.Root
        ' Create and open a text field parser, the parser opens the file
        ' which is in the same directory as the executable. In the IDE invironment
        ' this directory is in \bin\Debug below the source files of the program.
        Using csvParser As New FileIO.TextFieldParser("sheet2.csv")
            ' Set up the field to read the fields correctly
            csvParser.Delimiters = New String() {","}
            csvParser.HasFieldsEnclosedInQuotes = True
            csvParser.TextFieldType = FileIO.FieldType.Delimited
            csvParser.TrimWhiteSpace = True
            ' In the example file given the first line of code has the field headers
            ' I used that in this code snippet to name the tags of the elements. I read
            ' all the headers into an array of strings to fill in the tags of the elements
            ' The headers in the file have spaces in them. You need to remove the spaces
            ' because they are not allowed in tag names.
            Dim header() As String = csvParser.ReadFields()
            ' This string array does the same as the header array but for the data of the elements
            Dim fields() As String
            While Not csvParser.EndOfData()
                ' get all the fields and read it into the string array
                fields = csvParser.ReadFields()
                ' A nice thing about VB .Net 2008 is that it allows what is called functional
                ' construction as seen in the next couple of lines. Each tag of the elements
                ' fills in what is called a hole like this, <%= header(0) %>, the header(0) is
                ' the value of the variable header at element 0.
                Dim xmlFieldData As XElement = <CompanyInfo>
                                                   <<%= header(0) %>><%= fields(0) %></>
                                                   <<%= header(1) %>><%= fields(1) %></>
                                                   <<%= header(2) %>><%= fields(2) %></>
                                                   <<%= header(3) %>><%= fields(3) %></>
                                                   <<%= header(4) %>><%= fields(4) %></>
                                                   <<%= header(5) %>><%= fields(5) %></>
                                                   <<%= header(6) %>><%= fields(6) %></>
                                                   <<%= header(7) %>><%= fields(7) %></>
                                                   <<%= header(8) %>><%= fields(8) %></>
                                                   <<%= header(9) %>><%= fields(9) %></>
                                                   <<%= header(10) %>><%= fields(10) %></>
                                                   <<%= header(11) %>><%= fields(11) %></>
                                                   <<%= header(12) %>><%= fields(12) %></>
                                                   <<%= header(13) %>><%= fields(13) %></>
                                                   <<%= header(14) %>><%= fields(14) %></>
                                                   <<%= header(15) %>><%= fields(15) %></>
                                                   <<%= header(16) %>><%= fields(16) %></>
                                                   <<%= header(17) %>><%= fields(17) %></>
                                                   <<%= header(18) %>><%= fields(18) %></>
                                                   <<%= header(19) %>><%= fields(19) %></>
                                                   <<%= header(20) %>><%= fields(20) %></>
                                                   <<%= header(21) %>><%= fields(21) %></>
                ' Add the above elements to the Root node. 
                ' Go back to the top and get the next row of data and do the same.
            End While
        End Using
        ' Write the XML data to a file, this is writing the info to the file called
        ' sheet2.xml in UFT-8 format.
        Dim xwriter As New XmlTextWriter("sheet2.xml", System.Text.Encoding.UTF8)
        ' The following three lines of code do a format of the XML to look nice
        ' otherwise the data is in one long line.
        xwriter.Formatting = Formatting.Indented
        xwriter.Indentation = 4
        xwriter.IndentChar = " "c
        ' Write the document to the file
        ' Close the document
    End Sub
End Module

Open in new window


Author Comment

ID: 22767148
this is perfect!!! thank you so much for the time you spent writing this out for me.

Author Closing Comment

ID: 31507945
this was a very well explained answer. you are a huge help!
LVL 63

Expert Comment

by:Fernando Soto
ID: 22767240
Not a problem, glad I was able to help.  ;=)


Featured Post

Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 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