S
Scott M. Lyon
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
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