New call-to-action
Results 1 to 13 of 13

Thread: Cannot read Excel file

  1. #1
    Member
    Real Name
    John Berry
    Join Date
    Nov 2007
    Location
    Maryland, USA
    Posts
    660

    Default Cannot read Excel file

    I am trying to use a grid to read an Excel file. I seem to be able to link to it through AlphaDAO but when I click on the Fields there are no fields listed. Has this been removed? I remember I was able to read and change Excel data in my V10.5 application.

    -John

  2. #2
    Member
    Real Name
    John Berry
    Join Date
    Nov 2007
    Location
    Maryland, USA
    Posts
    660

    Default Re: Cannot read Excel file

    Actually it doesn't work in 10.5 either. I went back to version 9 and it works fine. Doing some more searching I found this in some upgrade notes from Alpha:

    Version 10.5* - Build 3616-3623* Nov- 11-2010
    AlphaDAO - Excel - TableInfo - Getting a tableinfo on a table in an Excel file was broken by a change in a recent update. This is now fixed. The bug would have affected importing from Excel using AlphaDAO.

    So it looks like it used to be broken, then was fixed, but now it appears to be broken again.

    Would someone be kind enough to link to an Excel file and see if you get the list of fields? I appear to be able to link to it, when I click "Test Connection" it says "Succeeded" but when I go to list the fields there is nothing there. See pics. below.

    Thanks

    -John
    Attached Images Attached Images

  3. #3
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,662

    Default Re: Cannot read Excel file

    John

    It would be better if you attached an excel file that doesn't work for you.

    Keep it small and simple.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  4. #4
    "Certified" Alphaholic Clipper87's Avatar
    Real Name
    Frank
    Join Date
    Dec 2008
    Location
    Antwerp, Belgium
    Posts
    1,891

    Default Re: Cannot read Excel file

    John,

    I learn so much by just reading this board! Never even tried to use an Excel file as a table: whaaw! That said: I tested with some file & it works for me but when I create a blank file & just copy & paste an area some stuff from the first then I can't get it to work. In the sheet that works I notice that if I select the system table I don't get anything either but if I select some other table it works (whenever there are no empty brackets at the end of the table/view name) There must be some way to indicate that the data in your excel sheet is a table somehow. Irecognize some of the "tables" in the screenshot below as lookups from a deleted worksheet in that file. Don't know, not an excel/odbc guru and no intention to become one

    exc.png
    Frank

    Tell me and I'll forget; show me and I may remember; involve me and I'll understand

  5. #5
    Member
    Real Name
    John Berry
    Join Date
    Nov 2007
    Location
    Maryland, USA
    Posts
    660

    Default Re: Cannot read Excel file

    Hi Al. I will do that but it happens on all my Excel files. I will attach one that I've been trying the most.
    Attached Files Attached Files

  6. #6
    Member
    Real Name
    John Berry
    Join Date
    Nov 2007
    Location
    Maryland, USA
    Posts
    660

    Default Re: Cannot read Excel file

    Hi Frank, thanks for trying this out. The files I have been attempting to use have been created elsewhere and passed along to me and there is usually no more than one sheet and like you mention, that sheet keeps coming up in A5 with empty brackets and hence, no data. It's strange that it works in version 9 but not 10.5 or 11.

  7. #7
    Member
    Real Name
    John Berry
    Join Date
    Nov 2007
    Location
    Maryland, USA
    Posts
    660

    Default Re: Cannot read Excel file

    Update: It appears you have to use a SQL Select Statement. There is an article here http://nebula-rnd.com/blog/tech/2007/10/sql-with-excel01.html that explains it a little more. I never had to do this in V9 and I hope it's a bug because I don't want to have to do it each time but at least it seems to work.

    So, the Excel sheet is called IEP_504_qry and in order to use it I need to create a SQL Select Statement of SELECT * FROM [IEP_504_qry$]

    Bug?

    -John

  8. #8
    "Certified" Alphaholic Clipper87's Avatar
    Real Name
    Frank
    Join Date
    Dec 2008
    Location
    Antwerp, Belgium
    Posts
    1,891

    Default Re: Cannot read Excel file

    So why then does it work with my file ? (attached)
    Attached Files Attached Files
    Frank

    Tell me and I'll forget; show me and I may remember; involve me and I'll understand

  9. #9
    Member
    Real Name
    John Berry
    Join Date
    Nov 2007
    Location
    Maryland, USA
    Posts
    660

    Default Re: Cannot read Excel file

    Hmmm... I don't know. When I look at your XL file I see only one worksheet called Test but when I link to it via AlphaDAO I see several "tables" there also. Those other tables I can get to and put fields onto my grid, but the one I can't get to work in your XL is the one with the same name as the worksheet TEST. Did you try the XL file I posted?

    Thanks for helping with this.
    Attached Images Attached Images

  10. #10
    "Certified" Alphaholic Clipper87's Avatar
    Real Name
    Frank
    Join Date
    Dec 2008
    Location
    Antwerp, Belgium
    Posts
    1,891

    Default Re: Cannot read Excel file

    Found it: You need to name a range.

    Of the various objects available in a relational database (tables,views, stored procedures, and so forth), an Excel data source exposes only table equivalents, consisting of the worksheets and the named ranges defined in the specified workbook. Named ranges are treated as "Tables" and worksheets are treated as "System Tables,"

    Also see http://us.generation-nt.com/excel-ta...133133751.html
    Frank

    Tell me and I'll forget; show me and I may remember; involve me and I'll understand

  11. #11
    Member
    Real Name
    John Berry
    Join Date
    Nov 2007
    Location
    Maryland, USA
    Posts
    660

    Default Re: Cannot read Excel file

    Hi Frank,

    Yep, see my post #7. The way around it is to create a SQL Select Statement and use select * from [SheetName$]. Didn't need to do that in V9 but appears it's needed in V11.

    Thaks for checking it out with me, keeps my sanity intact :)

  12. #12
    "Certified" Alphaholic Clipper87's Avatar
    Real Name
    Frank
    Join Date
    Dec 2008
    Location
    Antwerp, Belgium
    Posts
    1,891

    Default Re: Cannot read Excel file

    John,

    I did not use a SQL statement just the connection string. In the excel file I selected some area, right clicked on it & selected "define name" and named it.
    TEST is not exposed as a table in the sample file because there is no range linked to it? Typical ODBC/Excel stuff I guess
    Frank

    Tell me and I'll forget; show me and I may remember; involve me and I'll understand

  13. #13
    Member
    Real Name
    John Berry
    Join Date
    Nov 2007
    Location
    Maryland, USA
    Posts
    660

    Default Re: Cannot read Excel file

    Jerry at Alpha has confirmed this is a bug and a fix will be in the next release.

    -John

Similar Threads

  1. read and parse an xml file
    By crimmelcp in forum Alpha Five Version 9 - Desktop Applications
    Replies: 8
    Last Post: 02-24-2010, 03:58 PM
  2. Can't read javascript file.
    By rlgiordano in forum Web Application Server v6
    Replies: 4
    Last Post: 03-17-2005, 09:03 AM
  3. Saving Report as text file or excel file
    By Michael Hadjimichael in forum Alpha Five Version 5
    Replies: 1
    Last Post: 06-02-2004, 04:46 AM
  4. Read text file into an array
    By Doug Chanley in forum Code Archive
    Replies: 3
    Last Post: 04-21-2003, 03:55 PM
  5. Read past end of file
    By forskare in forum Alpha Five Version 4
    Replies: 5
    Last Post: 04-09-2000, 04:39 AM

Bookmarks

Posting Permissions

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