By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,742 Members | 1,224 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,742 IT Pros & Developers. It's quick & easy.

Parseing data in CSV files

P: n/a
How would you parse this type of file into an array?

"Test","Help, 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)
Nov 21 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
Nov 21 '05 #2

P: n/a
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" <no**********@planet.nl> wrote in message
news:uR*************@TK2MSFTNGP12.phx.gbl...
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

Nov 21 '05 #3

P: n/a
"Brian Henry" <br**********@newsgroups.nospam> wrote in message
news:Ol**************@TK2MSFTNGP09.phx.gbl...
How would you parse this type of file into an array?

"Test","Help, 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)

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.
Nov 21 '05 #4

P: n/a
Brian,

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

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
DataGrid1.DataSource = ds.Tables(0)
End Sub

I hope this helps a little bit?

Cor
Nov 21 '05 #5

P: n/a
that might be what I need, thanks, I'll try it out

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:u3**************@tk2msftngp13.phx.gbl...
Brian,

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

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
DataGrid1.DataSource = ds.Tables(0)
End Sub

I hope this helps a little bit?

Cor

Nov 21 '05 #6

P: n/a
it worked, thanks!

"Brian Henry" <br**********@newsgroups.nospam> wrote in message
news:es**************@TK2MSFTNGP09.phx.gbl...
that might be what I need, thanks, I'll try it out

"Cor Ligthert" <no**********@planet.nl> wrote in message
news:u3**************@tk2msftngp13.phx.gbl...
Brian,

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

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
DataGrid1.DataSource = ds.Tables(0)
End Sub

I hope this helps a little bit?

Cor


Nov 21 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.