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
