string csv data to xml

[Edward W.]

after some processing and such I end up with csv data in a string variable.
For example

"023123","abcabc"
"123345","03a-m3"
"239349","amf309"
....

I need to somehow convert this to xml like follows

<root>
<customer><custnum>023123</custnum><custname>abcabc</custname></customer>
<customer><custnum>123345</custnum><custname>03a-m3</custname></customer>
<customer><custnum>239349</custnum><custname>amf309</custname></customer>
</root>

Eventually I need to pass this XML into a stored procedure parameter as a
text datatype.

I need guidiance on how to go from the CSV with commas and quotes to the XML
format. After each row in the string variable is a carriage return. Also,
is it possible to compare the XML to a XSD to see if it meets some standard?
I want to catch if the CSV has errors like this before I send it to sql
server

"023123","" <-- error, the second column is blank
"123345a","03a-m3" <-- error, the first column is 7 characters (6 is the
max) and is not numeric
"239349" <-- error, the second column is totally missing.


thanks!

 

[Cor Ligthert]

Edward,

You can get a csv direcly in a dataset using the OleDbDataAdapter.

\\\
Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Dim file As String = "Test2.txt"
Dim path As String = "C:\Test1\"
Dim ds As New DataSet
Try
Dim f As System.IO.File
If f.Exists(path & file) Then
Dim ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
path & ";Extended Properties=""Text;HDR=No;FMT=Delimited\"""
Dim conn As New OleDb.OleDbConnection(ConStr)
Dim da As New OleDb.OleDbDataAdapter("Select * from " & _
file, conn)
da.Fill(ds, "TextFile")
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
ds.writeXML("C:\test1\test.xml)
End Sub
///
I hope this helps a little bit?

Cor

 

[Mohamoss]

Hi Edward
Just small addition to what Cor provided, you can then update your
datasource ( your database ) once you have the file in a dataset you can
use a dataAdapter object to update your datasource use the update method of
the adapter against your datasource passing to it your dataset as a
parameter.
Mohamed M .Mahfouz
Developer Support Engineer
ITWorx on behalf of Microsoft EMEA GTSC