Solved

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

Posted on 2009-07-13
14
402 Views
Last Modified: 2013-11-07
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!
0
Comment
Question by:ipjyo
14 Comments
 
LVL 85

Expert Comment

by:Mike Tomlinson
Comment Utility
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
 
LVL 4

Expert Comment

by:barrislb
Comment Utility
Hi ipjyo,

Could you provide us with a sample file, please?  :- )
0
 
LVL 1

Assisted Solution

by:RSBuTCHeR
RSBuTCHeR earned 50 total points
Comment Utility
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
 

Author Comment

by:ipjyo
Comment Utility
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
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
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
 
LVL 85

Accepted Solution

by:
Mike Tomlinson earned 450 total points
Comment Utility
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
 

Author Comment

by:ipjyo
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:ipjyo
Comment Utility
Thanks Idle Mind..I will go through your code and try. I will let you know.
Thanks
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
Comment Utility
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
 

Author Comment

by:ipjyo
Comment Utility
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
 
LVL 85

Expert Comment

by:Mike Tomlinson
Comment Utility
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
 

Author Comment

by:ipjyo
Comment Utility
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
 

Author Closing Comment

by:ipjyo
Comment Utility
Idle Mind, your solution was very helpful.
Thanks
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
Comment Utility
Did you get something working?...I hadn't gotten back to your question yet...been busy with the kids all day.  =\
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
reports, calling from code. 3 29
.net Multiple Row Form Fields Data Entry 4 16
Achieve json result 2 24
XML & .net 5 14
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now