Solved

read excel/csv sheet and write into xml?

Posted on 2008-10-20
11
843 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
  • 6
  • 5
11 Comments
 
LVL 62

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 62

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
 
LVL 62

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 62

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 62

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 62

Expert Comment

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

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now