Friday, October 24, 2008

How to read CSV to dataset

I use simple JET OLEDB driver to read CSV files. Only con to this method is that delimiter in CSV must be exactly the same as in environment settings.

  Public Shared Function FillCSVToDataset(ByVal filePath As String, ByVal fileName As String) As DataSet

    Dim sConnectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Text;", filePath)
    Dim objConn As New OleDbConnection(sConnectionString)

    Dim sSelectStr As String = String.Format("SELECT * FROM {0}", fileName)

    Dim objCmdSelect As New OleDbCommand(sSelectStr, objConn)
    Dim objAdapter1 As New OleDbDataAdapter()
    objAdapter1.SelectCommand = objCmdSelect

    Dim objDataset1 As New DataSet()
    objAdapter1.Fill(objDataset1, "CSV")

    Return objDataset1
  End Function