Solved

read excel/csv sheet and write into xml?

Posted on 2008-10-20
11
835 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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.

746 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

13 Experts available now in Live!

Get 1:1 Help Now