CSV data access with Data.ODBC problem 1

[wmin]

Following script generates error:

Dim Conn As System.Data.Odbc.OdbcConnection,dt as datatable
Dim da As System.Data.Odbc.OdbcDataAdapter
Dim strConnstr,strImportfolder,strFilename as string


Microsoft Text Driver requires the folder in which the CSV file resides to initialize


strImportFolder = "c:\importfile" '
this is the folder in which the file resides

strFileName = "csvimport.csv"
'this is the csv file to be imported

strConnStr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + strImportFolder + ";"
Conn = New Odbc.OdbcConnection(strConnStr)



Note that we haven't supplied the CSV file name yet, we have supplied only the folder name, the file name is required when we query the CSV file.

--------------------------
Querying CSV File
--------------------------
We have initialized the connection object the querying syntax is as follows
Select * from [csvfile]

Since we have supplied the folder name already it is enough we supply only the filename when querying.


da = New System.data.Odbc.OdbcDataAdapter("select * from [" + strFileName + "]", conn)
da.Fill(dt)

-------------------------------------------
end
-------------------------------------------

Exception Details: System.ArgumentNullException: Value cannot be null. Parameter name: dataTable

Line xxx: da.Fill(dt)

... this code seems simple enough, any ideas of why it is breaking?

PS. Code was borrowed from

http://www.dotnetspider.com/technology/kbpages/890.aspx?cachekey=542

Authour: Author: Muralidharan Ramakrishnan

 

[ThatRickGuy]

I couldn't find the System.Data.ODBC namespace, but there is a Microsoft.Data.ODBC.dll reference available. So I used it. If yours works with the System reference, go with it.

The main issue in the code was the lack of a "New" keyword in the dt declaration.

And for some better form than the originator of the code, only declare multiple variables of the same type on one row. And preferably, only simple types (String, Char, Int, Decimal, etc).

Dim Conn As Microsoft.Data.Odbc.OdbcConnection
    Dim dt As New DataTable()

    Dim da As Microsoft.Data.Odbc.OdbcDataAdapter
    Dim strConnstr, strImportfolder, strFilename As String

    strImportfolder = "c:\user" '

    strFilename = "BuyoutInvoice.txt"
    'this is the csv file to be imported

    strConnstr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & strImportfolder & "\;"
    Conn = New Odbc.OdbcConnection(strConnstr)

    da = New Odbc.OdbcDataAdapter("select * from [" + strFilename + "]", Conn)
    
    da.Fill(dt)

    MsgBox(dt.Rows(0).Item(0).ToString)

-Rick

----------------------

http://www.ringdev.com

 

[wmin]

your code works, I also found that .csv file does not work, renaming it to .txt solves some problems - not sure why.

 

[at51178]

Hi,

I was wondering how can you set the delimiter to semi-colon with the above code you used, I am able to import data with a comma delimiter but I can not import it with a semi-colon delimiter.