Problem Reading Excel Spreadsheet From VB.NET

M

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
 
M

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
 
M

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# said:
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# said:
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
 
A

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.

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# said:
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# said:
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
 
M

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 said:
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.

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# said:
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


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top