Problem Reading Excel Spreadsheet From VB.NET

Discussion in 'Microsoft VB .NET' started by Michael C#, Jan 26, 2005.

  1. Michael C#

    Michael C# Guest

    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
     
    Michael C#, Jan 26, 2005
    #1
    1. Advertisements

  2. Michael C#

    Michael C# Guest

    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#" <> wrote in message
    news:ljDJd.1190$...
    > 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
    >
    >
     
    Michael C#, Jan 26, 2005
    #2
    1. Advertisements

  3. Michael C#

    Michael C# Guest

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

    >
    >
     
    Michael C#, Jan 26, 2005
    #3
  4. Michael C#

    Al Jones Guest

    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# <> 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#" <> wrote in message
    > news:RHDJd.1192$...
    >> 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#" <> wrote in message
    >> news:ljDJd.1190$...
    >>> 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/
     
    Al Jones, Jan 26, 2005
    #4
  5. Michael C#

    Michael C Guest

    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" <> wrote in message
    news:blush:psk6623o1xhg4go@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# <> 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#" <> wrote in message
    > > news:RHDJd.1192$...
    > >> 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#" <> wrote in message
    > >> news:ljDJd.1190$...
    > >>> 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/
     
    Michael C, Jan 26, 2005
    #5
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Stu Lock
    Replies:
    0
    Views:
    247
    Stu Lock
    Oct 5, 2004
  2. Lucky
    Replies:
    0
    Views:
    358
    Lucky
    Dec 7, 2005
  3. Replies:
    1
    Views:
    1,079
    Bruce W. Darby
    Jan 22, 2007
  4. GH
    Replies:
    3
    Views:
    246
  5. HardySpicer

    Reading a text file and reading

    HardySpicer, Jan 15, 2009, in forum: Microsoft VB .NET
    Replies:
    14
    Views:
    587
    David Glienna
    Jan 21, 2009
Loading...

Share This Page