Solved

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

Posted on 2015-02-19
1
151 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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 call new form in VB2013? 4 37
Datatable / Dates ? 4 33
VB .net 2010 Byte array 2 24
Adding / drawing an image file to a VB.NET rectangle in ASP.NET 3 13
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 …
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

752 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