[RESOLVED] CSV to XML

[MMock]

I would just like some advice on the best way to take a CSV file (text file separated by commas) and convert it to XML. I didn't think it was supposed to be too difficult, given the timeframe my management has given me to get it done, but when I google "CSV to XML" I get all these vendor products for purchase which not only do I not think is an option but I am wondering what's the big deal about writing one's own?

Thank you, any comments and advice are appreciated.

[stanav]

A simple way to do this is to read your csv file to a datatable, and then call datatable.WriteXml to write it to an XML file. You can search this forum for code to read csv to datatable.

[MMock]

stanav -

Thank you SO VERY MUCH for answering my post! I grabbed the code from the other thread, and it is working magically!

You have helped me out a lot and I greatly appreciate it!

Thanks!
Marlene

[MMock]

Oh, I just have one question. In my original file my first comma-delimited field is "2008-02-09 20:00" which is a date and time. When I see what's in my datatable, it's this:

? dt.Rows(0).Item(0).ToString
"2/9/2008 8:00:00 PM"

I actually don't want it to convert military time to 12-hour time with a meridiem, why did that happen automatically? This is the code I borrowed from the original thread for easy reference:

            sourcePath = System.IO.Path.GetDirectoryName(filePath)
            csvFile = System.IO.Path.GetFileName(filePath)
            conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sourcePath & ";Extended Properties=""text;HDR=No;FMT=Delimited"";"
            conn = New OleDb.OleDbConnection(conString)
            selString = "Select * From " & csvFile
            adapter = New OleDb.OleDbDataAdapter(selString, conn)
            dt = New DataTable(System.IO.Path.GetFileNameWithoutExtension(filePath))
            conn.Open()
            adapter.Fill(dt)

Thanks...hope you're still out there! (Or anyone else).

[stanav]

What is the data type of that column in your datatable? From the value you show in your csv file and the value you have when you do ToString of the datatable item, it does look like the column is of DateTime type. Try this and see if you get the same value as in the csv file

MessageBox.Show(CDate(dt.Rows(0).Item(0)).ToString("yyyy-MM-dd HH:mm"))

And we'll go from there...

[MMock]

Thank you for chiming back in to this "resolved" thread!

I did what you asked and got this:
? CDate(dt.Rows(0).Item(0)).ToString("yyyy-MM-dd HH:mm")
"2008-02-09 20:00"
(By the way, it's a web app so I couldn't do a message box, but I'm sure that's of no importance).

I didn't define my datatable columns to be of any type, that's what I thought was so easy about it, all I did was:
dt = New DataTable(System.IO.Path.GetFileNameWithoutExtension(filePath))
It seemed to default to whatever it wanted.

Well, I appreciate your help and I'll be back online tomorrow...the weather in CT stinks right now, I have a terrible chest cold, and it's time to get home and pay the babysitter for a full day (because of cancelled school due to the weather!)

Thanks.

Thanks!

[MMock]

Do you have anything else to add?

Thanks.

[stanav]

That column does seem to be of type Date... And at the moment, I don't have a better solution but going an extra step: after importing your csv file to a datatable as you already did, create another datatable with the same number of columns, and specifying the type to be String... Now populate this new table with the data from the other table. When done, you write this new table to an Xml file. Somethng like this should work:

        'Assuming dt is the datatable imported from csv file
        'Now we create a new datatable
        Dim newDT As New DataTable(dt.TableName)
        'Adding columns to this new datatable
        For i As Integer = 0 To dt.Columns.Count - 1
            newDT.Columns.Add(dt.Columns(i).ColumnName, GetType(String))
        Next
        'Now import the data to newDT and specify the format for the date column which
        'is column(0) in dt
        Dim row As DataRow = Nothing
        For i As Integer = 0 To dt.Rows.Count - 1
            row = newDT.NewRow()
            'Manually format the date string
            row.Item(0) = CDate(dt.Rows(i).Item(0)).ToString("yyyy-MM-dd HH:mm")
            For j As Integer = 1 To dt.Columns.Count - 1
                row.Item(j) = CStr(dt.Rows(j).Item(i))
            Next
            newDT.Rows.Add(row)
        Next
        'When all done, writee it to an Xml file
        newDT.WriteXml("put the xml output file path here")

Hopefully someone can comes up with a better way to do it that can eliminate this extra step...

[MMock]

No, that's fine. I can convert it back to what I wanted to be, I just didn't understand why it automatically turned it into a date and reformatted it against my will. But it's no problem putting it back to the way I want it.

As for the XML output, I'm not doing that anymore. I can get data in one of two ways, either an XML file or a CSV file. If the latter, I was going to convert it into XML so I could use the same parser code I wrote to get my fields and insert them to the db, but it's much easier just working directly with the datatable columns.

So thanks for all your help.

Marlene

p.s. Guess I can mark this thread [REALLY RESOLVED] now!

[MMock]

I just noticed something else interesting. I have my csv file open in Excel. The cell that has the date and time reads 2/9/2008 20:00 in the cell. But when I highlight the cell, what displays in the function area (if that's what it's called - I'm not a seasoned Excel user) is 2/9/2008 8:00:00 PM. Interesting...it did some sort of conversion for me, which must be the same thing the datatable does.

Mystery sort of solved...