Solved

read excel/csv sheet and write into xml?

Posted on 2008-10-20
11
855 Views
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.
sheet1.xls
0
Comment
Question by:psuscott
[X]
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
11 Comments
 
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?

Fernando

0
 

Author Comment

by:psuscott
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.
0
 
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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 63

Expert Comment

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

Author Comment

by:psuscott
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.
0
 

Author Comment

by:psuscott
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
0
 
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
Fernando
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 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.

Fernando

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) %></>
                                               </CompanyInfo>
                ' Add the above elements to the Root node. 
                root.Add(xmlFieldData)
                ' 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
        doc.WriteTo(xwriter)
        ' Close the document
        xwriter.Close()
 
    End Sub
 
End Module

Open in new window

sheet2csv.txt
sheet2XML.txt
0
 

Author Comment

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

Author Closing Comment

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

Expert Comment

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

0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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…

688 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