Use the OleDbDataAdapter to Read and Parse a CSV File - October 2010
into a DataTable or Collection

If you download this tip please consider making a modest donation.

In July 2010 I wrote some code to read values from an Excel spreadsheet. Click here to view that code. Unfortunately it did not work on my host's server. And I didn't want to take the time to figure out why. So I revisited some code I wrote in April 2002 that uses ADO to parse a text file. And I found some code on the web. I reworked the code. I encapsulated it in a class module called: cReadCSVIntoDataTable. I made an Asp.Net, VB.Net, VS/2008 project to demonstrate it. It becomes the October 2010 tip-of-the-month.

2010/09/26 - I added code that returns the csv data into a collection. That code is contained in a class module called: cReadCSVIntoCollection. That module inherits the older class.

The Demo Application

The demo page:


File Sample.csv is parsed and its data is shown in a table.
Sample.csv is provided with the downloadable source code. See below.
Indicate whether the file contains a header row.
Limit the number of rows displayed by enter a number in the 'Maximum' field. Default is 50.

Sample CSV Files

The following CSV files are included with the downloadable source code:

Sample Calling Code - Return into a DataTable

Dim oRCSVIDT As New cReadCSVIntoDataTable
Dim oFU As FileUpload = Me.TableMain.FindControl("fileupload" & "csvfile")
Dim oChk As CheckBox = Me.TableMain.FindControl("chk" & "chkHeader")
Me.moDT = oRCSVIDT.ReadCSVIntoDataTable(oFU, GetSaveAsName(oFU), , oChk.Checked, True)
If Not Me.moDT Is Nothing Then
    ShowResultsUsingDataTable(Me.TableResults, Me.moDT)
End If

Sample Calling Code - Return into a Collection

Dim oRCSVIC As New cReadCSVIntoCollection
Dim oFU As FileUpload = Me.TableMain.FindControl("fileupload" & "csvfile")
Dim oChk As CheckBox = Me.TableMain.FindControl("chk" & "chkHeader")
Me.moCSVCollection = oRCSVIC.ReadCSVIntoCollection(sFileName, , oChk.Checked, True)
If Not Me.moCSVCollection Is Nothing Then
   
ShowResultsUsingCollection(Me.TableResults, Me.moCSVCollection)
End If
To view the collection properties examine code contained in subroutine ShowResultsUsingCollection.
The collection contains rows which contain columns.
Rows can be addressed using class: cCSVRow,
Columns as: cCSVCell.

Errata - Maximum Filename Size

I discovered through a lot of trial and error that the maximum file name length is 64 characters. Make sure that the file name passed to these routines does not exceed that length. I have included a function that will truncate the file name. See function: ShortenNameIfTooLong in module cReadCSVIntoCollection. It not only truncates the name but renames the corresponding file. A Guid is appended to the name to insure that it is unique.

Download VB.Net Code

Download the source code by clicking on the zipped file name.

Download - Source Zipped File Size
VS2008 VB Solution CSVDataReader.zip 80K

Previous Tip    Tip Index    Next Tip

Home

Updated 2011/12/28