Parseing data in CSV files

[Brian Henry]

How would you parse this type of file into an array?

"Test","Hel p, data","hello there, this text has commas","commas seperate
data, and in quotes they dont"

where the double quotes are the string markers, which can contain comma's,
but when you are not in a quote block the commas seperate the data... you're
basic Comma seperated value file... thanks

(i know split doesnt work for this... thats why im asking)

[Cor Ligthert]

Re: Parseing data in CSV files

Brian,

Why does the split on "," (all three characters I write) not work,

When you than eliminiate the first position from the first arrayItem and the
last postition from the last Array item with substring, you have what you
want in my idea?

Cor

[Brian Henry]

Re: Parseing data in CSV files

the reason it wont work is because not every field is in quotes... so data
can look like this

"hello",,,,,,,, "field again","another , field here","bad huh?",,,

and at each comma that isnt in quotes is a new field... kind of makes
splitting alone really hard... i was going to split on the string quote
comma quote "","" like that, but because some fields have no quotes its a
lot harder then a simple split and removeing the starting and ending quotes



"Cor Ligthert" <notfirstname@p lanet.nl> wrote in message
news:uRQCkefhEH A.396@TK2MSFTNG P12.phx.gbl...[color=blue]
> Brian,
>
> Why does the split on "," (all three characters I write) not work,
>
> When you than eliminiate the first position from the first arrayItem and
> the
> last postition from the last Array item with substring, you have what you
> want in my idea?
>
> Cor
>
>[/color]

[Guest]

Re: Parseing data in CSV files

"Brian Henry" <brianiupmsdn@n ewsgroups.nospa m> wrote in message
news:OlEF5TfhEH A.1276@TK2MSFTN GP09.phx.gbl...[color=blue]
> How would you parse this type of file into an array?
>
> "Test","Hel p, data","hello there, this text has commas","commas seperate
> data, and in quotes they dont"
>
> where the double quotes are the string markers, which can contain comma's,
> but when you are not in a quote block the commas seperate the data...[/color]
you're[color=blue]
> basic Comma seperated value file... thanks
>
> (i know split doesnt work for this... thats why im asking)
>
>[/color]
I think I solved this some time ago but I cant remember how & dont now what
I did with the source code. I seem to remember changing single to double
quotes but the reason why escapes me now.
It occurs to me now however that splitting on commas then looping through
the bits extracted joining together any which start with a " or even a '
perhaps until you have a trailing " or ' to match. Then you just have the
problem of testing for 'escaped' or doubled quotes such as 123,"text
"",here""", 246

--
Jonathan Bailey.

[Cor Ligthert]

Re: Parseing data in CSV files

Brian,

It seems as an original CSV so you can try that with OleDb

Private Sub Form1_Load(ByVa l sender As Object, _
ByVal e As System.EventArg s) 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=Micro soft.Jet.OLEDB. 4.0;Data Source=" & _
path & ";Extended Properties=""Te xt;HDR=No;FMT=D elimited\"""
Dim conn As New OleDb.OleDbConn ection(ConStr)
Dim da As New OleDb.OleDbData Adapter("Select * from " & _
file, conn)
da.Fill(ds, "TextFile")
End If
Catch ex As Exception
MessageBox.Show (ex.ToString)
End Try
DataGrid1.DataS ource = ds.Tables(0)
End Sub

I hope this helps a little bit?

Cor

[Brian Henry]

Re: Parseing data in CSV files

that might be what I need, thanks, I'll try it out

"Cor Ligthert" <notfirstname@p lanet.nl> wrote in message
news:u3KTmbghEH A.1964@tk2msftn gp13.phx.gbl...[color=blue]
> Brian,
>
> It seems as an original CSV so you can try that with OleDb
>
> Private Sub Form1_Load(ByVa l sender As Object, _
> ByVal e As System.EventArg s) 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=Micro soft.Jet.OLEDB. 4.0;Data Source=" & _
> path & ";Extended Properties=""Te xt;HDR=No;FMT=D elimited\"""
> Dim conn As New OleDb.OleDbConn ection(ConStr)
> Dim da As New OleDb.OleDbData Adapter("Select * from " & _
> file, conn)
> da.Fill(ds, "TextFile")
> End If
> Catch ex As Exception
> MessageBox.Show (ex.ToString)
> End Try
> DataGrid1.DataS ource = ds.Tables(0)
> End Sub
>
> I hope this helps a little bit?
>
> Cor
>
>[/color]

[Brian Henry]

Re: Parseing data in CSV files

it worked, thanks!

"Brian Henry" <brianiupmsdn@n ewsgroups.nospa m> wrote in message
news:esX0j7ghEH A.1276@TK2MSFTN GP09.phx.gbl...[color=blue]
> that might be what I need, thanks, I'll try it out
>
> "Cor Ligthert" <notfirstname@p lanet.nl> wrote in message
> news:u3KTmbghEH A.1964@tk2msftn gp13.phx.gbl...[color=green]
>> Brian,
>>
>> It seems as an original CSV so you can try that with OleDb
>>
>> Private Sub Form1_Load(ByVa l sender As Object, _
>> ByVal e As System.EventArg s) 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=Micro soft.Jet.OLEDB. 4.0;Data Source=" & _
>> path & ";Extended Properties=""Te xt;HDR=No;FMT=D elimited\"""
>> Dim conn As New OleDb.OleDbConn ection(ConStr)
>> Dim da As New OleDb.OleDbData Adapter("Select * from " & _
>> file, conn)
>> da.Fill(ds, "TextFile")
>> End If
>> Catch ex As Exception
>> MessageBox.Show (ex.ToString)
>> End Try
>> DataGrid1.DataS ource = ds.Tables(0)
>> End Sub
>>
>> I hope this helps a little bit?
>>
>> Cor
>>
>>[/color]
>
>[/color]