Import CSV into a DataTable

Question

Possible Duplicate:
How to read a csv file into a .net datatable

I have a problem in my project, where I am trying to read data in a csv file, I want to convert this data to a datatable.

How can I do this?

My code:

System.Data.Odbc.OdbcConnection conn;
DataTable insDataTable = new DataTable();
System.Data.Odbc.OdbcDataAdapter da;
string folder = files.FullName;
string file = System.IO.Path.GetFileName(fUpload.PostedFile.FileName);
conn = new System.Data.Odbc.OdbcConnection(@"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + folder + ";Extensions=asc,csv,tab,txt;Persist Security Info=False");
da = new System.Data.Odbc.OdbcDataAdapter("select * from [" + file + "]", conn);
da.Fill(insDataTable);

It gives an error like :

ERROR [42S02] [Microsoft][ODBC Text Driver] The Microsoft Jet database engine could not find the object 'test.csv'. Make sure the object exists and that you spell its name and the path name correctly.

I am checking there is a file 'test.csv' and the file path is correct :(

Already asked and answered here: http://stackoverflow.com/questions/1050112/how-to-read-a-csv-file-into-a-net-datatable The question may be different, but the answer of how to read a csv into a datatable remains the same. — David, Feb 17 '11 at 21:36
**This is not a dupe**, as the problem in the error message is more about creating the odbc connection string based on the asp.net upload vs just creating a datatable from csv. — Joel Coehoorn, Feb 17 '11 at 22:01

score 0 · Answer 1 · edited Feb 17 '11 at 21:51

Couple of comments.

1) You may find the FileHelpers library is useful for performing operations like this. http://www.filehelpers.com/quick_start.html

2) You may find the following functions useful to get what you are looking for.

Private Function GetTextDataSource(ByVal Filename As String, ByVal bIsPreview As Boolean, ByVal bIsCommaSeperated As Boolean) As DataView

    Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/Folder/Path/") & ";"
    If chkUploadFileColumnsFirstRow.Checked Then
        sConnectionString &= "Extended Properties='text;HDR=Yes;FMT=" & IIf(bIsCommaSeperated, "CSVDelimited", "TabDelimited") & ";IMEX=1'"
    Else
        sConnectionString &= "Extended Properties='text;FMT=" & IIf(bIsCommaSeperated, "CSVDelimited", "TabDelimited") & ";IMEX=1'"
    End If

    Dim objConnection As New OleDbConnection(sConnectionString)

    Dim dv As DataView
    Try
        dv = GetOleDbDataView("SELECT " & IIf(bIsPreview, "TOP 1 ", "") & " * FROM [" & Replace(Filename, ";", "") & "]", objConnection)
    Catch ex As OleDbException
        Logger.Error(ex.Message)
        AddError("Error selecting data from uploaded file, please ensure your file matches the correct specification and try again.")
        Return Nothing
    End Try
    Return dv
End Function

Private Function GetOleDbDataView(ByVal SelectCommand As String, ByVal objConn As OleDbConnection) As DataView
    ' Create new OleDbCommand to return data from worksheet.
    Dim objCmdSelect As New OleDbCommand(SelectCommand, objConn)

    ' Create new OleDbDataAdapter that is used to build a DataSet 
    ' based on the preceding SQL SELECT statement.
    Dim objAdapter1 As New OleDbDataAdapter()

    ' Pass the Select command to the adapter.
    objAdapter1.SelectCommand = objCmdSelect

    ' Create new DataSet to hold information from the worksheet.
    Dim objDataset1 As New DataSet()

    ' Fill the DataSet witht he information from the worksheet.
    objAdapter1.Fill(objDataset1, "ExcelReturnData")

    Return objDataset1.Tables(0).DefaultView
End Function

Import CSV into a DataTable

1 Answers1