Import CSV into a DataTable [duplicate]

This question already has answers here: Closed 11 years ago.

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.OdbcConne开发者_Go百科ction(@"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 :(

---

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