Manipulating a CSV file using VB.net

I'm new to VB.net programming and I'm trying to create a vb.net  console app that reads a csv file, then filter it to only display certain rows, then change values in specific rows if it meets certain criterias, then write out the new modified information in the csv file.

Below is a sample of the csv file I'm working with. The first row are the column headings

Route,Direction,Milepost,Date_Rated,Surface_Type,Bridge,Construction,MRI,MRUT,TRANS23,WHEEL23,NONWHEEL23
AL0001,5,79.589,4/29/2009,F,0,0,108,0.105,0,0,0
AL0001,5,79.599,4/29/2009,F,0,0,109.5,0.075,0,0,0
AL0001,5,79.609,4/29/2009,F,0,0,46,0.07,0,0,0
AL0001,5,79.619,4/29/2009,F,0,0,56,0.085,0,0,0

"This is the code that I have so far, and it just simply reads the file, but I need it to also do the above tasks mentioned above"

Imports System.IO

Module Module1
    Public Sub Main()
        readCSV()
    End Sub
    Public Function readCSV() As DataTable
        Dim dt As New DataTable()
        dt.Columns.Add("Col1")
        dt.Columns.Add("Col2")
        dt.Columns.Add("Col3")
        dt.Columns.Add("Col4")
        dt.Columns.Add("Col5")
        dt.Columns.Add("Col6")
        dt.Columns.Add("Col7")
        dt.Columns.Add("Col8")
        dt.Columns.Add("Col9")
        dt.Columns.Add("Col10")
        dt.Columns.Add("Col11")
        dt.Columns.Add("Col12")

        Dim sr As New StreamReader("C:\Documents and Settings\meansr\My Documents\Visual Studio 2010\Projects\ReadCSV\Third_and_Sixth_District.csv")

        Dim input As String

        ' Read the first line
        input = sr.ReadLine

        While input IsNot Nothing
            Dim s As String() = Input.Split(New Char() {","c})
            dt.Rows.Add(s)
            input = sr.ReadLine()
        End While

        sr.Close()

        Return dt
    End Function

End Module


Thanks for your help!
LVL 1
rmitwizAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZhaolaiCommented:
This is to just get you started:

    Private Sub ProcessFile()
        'read the source file into a string array
        Dim arrLines() As String = File.ReadAllLines("C:\Documents and Settings\meansr\My Documents\Visual Studio 2010\Projects\ReadCSV\Third_and_Sixth_District.csv")
        Dim arrFields() As String
        'new string array to store the info for the output file
        Dim arrNewLines() As String = New String() {}
        'loop through the source file line-by-line
        For Each line As String In arrLines
            arrFields = line.Split(",")
            'arrFileds(0) is Route
            'arrFields(1) is Direction, etc
            'process the fields here
            '....

            'then put fields (array) back to a string (line)
            line = Join(arrFields, ",")
            'add the line to an array
            Array.Resize(arrNewLines, arrNewLines.Length)
            arrNewLines(arrNewLines.Length - 1) = line
        Next

        'save the new array to a new file
        File.WriteAllLines("C:\Documents and Settings\meansr\My Documents\Visual Studio 2010\Projects\ReadCSV\Third_and_Sixth_District_New.csv", arrNewLines)
    End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike TomlinsonHigh School Computer Science, Computer Applications, Digital Design, and Mathematics TeacherCommented:
If you use the approach proposed by Zhaolai I would switch to List(Of String) instead of an Array if you have .Net 2. 0 or above...
Éric MoreauSenior .Net ConsultantCommented:
The TextFieldParser is also good at reading CSV files. check http://www.emoreau.com/Entries/Articles/2010/05/Do-you-know-the-TextFieldParser.aspx
ZhaolaiCommented:
Thank you, Idle_Mind, for the suggestion. Here is the code using List(Of String):
(
Note: rmitwiz, if you accept this solution, please reward partial points to Idle_Mind. Thanks. Good luck.)

    Private Sub ProcessFile()
        'read the source file into a string array
        Dim arrLines() As String = File.ReadAllLines("C:\Documents and Settings\meansr\My Documents\Visual Studio 2010\Projects\ReadCSV\Third_and_Sixth_District.csv")
        Dim arrFields() As String
        'new string array to store the info for the output file
        Dim lstLines As New List(Of String)
        'loop through the source file line-by-line
        For Each line As String In arrLines
            arrFields = line.Split(",")
            'arrFileds(0) is Route
            'arrFields(1) is Direction, etc
            'process the fields here
            '....

            'then put fields (array) back to a string (line)
            line = Join(arrFields, ",")
            'add the line to a list
            lstLines.Add(line)
        Next

        'save the new array to a new file
        File.WriteAllLines("C:\Documents and Settings\meansr\My Documents\Visual Studio 2010\Projects\ReadCSV\Third_and_Sixth_District_New.csv", lstLines.ToArray)
    End Sub

Open in new window

rmitwizAuthor Commented:
The answers wasn't a simple copy and please solution, I had to make several revisions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.