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

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#" <(E-Mail Removed)> wrote in message
news:ljDJd.1190$(E-Mail Removed)...
> 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
>
>

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#" <(E-Mail Removed)> wrote in message
news:RHDJd.1192$(E-Mail Removed)...
> 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#" <(E-Mail Removed)> wrote in message
> news:ljDJd.1190$(E-Mail Removed)...
>> 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
>>
>>
>
>

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# <(E-Mail Removed)> 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#" <(E-Mail Removed)> wrote in message
> news:RHDJd.1192$(E-Mail Removed)...
>> 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#" <(E-Mail Removed)> wrote in message
>> news:ljDJd.1190$(E-Mail Removed)...
>>> 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/

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" <(E-Mail Removed)> wrote in message
newspsk6623o1xhg4go@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# <(E-Mail Removed)> 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#" <(E-Mail Removed)> wrote in message
> > news:RHDJd.1192$(E-Mail Removed)...
> >> 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#" <(E-Mail Removed)> wrote in message
> >> news:ljDJd.1190$(E-Mail Removed)...
> >>> 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/