Re: [RESOLVED] CSV to XML
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:
Code:
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).
Re: [RESOLVED] CSV to XML
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
Code:
MessageBox.Show(CDate(dt.Rows(0).Item(0)).ToString("yyyy-MM-dd HH:mm"))
And we'll go from there...
Re: [RESOLVED] CSV to XML
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!
Re: [RESOLVED] CSV to XML
Do you have anything else to add?
Thanks.
Re: [RESOLVED] CSV to XML
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:
Code:
'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...
Re: [RESOLVED] CSV to XML
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!
Re: [RESOLVED] CSV to XML
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...