By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,833 Members | 2,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,833 IT Pros & Developers. It's quick & easy.

Using ODBC to read from Excel - mixed format columns

P: n/a
I currently am working on a VB.NET application that imports data from an
Excel spreadsheet that the customer filled in.
I used the following code to use ODBC to read the data from the spreadsheet,
and return it in a datatable.

Public Function ReadExcelDT(ByVal aFileName As String) As DataTable
' Open the Excel Spreadsheet, and using ODBC, read it into a DataTable for
later processing
Dim odbcConnectionString As String = _
"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;" & _
"Dbq=" & aFileName & ";"
Dim odbcConn As New OdbcConnection(odbcConnectionString)
Dim odbcCmd As New OdbcCommand("SELECT * FROM [Sheet1$]", odbcConn)
Dim odbcAdapter As New OdbcDataAdapter(odbcCmd)
Dim Dt As New DataTable
odbcConn.Open()
odbcAdapter.Fill(Dt)

odbcConn.Close()
Return Dt
End Function
The problem: some of my columns are mixed format. In other words, some
fields are numeric, while others are alphabetic. My understanding of the way
that it works, it makes it's best "guess" what that format is supposed to
be, and all fields that are not in that format return only as DBNull values.
I also understand that the work-around for this is to enable something
called "import mode" in the connection string, so it would import everything
as text (including the numeric fields).
Unfortunately, I've had zero luck getting that working with ODBC. The only
code I have found on the internet (including Microsoft's site) regarding
enabling "import mode" is either using ADO or DAO, and both of those are
code in VB6.
The way I look at it, I have three choices:

1) Figure out how to enable "import mode" in ODBC
2) Figure out how to rewrite this code to use ADO or DAO (bearing in mind
that this is VB.NET)
3) Write my own Fill command that will "manually" read through the Excel
Spreadsheet (of course, in order to do that, I'll need to figure out how to
do that exactly).
Can anyone help?
Thanks in advance!

-Scott
Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Scott,

I dont know if this is working on ODBC
"IMEX=1;" tells the driver to always read "intermixed" data columns as text

See for this
www.connectionstrings.com

Or

http://www.carlprothman.net/Default.aspx?tabid=81

I hope this helps,

Cor
Nov 23 '05 #2

P: 1
After spending a while trying to find the solution for this problem. I managed to get around the problem using OldDb data adapter instead of Odbc. This is VB.NET code I suggest:

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Dim Con As New OleDbConnection(ConnectionString)
Dim CmdSelect As New OleDbCommand("SELECT * FROM [MAIN$]", Con)
Dim Adapter As New OleDbDataAdapter(CmdSelect)

More information can be found from

http://msdn2.microsoft.com/en-us/library/ms254500.aspx
http://support.microsoft.com/default...b;EN-US;257819
http://www.codeguru.com/cpp/data/mfc...cle.php/c8299/

Best of Luck :)
Jun 25 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.