473,834 Members | 2,009 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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(ByV al aFileName As String) As DataTable
' Open the Excel Spreadsheet, and using ODBC, read it into a DataTable for
later processing
Dim odbcConnectionS tring As String = _
"Driver={Micros oft Excel Driver (*.xls)};Driver Id=790;" & _
"Dbq=" & aFileName & ";"
Dim odbcConn As New OdbcConnection( odbcConnectionS tring)
Dim odbcCmd As New OdbcCommand("SE LECT * FROM [Sheet1$]", odbcConn)
Dim odbcAdapter As New OdbcDataAdapter (odbcCmd)
Dim Dt As New DataTable
odbcConn.Open()
odbcAdapter.Fil l(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 8960
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******** ******@TK2MSFTN GP12.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(ByV al aFileName As String) As DataTable
' Open the Excel Spreadsheet, and using ODBC, read it into a DataTable for
later processing
Dim odbcConnectionS tring As String = _
"Driver={Micros oft Excel Driver (*.xls)};Driver Id=790;" & _
"Dbq=" & aFileName & ";"
Dim odbcConn As New OdbcConnection( odbcConnectionS tring)
Dim odbcCmd As New OdbcCommand("SE LECT * FROM [Sheet1$]", odbcConn)
Dim odbcAdapter As New OdbcDataAdapter (odbcCmd)
Dim Dt As New DataTable
odbcConn.Open()
odbcAdapter.Fil l(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******** ********@TK2MSF TNGP10.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******** ******@TK2MSFTN GP12.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(ByV al aFileName As String) As DataTable
' Open the Excel Spreadsheet, and using ODBC, read it into a DataTable
for
later processing
Dim odbcConnectionS tring As String = _
"Driver={Micros oft Excel Driver (*.xls)};Driver Id=790;" & _
"Dbq=" & aFileName & ";"
Dim odbcConn As New OdbcConnection( odbcConnectionS tring)
Dim odbcCmd As New OdbcCommand("SE LECT * FROM [Sheet1$]", odbcConn)
Dim odbcAdapter As New OdbcDataAdapter (odbcCmd)
Dim Dt As New DataTable
odbcConn.Open()
odbcAdapter.Fil l(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******** ********@TK2MSF TNGP10.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******** ******@TK2MSFTN GP12.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(ByV al aFileName As String) As DataTable
' Open the Excel Spreadsheet, and using ODBC, read it into a DataTable
for
later processing
Dim odbcConnectionS tring As String = _
"Driver={Micros oft Excel Driver (*.xls)};Driver Id=790;" & _
"Dbq=" & aFileName & ";"
Dim odbcConn As New OdbcConnection( odbcConnectionS tring)
Dim odbcCmd As New OdbcCommand("SE LECT * FROM [Sheet1$]", odbcConn)
Dim odbcAdapter As New OdbcDataAdapter (odbcCmd)
Dim Dt As New DataTable
odbcConn.Open()
odbcAdapter.Fil l(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\\Micr osoft\\Jet\\4.0 \\Engines\\Exce l"
regVersion = Registry.LocalM achine.OpenSubK ey(keyValue,
True)

Dim intVersion As Integer = 0
If (Not regVersion Is Nothing) Then
intVersion = regVersion.GetV alue("TypeGuess Rows",
0)
If intVersion <> 0 Then
regVersion.SetV alue("TypeGuess Rows", 0)
End If
regVersion.Clos e()
End If

' you can set this value back to it's original value by
doing
regVersion.SetV alue("TypeGuess Rows", 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******** ********@TK2MSF TNGP10.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******** ******@TK2MSFTN GP12.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(ByV al aFileName As String) As DataTable
' Open the Excel Spreadsheet, and using ODBC, read it into a DataTable
for
later processing
Dim odbcConnectionS tring As String = _
"Driver={Micros oft Excel Driver (*.xls)};Driver Id=790;" & _
"Dbq=" & aFileName & ";"
Dim odbcConn As New OdbcConnection( odbcConnectionS tring)
Dim odbcCmd As New OdbcCommand("SE LECT * FROM [Sheet1$]", odbcConn)
Dim odbcAdapter As New OdbcDataAdapter (odbcCmd)
Dim Dt As New DataTable
odbcConn.Open()
odbcAdapter.Fil l(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
7021
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 aware that when reading Excel Spreadsheets it will determine the datatype of a column, by looking at the content of the first few rows. If it determines that the column is text, then all numeric cells/fields are returned as null and if the column...
6
18858
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 the data in the excel file that if the first character in the excel file cell is numeric it will read and write only numeric values only. If I sort the coloumn in the excel file and the first character in the cell read is alphanumeric then only...
1
2327
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) Response.ContentType = "application/vnd.ms-excel" Response.Charset = "" Response.AppendHeader("content-disposition", "attachment;filename=test.xls") Response.Write(txtWriter.ToString) Response.End()
4
2621
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 looks has the following columns: Data Description / Source / 1990 / 1991 / 1992 / etc. It's set up as a pivot-table (at least I think so... not too familiar with Excel Pivot Tables), based on the first two columns. Anyway, when I run the
5
2832
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""" cnnExcel = New OleDbConnection(strExcel) da = New OleDbDataAdapter("Select * from ", cnnExcel) da.Fill(ds, "Excel") dv = New DataView(ds.Tables("Excel")) This works fine exept when the following condition happens: The part
1
2655
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 I entered text values and for numeric columns I entered numeric values. It works fine and pass through all the validation checks and gets inserted into the database successfully. But when I gave some junk values in the excel sheet and tried to...
0
361
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 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.
9
22515
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 namespaces and classes should I use and how? -- dba123
4
8078
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 in a table in Access with Text data type. For some reason Access only wants to paster the first 3 records. Can anyone please help me? Thanks in Advance
0
9646
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10795
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10550
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10220
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9332
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7760
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6957
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5627
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
3981
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.