473,394 Members | 1,738 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

Problem reading text/numeric data from Excel

I've just discovered a bug in some code I wrote a little while ago, and I
need you guys' help to fix it.
My program imports data from a standard Excel Spreadsheet (just with
specific column headers). I used ODBC in my VB.NET program to read that
spreadsheet into a dataset, to make it easy to manipulate. The code I use to
read it is as the bottom of this posting.
The problem I'm having though, is that I have one column of data
(potentially a few others as well) that for one input file starts off as
alphabetic, and then for some rows is numeric.
Unfortunately, when I read it into the dataset, while the alphabetic rows
come in just fine, the numeric ones are showing up as System.DBNull.
How can I fix this, short of requiring the input file to have the cells
formatted explicitly to text?
The code I use to read is the following function:

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 data (for the column in question) looks similar to this:

Unit
-----
ABC123 (returns ABC123 in the dataset as expected)
DEF456 (returns DEF456 as expected)
789123 (returns a System.DBNull)
456789 (returns a System.DBNull)
Any ideas?
Thanks!
-Scott

Nov 21 '05 #1
5 8915
Not specifically and answer to your question, but maybe a workaround...

Instead of reading the excel spreadsheet using ODBC, try treating it as an
object and accessing the values in the individual cells. It sounds like
ODBC is assuming the first row of data defines the types for each field, and
eliminating ODBC should get you around this.

The problem is this means a significant rewrite of your aproach.
"Scott M. Lyon" <sc******************@rapistan.BLUE.com> wrote in message
news:eS**************@TK2MSFTNGP12.phx.gbl...
I've just discovered a bug in some code I wrote a little while ago, and I
need you guys' help to fix it.
My program imports data from a standard Excel Spreadsheet (just with
specific column headers). I used ODBC in my VB.NET program to read that
spreadsheet into a dataset, to make it easy to manipulate. The code I use to read it is as the bottom of this posting.
The problem I'm having though, is that I have one column of data
(potentially a few others as well) that for one input file starts off as
alphabetic, and then for some rows is numeric.
Unfortunately, when I read it into the dataset, while the alphabetic rows
come in just fine, the numeric ones are showing up as System.DBNull.
How can I fix this, short of requiring the input file to have the cells
formatted explicitly to text?
The code I use to read is the following function:

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 data (for the column in question) looks similar to this:

Unit
-----
ABC123 (returns ABC123 in the dataset as expected)
DEF456 (returns DEF456 as expected)
789123 (returns a System.DBNull)
456789 (returns a System.DBNull)
Any ideas?
Thanks!
-Scott

Nov 21 '05 #2
I was afraid of that... I should have known it was coming together just a
little TOO easily... ;)
Thanks!

"Jim Underwood" <ja*************@fallonclinic.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Not specifically and answer to your question, but maybe a workaround...

Instead of reading the excel spreadsheet using ODBC, try treating it as an
object and accessing the values in the individual cells. It sounds like
ODBC is assuming the first row of data defines the types for each field,
and
eliminating ODBC should get you around this.

The problem is this means a significant rewrite of your aproach.
"Scott M. Lyon" <sc******************@rapistan.BLUE.com> wrote in message
news:eS**************@TK2MSFTNGP12.phx.gbl...
I've just discovered a bug in some code I wrote a little while ago, and I
need you guys' help to fix it.
My program imports data from a standard Excel Spreadsheet (just with
specific column headers). I used ODBC in my VB.NET program to read that
spreadsheet into a dataset, to make it easy to manipulate. The code I use

to
read it is as the bottom of this posting.
The problem I'm having though, is that I have one column of data
(potentially a few others as well) that for one input file starts off as
alphabetic, and then for some rows is numeric.
Unfortunately, when I read it into the dataset, while the alphabetic rows
come in just fine, the numeric ones are showing up as System.DBNull.
How can I fix this, short of requiring the input file to have the cells
formatted explicitly to text?
The code I use to read is the following function:

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 data (for the column in question) looks similar to this:

Unit
-----
ABC123 (returns ABC123 in the dataset as expected)
DEF456 (returns DEF456 as expected)
789123 (returns a System.DBNull)
456789 (returns a System.DBNull)
Any ideas?
Thanks!
-Scott


Nov 21 '05 #3
Hey Jim,

It's official... I'm giving up on getting ODBC working for this problem, and
resigning myself to reading the spreadsheet "manually"...
The only thing is, I am not sure how to do that with an Excel Spreadsheet
(short of reading the file one byte at a time, and trying to figure out what
format Excel spreadsheets are saved in).

Is this something where I'd (somehow) create an Excel object to load the
data into, and that object would allow me access to individual cells?
How would I do all that?
Thanks!
-Scott
"Jim Underwood" <ja*************@fallonclinic.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Not specifically and answer to your question, but maybe a workaround...

Instead of reading the excel spreadsheet using ODBC, try treating it as an
object and accessing the values in the individual cells. It sounds like
ODBC is assuming the first row of data defines the types for each field,
and
eliminating ODBC should get you around this.

The problem is this means a significant rewrite of your aproach.
"Scott M. Lyon" <sc******************@rapistan.BLUE.com> wrote in message
news:eS**************@TK2MSFTNGP12.phx.gbl...
I've just discovered a bug in some code I wrote a little while ago, and I
need you guys' help to fix it.
My program imports data from a standard Excel Spreadsheet (just with
specific column headers). I used ODBC in my VB.NET program to read that
spreadsheet into a dataset, to make it easy to manipulate. The code I use

to
read it is as the bottom of this posting.
The problem I'm having though, is that I have one column of data
(potentially a few others as well) that for one input file starts off as
alphabetic, and then for some rows is numeric.
Unfortunately, when I read it into the dataset, while the alphabetic rows
come in just fine, the numeric ones are showing up as System.DBNull.
How can I fix this, short of requiring the input file to have the cells
formatted explicitly to text?
The code I use to read is the following function:

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 data (for the column in question) looks similar to this:

Unit
-----
ABC123 (returns ABC123 in the dataset as expected)
DEF456 (returns DEF456 as expected)
789123 (returns a System.DBNull)
456789 (returns a System.DBNull)
Any ideas?
Thanks!
-Scott


Nov 23 '05 #4
There is actually a workaround for your problem.

Although accessing individual cell is safer, but what you can do is to
modify the registry that makes Excel guess the data type with the data
from the first 8 rows (that's the default setting on my machine)...

Here is the code in VB .NET that does this. The only problem with this
is that your application have to have the permission to modify the
registry.

' varify Excel settings
Dim regVersion As RegistryKey
Dim keyValue As String
keyValue =
"Software\\Microsoft\\Jet\\4.0\\Engines\\Excel "
regVersion = Registry.LocalMachine.OpenSubKey(keyValue,
True)

Dim intVersion As Integer = 0
If (Not regVersion Is Nothing) Then
intVersion = regVersion.GetValue("TypeGuessRows",
0)
If intVersion <> 0 Then
regVersion.SetValue("TypeGuessRows", 0)
End If
regVersion.Close()
End If

' you can set this value back to it's original value by
doing
regVersion.SetValue("TypeGuessRows", intVersion)

after your operations...

By the way, does anyone know if setting TypeGuessRows to 0 will affect
anything (performance?)

Best regards,

Charlie Chang
Scott M. Lyon wrote:
Hey Jim,

It's official... I'm giving up on getting ODBC working for this problem, and
resigning myself to reading the spreadsheet "manually"...
The only thing is, I am not sure how to do that with an Excel Spreadsheet
(short of reading the file one byte at a time, and trying to figure out what
format Excel spreadsheets are saved in).

Is this something where I'd (somehow) create an Excel object to load the
data into, and that object would allow me access to individual cells?
How would I do all that?
Thanks!
-Scott
"Jim Underwood" <ja*************@fallonclinic.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Not specifically and answer to your question, but maybe a workaround...

Instead of reading the excel spreadsheet using ODBC, try treating it as an
object and accessing the values in the individual cells. It sounds like
ODBC is assuming the first row of data defines the types for each field,
and
eliminating ODBC should get you around this.

The problem is this means a significant rewrite of your aproach.
"Scott M. Lyon" <sc******************@rapistan.BLUE.com> wrote in message
news:eS**************@TK2MSFTNGP12.phx.gbl...
I've just discovered a bug in some code I wrote a little while ago, and I
need you guys' help to fix it.
My program imports data from a standard Excel Spreadsheet (just with
specific column headers). I used ODBC in my VB.NET program to read that
spreadsheet into a dataset, to make it easy to manipulate. The code I use

to
read it is as the bottom of this posting.
The problem I'm having though, is that I have one column of data
(potentially a few others as well) that for one input file starts off as
alphabetic, and then for some rows is numeric.
Unfortunately, when I read it into the dataset, while the alphabetic rows
come in just fine, the numeric ones are showing up as System.DBNull.
How can I fix this, short of requiring the input file to have the cells
formatted explicitly to text?
The code I use to read is the following function:

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 data (for the column in question) looks similar to this:

Unit
-----
ABC123 (returns ABC123 in the dataset as expected)
DEF456 (returns DEF456 as expected)
789123 (returns a System.DBNull)
456789 (returns a System.DBNull)
Any ideas?
Thanks!
-Scott



Nov 28 '05 #5
Jan
If workbook you are importing is not large (less than 5 sheets and 150
rows per sheet), you have our ExcelLite Free component you can freely
use in commercial apps. If your worksheet grows in size, you can easily
update to ExcelLite Professional.
Automation is another option, but beware of drawbacks:
http://www.gemboxsoftware.com/ExcelLite.htm#Automation
Jan
GemBox Software
www.gemboxsoftware.com

Scott M. Lyon wrote:
Hey Jim,

It's official... I'm giving up on getting ODBC working for this problem, and
resigning myself to reading the spreadsheet "manually"...
The only thing is, I am not sure how to do that with an Excel Spreadsheet
(short of reading the file one byte at a time, and trying to figure out what
format Excel spreadsheets are saved in).

Is this something where I'd (somehow) create an Excel object to load the
data into, and that object would allow me access to individual cells?
How would I do all that?
Thanks!
-Scott


Nov 29 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Derk | last post by:
I have an VB App that reads an Excel Sspreadsheet and it seems to having problems determining the data type of a column. The column in question has alpha numeric content eg S001, B123 or 1234. I am...
6
by: Paul | last post by:
I was wondering if anyone has had an issue where using vba code to read an excel file and import the data into an access table some records are not imported from the excel file. It seems looking at...
1
by: NancyA | last post by:
I am using the following code to write data from a datagrid to an Excel file: Dim tw As New System.IO.StringWriter Dim hw As New System.Web.UI.HtmlTextWriter(tw) dg.RenderControl(hw)...
4
by: Michael C# | last post by:
Hi all, I have a little program that uses OleDb to open and read an Excel spreadsheet from VB.NET. The problem I'm running into is it's not reading the column headers... The Excel worksheet...
5
by: Rob T | last post by:
I have a routine that imports a list of part numbers into a dataview: strExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & boxFile.Text & ";Extended Properties=""Excel 8.0;HDR=NO"""...
1
by: Ramakrishnan Nagarajan | last post by:
Hi, I am converting Excel data into a Dataset in C#. There are around 24 columns in the Excel Sheet. First I tried to insert one row with correct values in the Excel sheet. i.e. for text columns...
0
by: Scott M. Lyon | last post by:
I've just discovered a bug in some code I wrote a little while ago, and I need you guys' help to fix it. My program imports data from a standard Excel Spreadsheet (just with specific column...
9
by: dba123 | last post by:
I need some help and direction on what classes and an example or two (article) on how to read an Excel Worksheet and insert one column into a database table column. I am using .NET 2.0 only. What...
4
by: andrewmac | last post by:
I hope someone can help - I am new to Access so please be patient. I have a list of about 100 equity tickers in a column in Excel formatted as Text. I am trying to copy and paste into a field...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.