how do we parse a comma delimited file using vb.net?

Hi,
Could you help me how to parse a comma delimited file using vb.net?
The file consists of name and value pairs in the below format.
"Name1","Name2","Name3",
"Name4","Name5","Name6"....
"Value1","value2","Value3",
"Value4","",""...
So I need to parse this file and put all the Names in one array and all the Values in
another array. Some of those values can be blank so I have those blank ones as "".
And before putting them in arrays, I may need to strip the quotes.
Any help is appreciated. Thank you!
ipjyoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Mike TomlinsonConnect With a Mentor Middle School Assistant TeacherCommented:
Here's a quick stab at it...

My Output:

    FirstName --> Stacy
    LastName --> Chan
    LoanType -->
    Address --> Dean Street
    zip --> 76428

Produced with:
Public Class Form1
 
    Private Data As New Dictionary(Of String, String)
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim DataFile As String = My.Computer.FileSystem.CombinePath(My.Computer.FileSystem.SpecialDirectories.MyDocuments, "Data.txt")
        Dim RawData As New List(Of String)
        For Each Line As String In My.Computer.FileSystem.ReadAllText(DataFile).Split(vbCrLf.ToCharArray, StringSplitOptions.RemoveEmptyEntries)
            RawData.AddRange(Line.Split(",".ToCharArray, StringSplitOptions.RemoveEmptyEntries))
        Next
        If RawData.Count Mod 2 = 0 Then
            Dim middle As Integer = RawData.Count / 2
            For i As Integer = 0 To middle - 1
                Data.Add(RawData(i).TrimStart(Chr(34)).TrimEnd(Chr(34)), RawData(middle + i).TrimStart(Chr(34)).TrimEnd(Chr(34)))
            Next
 
            For Each kvp As KeyValuePair(Of String, String) In Data
                Debug.Print(kvp.Key & " --> " & kvp.Value)
            Next
        Else
            MessageBox.Show("Missing Data in File", "Odd Number of Entries", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
    End Sub
 
End Class

Open in new window

0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
How do we now where the Names end and the Values begin?  Will there always be an even number of lines in the file and we can just divide by two?  Is it always the same number of items on each line?

Also, one IMPORTANT question:  Will there ever be a COMMA as part of the data (name/value) itself INSIDE the quotes?
0
 
barrislbCommented:
Hi ipjyo,

Could you provide us with a sample file, please?  :- )
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
RSBuTCHeRConnect With a Mentor Commented:
Hi Barrislb

Check out this article:
http://www.devarticles.com/c/a/ASP.NET/Reading-a-Delimited-File-Using-ASP.Net-and-VB.Net/

If you have any further question let us know.

Falcko
0
 
ipjyoAuthor Commented:
hi,
sorry for the late reply..my Internet was not working since morning.
There are always even number of lines in the file.
but number of values may vary depending on the number of names that we have correspondingly.
There is no COMMA as part of the data (name/value) itself INSIDE the quotes.
here is a small sample file as shown in the attached.
I am thinking to divide it by 2  and then load into 2 arrays and then into dictionary.
Any sample code or anything that will get me started is very helpful.
Please let me know if I have to provide any additional information.
Thank you.



"FirstName","LastName"
"LoanType","Address"
"zip"
"Stacy","Chan"
"","Dean Street"
"76428"
 
In the 1st and 2nd lines I have 2 names per line and in the third line I have only one name.
So correspondingly in the 4th and 5th lines I have 2 values per line and one value in the 6th line.

Open in new window

0
 
CodeCruiserCommented:
I dont think there is any way of parsing this unless either the number of lines for names or the number of names is fixed. If you have one record like

"FirstName","LastName"
"LoanType","Address"
"zip"
"Stacy","Chan"
"","Dean Street"
"76428"
 
and another one like
"FirstName","LastName"
"LoanType","Address1"
"Address2", "Address3"
"zip"
"Stacy","Chan"
"","Dean Street"
"", ""
"76428"
 

Then there is no way of differentiating the names and values.
0
 
ipjyoAuthor Commented:
hi,
actually there is only one record at a time that comes from the file.
I am sorry, I think I provided some wrong format of the file. Here is the correct format of the file.
We dont have a fixed number of lines.
But we have a fixed number of names and fixed number of corresponding values.
once the names are ended, the values starts from the next line without a COMMA(as shown below, there is no COMMA between "cellphone" and "Stacy")
Any ideas please? Thanks for the help!
Sorry for the confusion I made. Please let me know if I have to provide additional information.
"FirstName","LastName","address1","address2","mailzip","ma
ilcity","mailstate","EntityDate"
,"HosiId","description","homefax","cellphone"
"Stacy","Chan","crestpines","","","","","","1234",""
,"",""

Open in new window

0
 
ipjyoAuthor Commented:
Thanks Idle Mind..I will go through your code and try. I will let you know.
Thanks
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Are the lines really broken up like that?...from your description I would expect it to be:
"FirstName","LastName","address1","address2","mailzip","mailcity","mailstate","EntityDate","HosiId","description","homefax","cellphone"
"Stacy","Chan","crestpines","","","","","","1234","","",""

Open in new window

0
 
ipjyoAuthor Commented:
The lines are broken up indeterministically. This is actually a huge file consisting of around 1000 names and 1000 values.But there is always a corresponding value for name. I think I should try "dividing by 2 " logic. I will try the code you provided. Please let me know if you have any more suggestions.
I will let you know.
Thank you
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
The code I gave you won't work with the actual values themselves being split across lines...   =\

...let me stare at it for a couple of mins and I'll see if I can post something else up.
0
 
ipjyoAuthor Commented:
oh..ok. Thanks
Here is the actual file attached if you would like to take a look.
Actually this is a .dat file, I converted it into .txt to attach here.
Thank you
file.txt
0
 
ipjyoAuthor Commented:
Idle Mind, your solution was very helpful.
Thanks
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Did you get something working?...I hadn't gotten back to your question yet...been busy with the kids all day.  =\
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.