Problem Reading Excel Spreadsheet From VB.NET

Michael C#

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
application I can open and read the first two column headings, but the
others all come through as "F3", "F4", etc. Anyone have any ideas? Here's
my code:

Dim objConn As OleDbConnection = New OleDbConnection(sConnectionString)
objConn.Open()
Dim strCmd As String = String.Format("SELECT * FROM [{0}]", table)
Dim objCmdSelect As OleDbCommand = New OleDbCommand(strCmd, objConn)
Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect
objAdapter1.Fill(objDataset1)
Dim Arr As New ArrayList
For Each c As DataColumn In ds.Tables(0).Rows(0).ItemArray
Dim s As String = c.ColumnName
Arr.Add(s)
Next
objAdapter1.Dispose()
objCmdSelect.Dispose()
objConn.Close()
objConn.Dispose()
TIA

Nov 21 '05 #1

Michael C#

Oops. Posted to the wrong place, let me try again. Please excuse
double-post:

I changed the name of one of the columns to "X" and it read the ColumnName
correctly. I guess it's assigning alphanumeric "F3, F4, etc." because the
ColumnNames are all numeric (1990, 1991, etc.) Is there any way to force it
to recognize these values as actual ColumnNames so I don't lose the
information?

Thanks
"Michael C#" <xy*@abcdef.com> wrote in message
news:lj****************@fe08.lga...

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 application I can open and read the first two column headings, but the
others all come through as "F3", "F4", etc. Anyone have any ideas?
Here's my code:

Dim objConn As OleDbConnection = New OleDbConnection(sConnectionString)
objConn.Open()
Dim strCmd As String = String.Format("SELECT * FROM [{0}]", table)
Dim objCmdSelect As OleDbCommand = New OleDbCommand(strCmd, objConn)
Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect
objAdapter1.Fill(objDataset1)
Dim Arr As New ArrayList
For Each c As DataColumn In ds.Tables(0).Rows(0).ItemArray
Dim s As String = c.ColumnName
Arr.Add(s)
Next
objAdapter1.Dispose()
objCmdSelect.Dispose()
objConn.Close()
objConn.Dispose()
TIA

Nov 21 '05 #2

Michael C#

I think I found the solution. Setting HDR=YES in the Extended Properties of
the OleDb Connection string seems to do the trick.

Thanks

"Michael C#" <xy*@abcdef.com> wrote in message
news:RH***************@fe08.lga...

Oops. Posted to the wrong place, let me try again. Please excuse
double-post:

I changed the name of one of the columns to "X" and it read the ColumnName
correctly. I guess it's assigning alphanumeric "F3, F4, etc." because the
ColumnNames are all numeric (1990, 1991, etc.) Is there any way to force
it
to recognize these values as actual ColumnNames so I don't lose the
information?

Thanks
"Michael C#" <xy*@abcdef.com> wrote in message
news:lj****************@fe08.lga...

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 application I can open and read the first two column headings,
but the others all come through as "F3", "F4", etc. Anyone have any
ideas? Here's my code:

Dim objConn As OleDbConnection = New OleDbConnection(sConnectionString)
objConn.Open()
Dim strCmd As String = String.Format("SELECT * FROM [{0}]", table)
Dim objCmdSelect As OleDbCommand = New OleDbCommand(strCmd, objConn)
Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect
objAdapter1.Fill(objDataset1)
Dim Arr As New ArrayList
For Each c As DataColumn In ds.Tables(0).Rows(0).ItemArray
Dim s As String = c.ColumnName
Arr.Add(s)
Next
objAdapter1.Dispose()
objCmdSelect.Dispose()
objConn.Close()
objConn.Dispose()
TIA

Nov 21 '05 #3

Al Jones

Also, just a shot in the dark, I'd bet that if you changed them from the
default of numeric to character they'd come up correct, too.

On Tue, 25 Jan 2005 22:32:24 -0500, Michael C# <xy*@abcdef.com> wrote:

I think I found the solution. Setting HDR=YES in the Extended
Properties of
the OleDb Connection string seems to do the trick.

Thanks

"Michael C#" <xy*@abcdef.com> wrote in message
news:RH***************@fe08.lga...

Oops. Posted to the wrong place, let me try again. Please excuse
double-post:

I changed the name of one of the columns to "X" and it read the
ColumnName
correctly. I guess it's assigning alphanumeric "F3, F4, etc." because
the
ColumnNames are all numeric (1990, 1991, etc.) Is there any way to
force
it
to recognize these values as actual ColumnNames so I don't lose the
information?

Thanks
"Michael C#" <xy*@abcdef.com> wrote in message
news:lj****************@fe08.lga...

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 application I can open and read the first two column headings,
but the others all come through as "F3", "F4", etc. Anyone have any
ideas? Here's my code:

Dim objConn As OleDbConnection = New OleDbConnection(sConnectionString)
objConn.Open()
Dim strCmd As String = String.Format("SELECT * FROM [{0}]", table)
Dim objCmdSelect As OleDbCommand = New OleDbCommand(strCmd, objConn)
Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect
objAdapter1.Fill(objDataset1)
Dim Arr As New ArrayList
For Each c As DataColumn In ds.Tables(0).Rows(0).ItemArray
Dim s As String = c.ColumnName
Arr.Add(s)
Next
objAdapter1.Dispose()
objCmdSelect.Dispose()
objConn.Close()
objConn.Dispose()
TIA

--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Nov 21 '05 #4

Michael C

Actually I tried that and it didn't work with HDR=YES. I'm assuming that
OleDb probably won't let you use all-numeric names for columns? Not sure,
but that's what it ended up looking like. I ended up setting the extended
properties to HDR=NO and treating the first line as just another row of
data. Also had to set IMEX=1 in the extended properties to get consistent
results.

Thanks,
Michael C#

"Al Jones" <al**********@shotmail.com> wrote in message
news:opsk6623o1xhg4go@aljones...

Also, just a shot in the dark, I'd bet that if you changed them from the
default of numeric to character they'd come up correct, too.

On Tue, 25 Jan 2005 22:32:24 -0500, Michael C# <xy*@abcdef.com> wrote:

I think I found the solution. Setting HDR=YES in the Extended
Properties of
the OleDb Connection string seems to do the trick.

Thanks

"Michael C#" <xy*@abcdef.com> wrote in message
news:RH***************@fe08.lga...

Oops. Posted to the wrong place, let me try again. Please excuse
double-post:

I changed the name of one of the columns to "X" and it read the
ColumnName
correctly. I guess it's assigning alphanumeric "F3, F4, etc." because
the
ColumnNames are all numeric (1990, 1991, etc.) Is there any way to
force
it
to recognize these values as actual ColumnNames so I don't lose the
information?

Thanks
"Michael C#" <xy*@abcdef.com> wrote in message
news:lj****************@fe08.lga...
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 application I can open and read the first two column headings,
but the others all come through as "F3", "F4", etc. Anyone have any
ideas? Here's my code:

Dim objConn As OleDbConnection = New OleDbConnection(sConnectionString) objConn.Open()
Dim strCmd As String = String.Format("SELECT * FROM [{0}]", table)
Dim objCmdSelect As OleDbCommand = New OleDbCommand(strCmd, objConn)
Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect
objAdapter1.Fill(objDataset1)
Dim Arr As New ArrayList
For Each c As DataColumn In ds.Tables(0).Rows(0).ItemArray
Dim s As String = c.ColumnName
Arr.Add(s)
Next
objAdapter1.Dispose()
objCmdSelect.Dispose()
objConn.Close()
objConn.Dispose()
TIA

--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Nov 21 '05 #5