Solved

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

Posted on 2009-07-13
14
406 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
ID: 24845885
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
ID: 24847452
Hi ipjyo,

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

Assisted Solution

by:RSBuTCHeR
RSBuTCHeR earned 50 total points
ID: 24848262
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:ipjyo
ID: 24850536
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
ID: 24850578
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
ID: 24851343
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
ID: 24851456
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
 

Author Comment

by:ipjyo
ID: 24851484
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
ID: 24851581
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
ID: 24851692
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
ID: 24851746
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
ID: 24851842
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
ID: 31603104
Idle Mind, your solution was very helpful.
Thanks
0
 
LVL 85

Expert Comment

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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to join on ID, with prefix? 15 58
Help with preventing downloading a zip file 10 37
VB.Net - MemoryMappedFiles - Confirm receipt 2 19
VB.NET 2008 - SQL Timeout 9 24
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
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 …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 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