troubleshooting Question

VB.Net - Looking for feedback on CSV files into DataTable foundation of my project

Avatar of JMO9966
JMO9966 asked on
Visual Basic.NET
15 Comments1 Solution574 ViewsLast Modified:
I have this cool class module that an expert on here had provided me and it's been very useful for reading a .csv file into a VB.Net DataTable.

The current project I'm working on involves a third party program that cannot easily write their transactions to a single csv file, but instead each transaction creates it's own unique csv file.

I'm looking for tips on how to tweak this code to read and rename each individual csv file and then append each one of these rows to one DataTable in VB.Net for processing.  I think this would be most efficient.  After all csv files from the specified folder have been read and renamed, I will then run my code against all rows in my DataTable.

Main question revolves around how I would approach gathering all the csv files currently in this folder and then after I'm done reading the one row contained in each of the csv files, I would rename the file to .old or whatever to make sure it's not read again.  

My approach will need to take a snapshot of the folder's content since in theory, another csv file could be added to this folder while my code is running.

Here's the current way I'v been processing csv files but in previous projects this file was having new rows appended to it while this project will have a new csv file for each record,

Dim fileReader As New CSVFileReader
Dim tblWithdraws As New DataTable()
           tblWithdraws = fileReader.ReadFile(filePath & "Withdraws.csv")

Here's my class module that someone on here had provided:

Imports System.IO
Imports Microsoft.VisualBasic.FileIO
Imports System.Collections.Generic

Public Class CSVFileReader

    ''' <summary>
    ''' Read a CSV file, and create a DataTable.
    ''' </summary>
    ''' <param name="fileName">The file to parse</param>
    ''' <returns>A DataTable with the file contents</returns>

    Public Function ReadFile(ByVal fileName As String) As DataTable

        ' Initialize the return values
        Dim list As New List(Of String())

        Dim table As DataTable = Nothing

        Using parser As New TextFieldParser(fileName)

            ' Setup the comma-delimited file parser.
            parser.TextFieldType = FieldType.Delimited
            parser.Delimiters = New String() {","}
            parser.HasFieldsEnclosedInQuotes = True

            While Not parser.EndOfData
                    ' Read the comma-delimited text as fields into a string array.
                    Dim input As String() = parser.ReadFields()

                    If table Is Nothing Then
                        table = Me.CreateTable(Path.GetFileName(fileName), input)
                    End If

                    If input(0).Trim <> "" And input(0) <> "******" Then

                        Me.AddRow(table, input)
                        'Else send to Error table

                    End If

                Catch ex As MalformedLineException
                    ' Ignore invalid lines.
                End Try
            End While

        End Using

        Return table

    End Function

    Private Function CreateTable(ByVal name As String, ByVal input As String()) As DataTable

        Dim table As New DataTable(name)
        For index As Integer = 1 To input.Length
            table.Columns.Add("F" & index)
        Next index
        Return table
    End Function

    Private Sub AddRow(ByVal table As DataTable, ByVal input As String())

        Dim row As DataRow = table.NewRow()
        For index As Integer = 0 To table.Columns.Count
            If index < input.Length Then
                row(index) = input(index)
            End If
        Next index

    End Sub

End Class

Join our community to see this answer!
Unlock 1 Answer and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros