Convert CSV File to data table

Introduction

This class allows you to convert a csv file to a data table. Useful when coding database import applications.

Background

After writing numerous database import applications I decided to create a standard class for reading CSV files and converting them into a Data Table

Using the code

The CSV Reader Class. Firstly we need to know the file location so we create a string attribute to store the file location.

Private FileName As String  

The constructor requires the file location.

 Public Sub New(ByVal FileName As String)
        Me.FileName = FileName
 End Sub

The getcolumns method returns an array of string values. These string values are the column names. these can either be  Names for the first row in the csv file, achieved by setting the columnNames  attribute true or the names are Column1, Column2,.....

  Public Function getColumns(ByVal ColumnNames As Boolean) As String()
        Try
            Dim fileReader As New StreamReader(FileName)
            Dim line As String = fileReader.ReadLine
            fileReader.Close()
            Dim Columns() As String = line.Split(",")
            If ColumnNames Then
                Return Columns
            End If
            Dim i As Integer = 1
            Dim c As Integer = 0
            Dim columnsNames(Columns.Count - 1) As String
            For Each column As String In Columns
                columnsNames(c) = "column" & i
                i += 1
                c += 1
            Next
            Return columnsNames
        Catch ex As Exception
            'log to file    
        End Try
        Return Nothing
    End Function 

The returnData method will return a data table.

    Public Function ReturnData(ByVal ColumnNames As Boolean) As DataTable
        Try
            Dim dt As New DataTable
            For Each columnName In getColumns(ColumnNames)
                dt.Columns.Add(columnName)
            Next
            Dim fileReader As New StreamReader(FileName)
            If ColumnNames Then
                fileReader.ReadLine()
            End If
            Dim line As String = fileReader.ReadLine
            While Not IsNothing(line)
                line = line.Replace(Chr(34), "")
                dt.Rows.Add(line.Split(","))
                line = fileReader.ReadLine
            End While
            fileReader.Close()
            Return dt
        Catch ex As Exception
            'log to file
        End Try
        Return Nothing
    End Function