Results 1 to 5 of 5

Thread: Strange problem with Using OpenDataSource() to get data from Excel.

  1. #1
    Join Date
    Apr 2005
    Posts
    2

    Unhappy Strange problem with Using OpenDataSource() to get data from Excel.


    Hey guys. I got some problem here.

    I have an Excel spreadsheet. the data is something like :

    COL1 COL2 COL3
    DATA1 1 DETAIL1
    DATA2 2 DETAIL2
    DATA3 3 DETAIL3
    DATA4 4 DETAIL4
    DATA5 5 DETAIL5
    DATA6 6 DETAIL6
    DATA7 7 DETAIL7
    etc

    there are 1877 * 3 cells in total in the spreadsheet.
    but when I execute a query like:

    select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=Test.xls;User ID=Admin;Password=;Extended properties=Excel 8.0')...[Sheet1$]

    It returns 2132 * 5 units of data!!! And the "F4" and "F5" column just have NULL. so do row 1878 to 2132. I'm confused with that.

    I need your help~ 3X in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  3. #3
    Join Date
    Apr 2005
    Posts
    2
    Originally posted by MAK
    Zip the Excel sheet and upload it
    Thanks very much. I 've already solved this problem

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Do you like to share the problem and solution?

  5. #5
    Join Date
    Feb 2003
    Posts
    1,048
    The problem is that when you import Excel, it imports any rows and columns that you have used, even if the row or column is now empty.

    The options to correct it are to 1)delete columns and rows rather than simply clearing the contents or 2) se the print area to the columns and rows that are populated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •