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