?
Solved

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

Posted on 2009-07-13
14
Medium Priority
?
426 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 86

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 200 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

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 86

Accepted Solution

by:
Mike Tomlinson earned 1800 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 86

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 86

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 86

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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Watch the video to learn how one can deal with PST file corruption issue with an outstanding Kernel for Outlook PST Repair Tool easily. Using this tool, non-technical users can swiftly perform the repair process to restore their essential data witho…
Suggested Courses
Course of the Month5 days, 16 hours left to enroll

588 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