Reading In Excel Sheet Using ADO


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Reading In Excel Sheet Using ADO

  1. #1
    James Shingleton Guest

    Reading In Excel Sheet Using ADO


    I'm currently using an ADO connection to an Excel sheet to read in the entire
    sheet (a large sheet) and put it into an internal array so I can perform
    operations on it.
    The program is currently reading in most of the cells but for some reason
    seems to be randomly leaving some blank in the internal array when there
    is actually something in that Excel cell. If I copy a cell which has been
    read succesfully into the place of one which has not been read in successfully,
    it will then be copied across into the array fine. As far as I can see there
    is no difference in the formatting of the cells that can and can't be read
    in.
    Is there something which I am missing out here and does anyone have any suggestions
    on how to solve this?
    Thanks in advance.
    James Shingleton

  2. #2
    James Shingleton Guest

    Re: Reading In Excel Sheet Using ADO


    Hi,
    Included below is the code I am using to read in from the sheet and put it
    into the array. I've found that it seems to be reading in numbers in certain
    columns but not in others. If I put in the numeric values as '20, '21 etc.
    will it stop the problem or am I missing the point entirely?

    ' Open the Connection
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & File & _
    ";Extended Properties=""Excel 8.0;HDR=No"""
    rs.Open "Select * from [Circuitry$]", cnn, adOpenStatic, adLockReadOnly
    'Copy recordset to an array
    recArray = rs.GetRows
    'Transpose the array using a function as it is currently wrongly orientated
    tempArray = TransposeDim(recArray)

    Many thanks
    James Shingleton




    Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
    >On 22 Aug 2002 06:36:33 -0700, "James Shingleton" <vb.@127.0.0.1> wrote:
    >
    >
    > I'm currently using an ADO connection to an Excel sheet to read in the

    entire
    > sheet (a large sheet) and put it into an internal array so I can perform
    > operations on it.
    > The program is currently reading in most of the cells but for some reason
    > seems to be randomly leaving some blank in the internal array when there
    > is actually something in that Excel cell. If I copy a cell which has been
    > read succesfully into the place of one which has not been read in successfully,
    > it will then be copied across into the array fine. As far as I can see

    there
    > is no difference in the formatting of the cells that can and can't be

    read
    > in.
    > Is there something which I am missing out here and does anyone have any

    suggestions
    > on how to solve this?
    >
    >How are you populating the array? You might want to post some code so we

    can take a
    >look.
    >
    >
    >Paul ~~~ pclement@ameritech.net
    >Microsoft MVP (Visual Basic)



  3. #3
    Paul Clement Guest

    Re: Reading In Excel Sheet Using ADO

    On 22 Aug 2002 06:36:33 -0700, "James Shingleton" <vb.@127.0.0.1> wrote:


    I'm currently using an ADO connection to an Excel sheet to read in the entire
    sheet (a large sheet) and put it into an internal array so I can perform
    operations on it.
    The program is currently reading in most of the cells but for some reason
    seems to be randomly leaving some blank in the internal array when there
    is actually something in that Excel cell. If I copy a cell which has been
    read succesfully into the place of one which has not been read in successfully,
    it will then be copied across into the array fine. As far as I can see there
    is no difference in the formatting of the cells that can and can't be read
    in.
    Is there something which I am missing out here and does anyone have any suggestions
    on how to solve this?

    How are you populating the array? You might want to post some code so we can take a look.


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  4. #4
    Sue Harsevoort Guest

    Re: Reading In Excel Sheet Using ADO

    Are you checking the recArray before you transpose it? Is the data missing
    already then? Have you checked the recordset?

    Sue

    "James Shingleton" <vb.@127.0.0.1> wrote in message
    news:3d6500ce$1@10.1.10.29...
    >
    > Hi,
    > Included below is the code I am using to read in from the sheet and put it
    > into the array. I've found that it seems to be reading in numbers in

    certain
    > columns but not in others. If I put in the numeric values as '20, '21 etc.
    > will it stop the problem or am I missing the point entirely?
    >
    > ' Open the Connection
    > cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=" & File & _
    > ";Extended Properties=""Excel 8.0;HDR=No"""
    > rs.Open "Select * from [Circuitry$]", cnn, adOpenStatic,

    adLockReadOnly
    > 'Copy recordset to an array
    > recArray = rs.GetRows
    > 'Transpose the array using a function as it is currently wrongly

    orientated
    > tempArray = TransposeDim(recArray)
    >
    > Many thanks
    > James Shingleton
    >
    >
    >
    >
    > Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
    > >On 22 Aug 2002 06:36:33 -0700, "James Shingleton" <vb.@127.0.0.1> wrote:
    > >
    > >
    > > I'm currently using an ADO connection to an Excel sheet to read in the

    > entire
    > > sheet (a large sheet) and put it into an internal array so I can

    perform
    > > operations on it.
    > > The program is currently reading in most of the cells but for some

    reason
    > > seems to be randomly leaving some blank in the internal array when

    there
    > > is actually something in that Excel cell. If I copy a cell which has

    been
    > > read succesfully into the place of one which has not been read in

    successfully,
    > > it will then be copied across into the array fine. As far as I can see

    > there
    > > is no difference in the formatting of the cells that can and can't be

    > read
    > > in.
    > > Is there something which I am missing out here and does anyone have any

    > suggestions
    > > on how to solve this?
    > >
    > >How are you populating the array? You might want to post some code so we

    > can take a
    > >look.
    > >
    > >
    > >Paul ~~~ pclement@ameritech.net
    > >Microsoft MVP (Visual Basic)

    >




  5. #5
    Chris Hylton Guest

    Re: Reading In Excel Sheet Using ADO


    Add the following to your extended properties in the connection string and
    it should fix the problem. ADO has a problem with Excel when the datatypes
    within a column are 'mixed', ADO reads the first cell, determines the datatype
    and then if subsequent rows have a different datatype in the same column,
    ADO gets confused...the following extended property causes ADO to read everything
    in as text/string data.

    ";IMEX=1"

    I recently had the same problem working on a project for a guy who didn't
    end up paying me (I think he found me through this forum)...I guess someone
    should benefit from what I found...hope you aren't working for the same guy
    :-)

    Good luck,
    Chris

    "James Shingleton" <vb.@127.0.0.1> wrote:
    >
    >Hi,
    >Included below is the code I am using to read in from the sheet and put

    it
    >into the array. I've found that it seems to be reading in numbers in certain
    >columns but not in others. If I put in the numeric values as '20, '21 etc.
    >will it stop the problem or am I missing the point entirely?
    >
    >' Open the Connection
    > cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=" & File & _
    > ";Extended Properties=""Excel 8.0;HDR=No"""
    > rs.Open "Select * from [Circuitry$]", cnn, adOpenStatic, adLockReadOnly
    >'Copy recordset to an array
    > recArray = rs.GetRows
    >'Transpose the array using a function as it is currently wrongly orientated
    > tempArray = TransposeDim(recArray)
    >
    >Many thanks
    >James Shingleton
    >
    >
    >
    >
    >Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
    >>On 22 Aug 2002 06:36:33 -0700, "James Shingleton" <vb.@127.0.0.1> wrote:
    >>
    >>
    >> I'm currently using an ADO connection to an Excel sheet to read in the

    >entire
    >> sheet (a large sheet) and put it into an internal array so I can perform
    >> operations on it.
    >> The program is currently reading in most of the cells but for some reason
    >> seems to be randomly leaving some blank in the internal array when there
    >> is actually something in that Excel cell. If I copy a cell which has

    been
    >> read succesfully into the place of one which has not been read in successfully,
    >> it will then be copied across into the array fine. As far as I can see

    >there
    >> is no difference in the formatting of the cells that can and can't be

    >read
    >> in.
    >> Is there something which I am missing out here and does anyone have any

    >suggestions
    >> on how to solve this?
    >>
    >>How are you populating the array? You might want to post some code so we

    >can take a
    >>look.
    >>
    >>
    >>Paul ~~~ pclement@ameritech.net
    >>Microsoft MVP (Visual Basic)

    >



  6. #6
    James Shingleton Guest

    Re: Reading In Excel Sheet Using ADO


    Chris,
    Many thanks for the tip. It has solved my problem.
    Pretty sure I'm not working for the same guy.
    Once again many thanks.
    James




    "Chris Hylton" <cchylton@hotmail.com> wrote:
    >
    >Add the following to your extended properties in the connection string and
    >it should fix the problem. ADO has a problem with Excel when the datatypes
    >within a column are 'mixed', ADO reads the first cell, determines the datatype
    >and then if subsequent rows have a different datatype in the same column,
    >ADO gets confused...the following extended property causes ADO to read everything
    >in as text/string data.
    >
    >";IMEX=1"
    >
    >I recently had the same problem working on a project for a guy who didn't
    >end up paying me (I think he found me through this forum)...I guess someone
    >should benefit from what I found...hope you aren't working for the same

    guy
    >:-)
    >
    >Good luck,
    >Chris
    >
    >"James Shingleton" <vb.@127.0.0.1> wrote:
    >>
    >>Hi,
    >>Included below is the code I am using to read in from the sheet and put

    >it
    >>into the array. I've found that it seems to be reading in numbers in certain
    >>columns but not in others. If I put in the numeric values as '20, '21 etc.
    >>will it stop the problem or am I missing the point entirely?
    >>
    >>' Open the Connection
    >> cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    >> "Data Source=" & File & _
    >> ";Extended Properties=""Excel 8.0;HDR=No"""
    >> rs.Open "Select * from [Circuitry$]", cnn, adOpenStatic, adLockReadOnly
    >>'Copy recordset to an array
    >> recArray = rs.GetRows
    >>'Transpose the array using a function as it is currently wrongly orientated
    >> tempArray = TransposeDim(recArray)
    >>
    >>Many thanks
    >>James Shingleton
    >>
    >>
    >>
    >>
    >>Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
    >>>On 22 Aug 2002 06:36:33 -0700, "James Shingleton" <vb.@127.0.0.1> wrote:
    >>>
    >>>
    >>> I'm currently using an ADO connection to an Excel sheet to read in the

    >>entire
    >>> sheet (a large sheet) and put it into an internal array so I can perform
    >>> operations on it.
    >>> The program is currently reading in most of the cells but for some reason
    >>> seems to be randomly leaving some blank in the internal array when there
    >>> is actually something in that Excel cell. If I copy a cell which has

    >been
    >>> read succesfully into the place of one which has not been read in successfully,
    >>> it will then be copied across into the array fine. As far as I can see

    >>there
    >>> is no difference in the formatting of the cells that can and can't be

    >>read
    >>> in.
    >>> Is there something which I am missing out here and does anyone have

    any
    >>suggestions
    >>> on how to solve this?
    >>>
    >>>How are you populating the array? You might want to post some code so

    we
    >>can take a
    >>>look.
    >>>
    >>>
    >>>Paul ~~~ pclement@ameritech.net
    >>>Microsoft MVP (Visual Basic)

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center