Solved

How to INSERT, SORT, FILTER a CSV file in VB.NET

Posted on 2015-02-19
1
124 Views
Last Modified: 2015-08-24
I am trying to put to get a small program to randomly select a record from a CSV file.  The user would select the file and the program would filter the file on one column, insert a column of random numbers and then sort the file and select a number of record to be wirtten to an output file.

To select the file, I have the following:

 Private Sub btnBrowse1_Click(sender As Object, e As EventArgs) Handles btnBrowse1.Click
        Dim randomValue As Random = New Random
        Dim openFileDialog1 As New OpenFileDialog()


        openFileDialog1.Filter = "CSV Files|*.csv"
        openFileDialog1.Title = "Select a CSV File"

        Dim list As New List(Of String)

        If openFileDialog1.ShowDialog() = DialogResult.OK Then
            myFileName = openFileDialog1.FileName
            outputFile = myFileName.Replace(".csv", "_" & System.DateTime.Now.ToString("yyyyMMdd") & ".csv")
            txtCsvFile.Text = myFileName
        End If
    End Sub

To process the file I have this:

    Private Sub btnProcess_Click(sender As Object, e As EventArgs) Handles btnProcess.Click
        Dim sReader As StreamReader = New StreamReader(myFileName)
        Dim sWriter As StreamWriter = New StreamWriter(outputFile)
        Dim line As String
        Dim rv As Integer
        Dim recAmount As Integer
        Dim counter As Integer = 0

        ' Store contents in this String.
        ' Read first line.
        line = sReader.ReadLine
        recAmount = CInt(txtRecAmount.Text)

        MsgBox(randomValue.Next(1, 1000))

        ' Loop over each line in file, While list is Not Nothing.
        Do While sReader.Peek <> -1
            counter += 1
            line = sReader.ReadLine
            rv = randomValue.Next(1, 1000)

            Do While counter < recAmount + 1
                sWriter.WriteLine(rv & "," & line)
                Exit Do
                ' Read in the next line.
            Loop
        Loop
        sWriter.Close()
    End Sub

It doesn't filter or sort.  Do I need to read it into a two dimensional array? If so, how do I filter and sort the 2 dimensional array?
0
Comment
Question by:abuhaneef
1 Comment
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 40621199
You could use the TextFieldParser class to parse the CSV into separate elements, that you could sort on.

Example:

TextFieldParser Class
https://msdn.microsoft.com/en-us/library/microsoft.visualbasic.fileio.textfieldparser(v=vs.110).aspx

Using MyReader As New Microsoft.VisualBasic.FileIO.
    TextFieldParser("c:\logs\bigfile")

    MyReader.TextFieldType = 
        Microsoft.VisualBasic.FileIO.FieldType.Delimited
    MyReader.Delimiters = New String() {vbTab}
    Dim currentRow As String()
    'Loop through all of the fields in the file.  
    'If any lines are corrupt, report an error and continue parsing.  
    While Not MyReader.EndOfData
        Try
            currentRow = MyReader.ReadFields()
            ' Include code here to handle the row. 
        Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
            MsgBox("Line " & ex.Message & 
            " is invalid.  Skipping")
        End Try 
    End While 
End Using

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

828 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