Solved

read excel/csv sheet and write into xml?

Posted on 2008-10-20
11
851 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

Industry Leaders: 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!

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

738 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