Having problems with your account or logging in?
A lot of changes are happening in the community right now. Some may affect you. READ MORE HERE

A DSN-less method of reading CSV files in Workbench and Silk4Net

A DSN-less method of reading CSV files in Workbench and Silk4Net

The following class shows how to read a CSV file using SQL calls without needing to create a DSN first. The class requires two parameters: the SQL command and the full path to the csv file (including the file name). The information from the csv file is read into a datatable, from which you can extract the data as required by your project.

There is a requirement by Microsoft's text driver to have a schema file next to the csv file to define the file structure and delimiter. A sample minimal schema.ini file will contain the csv file name and the delimiter type:

[yourCSVfilename.csv]
Format=CSVDelimited

The schema.ini required for the test csv file is included in the archive.

Although the following code is in vb.Net there are two separate solutions attached to this article. One is written in vb.Net and the other is written in C# (C-Sharp).


Imports System.Data.Odbc
 
Public Class csv
 
  ' For more information on text driver schemas see
  ' http://msdn.microsoft.com/en-us/library/ms709353%28v=vs.85%29.aspx
 
  ' -------------------------------------------------------------
  ' Use a dataset to hold the results of reading the csv file
  Friend dsCSVData As New DataSet
  Friend dtCSVInfo As New DataTable
 
  ' -------------------------------------------------------------
  ' Read the named file
  Public Sub readCSV(ByVal sSQLCommand As String, ByVal sCSV As String)
 
    Dim sCSVFile As String = Nothing
    Dim sCSVPath As String = Nothing
 
    ' Sanity checks:
    ' Make sure there is a space at the end of the SQL command
    If Microsoft.VisualBasic.Right(sSQLCommand, 1) <> Chr(32) Then sSQLCommand = sSQLCommand & Chr(32)
    ' Make sure the dataset and datatable are empty
    dsCSVData.Clear()
    dtCSVInfo.Clear()
 
    ' Check the file exists
    If Not My.Computer.FileSystem.FileExists(sCSV) Then
      MsgBox("File not found!", vbOKOnly + vbExclamation, "Read CSV")
      Exit Sub
    EndIf
    ' End sanity checks
 
    ' Break down the file path
    sCSVFile = My.Computer.FileSystem.GetName(sCSV)
    sCSVPath = My.Computer.FileSystem.GetParentPath(sCSV)
 
    ' Construct the connection string for the csv file
    Dim sConnString As String = "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=" & sCSVPath & ";Extensions=asc,csv,tab,txt"
    ' Open a new ODBC connection
    Dim sConn As New OdbcConnection(sConnString)
    sConn.Open()
 
    ' Create a new data adapter so that we can fill the dataset
    Dim daCSV As New OdbcDataAdapter(sSQLCommand & sCSVFile, sConn)
    ' Fill the dataset from the data adapter
    daCSV.Fill(dsCSVData, sCSVFile)
    ' Fill the datatable from the dataset. Doing this means we get a recognisable layout.
    dtCSVInfo = dsCSVData.Tables(sCSVFile)
 
    ' Close the connection
    sConn.Close()
 
  End Sub
 
End Class

Once the dataset and datatables are assembled we make a call to the parseCSVData() function. Although the sample csv file only has a single table, the parseCSVData() function can deal with multiple tables.


Private Sub parseCSVData()

  Dim sData As String = Nothing
  Dim i As Integer = 0

  ' Do something with the contents of the dataset
  For Each sTable In CSVData.dsCSVData.Tables
    For Each sRow As DataRow In CSVData.dtCSVInfo.Rows
      sData = Nothing
      For i = 0 To sRow.ItemArray.Count - 1

        ' Each sRow.ItemArray.Count() is a single row of the current table.
        ' At this point you will need to add code to deal with the row data as
        ' needed for your test.

      Next
      ' Get the next row
    Next
    ' Get the next table
  Next

End Sub


These two archives each contain a complete Visual Studio 2010 solution that illustrates how to call the class and display the resulting data in a listbox. Please copy the csvdir folder to the root of your c:\drive. In this folder you will find the sample csv file and also the required schema.ini file. If you cannot copy the folder to the root of the c:\ drive then you will need to change the solution code to point to wherever you have copied the csvdir folder.

3782.CSVnoDSN.zip

6470.CSVnoDSN-C-sharp.zip__

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Top Contributors
Version history
Revision #:
1 of 1
Last update:
‎2014-10-15 20:27
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.