Alpha Video Training
Results 1 to 13 of 13

Thread: DAO excel import

  1. #1
    "Certified" Alphaholic
    Real Name
    Ed Barley
    Join Date
    Mar 2002
    Location
    Southern California
    Posts
    1,056

    Default DAO excel import

    Hello All,

    Looks like an EXCEL day on the board.

    I have a successful DAO EXCEL import operation.

    A customer used to send their EXCEL information in one large .XLS, now they send the information in 3-6 smaller .XLS files. I would like to just import each smaller XLS file. Trying to keep prep work down on the XLS files.

    After the first import with the DAO, it locks the import XLS file. I would like to use the same name for the 3-6 import files, so I can use the same import operation.

    How can I break the link to the EXCEL file with ALPHA still running? Right now I have to shut down ALPHA and then restart ALPHA. So that I can overwright the import XLS file with the same name(all 6 files need to be named the same).

    Am I looking at this wrong?

    Here is the import code:

    HTML Code:
    dim connectionString as c 
    connectionString = "{A5API='Excel',A5Syntax='Excel',FileName='C:\alphaimport\hsimport.xls'}" 
    delete options 
    dim options as p 
    options.ConsolidateArguments= .t.
    options.AddTablesToDatabase= .f.
    options.ShowProgress= .t.
    options.AllowCancel= .t.
     
    delete a_import
    dim a_import[0] as p 
     
    a_import[].ObjectName = "Sheet1$"
    a_import[..].SQLSelectStatement = "SELECT PCode, [Roll Number], BWt, Width, Diameter, [Gross Wt], CUSTOMER FROM [Sheet1$]"
    a_import[..].SQLType = "Portable"
    a_import[..].Arguments = ""
    a_import[..].ImportType = "Append to existing table"
    a_import[..].AppendOption = "Append all"
    a_import[..].AppendToTableName = a5.get_path() + chr(92) + "hs_xls_imp"+".dbf"
    a_import[..].AppendFieldMap = <<%txt%
    Customer|Customer
    Pcode|Pcode
    Roll_number|Roll_number
    Bwt|Bwt
    Width|Width
    Diameter|Diameter
    Gross_wt|Gross_wt
    %txt%
     
    dim flagSilent as l
    delete args 
    dim args as sql::arguments
    flagSilent = .f.
    delete p 
    dim p as p 
    p = a5_AlphaDAO_Import(connectionString,a_import,options,flagSilent,args)
    'p has an .hasError and .errorText property
    'a_import[] has the following properties for each item: .countRecordsImported, .hasError, .errorText, .userCancelled
    Thanks in advance

    Ed

  2. #2
    Member
    Real Name
    Doug Page
    Join Date
    Jan 2002
    Location
    Vancouver, BC Canada
    Posts
    963

    Default Re: DAO excel import

    Ed, I've never done anything quite like this but I did notice in the Help:

    It is very important that you close your connections at your end of your scripts, or else, if your script repeatedly opens connections, you will run out of resources. To close an open connection, you use the connection object's . Close() method.
    This was under the topic "Using AlphaDAO with XBasic".

    I don't see anywhere that you have "released" your .xls file from Alpha which is why it is locking it up. Closing the connection should free it up for manipulation.

  3. #3
    "Certified" Alphaholic
    Real Name
    Ed Barley
    Join Date
    Mar 2002
    Location
    Southern California
    Posts
    1,056

    Default Re: DAO excel import

    Hi Doug,

    I am on it right now. Thanks for the pointer.

    Ed

  4. #4
    "Certified" Alphaholic
    Real Name
    Ed Barley
    Join Date
    Mar 2002
    Location
    Southern California
    Posts
    1,056

    Default Re: DAO excel import

    Still no success.

    I have gone over "Creating an object" and SQL::Connection::Close(), and have tried various methods trying to turn the original operation made by the genie into a script.

    In the interactive window I have tired this from "Creating an Object" in the help file:

    HTML Code:
    dim conn as SQL::connection
    conn.open("{A5API=Excel,FileName='C:\alphaimport\hsimport.xls'}")
    conn.close()
    ERROR: Argument is incorrect data type
    The above tested fine, till the conn.close().......The example in the help file was actually opening up a Access data base, but the "Conn.open()" above went successful, even with testing the link to the EXCEL file.
    When I added the "conn.close() it threw up the ERROR message.

    When I tried to modify the code from the genie, I kept getting the same ERROR: as above.

    The below script imports fine, but when I try and delete the import EXCEL file, it is still locked.

    HTML Code:
    dim connectionString as c 
    c 
    delete options 
    dim options as p 
    options.C .f.
    options.ShowProgress= .t.
    options.AllowCancel= .t.
     
    delete a_import
    dim a_import[0] as p 
     
    a_import[].ObjectName = "Sheet1$"
    a_import[..].SQLSelectStatement = "SELECT PCode, [Roll Number], BWt, Width, Diameter, [Gross Wt], CUSTOMER FROM [Sheet1$]"
    a_import[..].SQLType = "Portable"
    a_import[..].Arguments = ""
    a_import[..].ImportType = "Append to existing table"
    a_import[..].AppendOption = "Append all"
    a_import[..].AppendToTableName = a5.get_path() + chr(92) + "hs_xls_imp"+".dbf"
    a_import[..].AppendFieldMap = <<%txt%
    Customer|Customer
    Pcode|Pcode
    Roll_number|Roll_number
    Bwt|Bwt
    Width|Width
    Diameter|Diameter
    Gross_wt|Gross_wt
    %txt%
     
    dim flagSilent as l
    delete args 
    dim args as sql::arguments
    flagSilent = .f.
    delete p 
    dim p as p 
    p = a5_AlphaDAO_Import(connectionString,a_import,options,flagSilent,args)
    'p has an .hasError and .errorText property
    'a_import[] has the following properties for each item: .countRecordsImported, .hasError, .errorText, .userCancelled
    end 
    connectionstring.close()
    Everything works great, except for freeing up the EXCEL file, to import the next one. Still have to close/open ALPHA to free the EXCEL file.

    Stumped

    Ed

  5. #5
    VAR
    Real Name
    Bill Parker
    Join Date
    Apr 2000
    Location
    Dallas, TX
    Posts
    1,714

    Default Re: DAO excel import

    Ed,

    Did you ever get this working to release the file? I have seen that I will have that issue.

    But first, I can't get the xbasic generated by the genie to run. Get an error
    Code:
    variable "args" not found.
    My code is similar to yours
    Code:
    dim connectionString as c 
    connectionString = "{A5API='Excel',A5Syntax='Excel',FileName=" +quote(vFile) +"}" 
    
    delete options 
    dim options as p 
    options.ConsolidateArguments= .t.
    options.AddTablesToDatabase= .f.
    options.ShowProgress= .t.
    options.AllowCancel= .t.
    
    
    delete a_import
    dim a_import[0] as p 
    
    
    a_import[].ObjectName = "FinesCost$"
    'a_import[].ObjectName = "Sheet1$"
    a_import[..].SQLSelectStatement = "SELECT * from [FinesCost$]"
    'a_import[..].SQLSelectStatement = "SELECT * from [Sheet1$]"
    a_import[..].SQLType = "Portable"
    a_import[..].Arguments = ""
    a_import[..].ImportType = "Create table"
    a_import[..].LocalTableName = a5.get_path() + chr(92) + "import_temp"+".dbf"
    a_import[..].LocalTableFieldDef = <<%txt%
    <SizeToFit=.T.>
    <ConvertTimeToDate=.T.>
    <DefineFieldOverrides=.F.>
    <OverrideDefinitions="">
    %txt%
    a_import[..].OverwriteOption = "Overwrite without prompting"
    
    dim flagSilent as l
    flagSilent = .f.
    delete p 
    dim p as p 
    p = a5_AlphaDAO_Import(connectionString,a_import,options,flagSilent,args)
    Bill.

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

    Default Re: DAO excel import

    Code:
    p = a5_AlphaDAO_Import(connectionString,a_import,options,flagSilent)
    Since you don't have arguments, ie args, take it off the last argument to the function.

    Did the genie gen that code or did you steal it and modify it???
    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.

  7. #7
    "Certified" Alphaholic
    Real Name
    Ed Barley
    Join Date
    Mar 2002
    Location
    Southern California
    Posts
    1,056

    Default Re: DAO excel import

    Hey Bill & Al,

    No. I have not found a way to disconnect as of yet, without the restart.

    I will give Al's suggestion a shot with the last argument. It sure would be nice for it to just disconnect from the excel file after the import.

    The code I posted is from the DAO genie.


    Ed

  8. #8
    VAR
    Real Name
    Bill Parker
    Join Date
    Apr 2000
    Location
    Dallas, TX
    Posts
    1,714

    Default Re: DAO excel import

    Thanks guys,

    I could not get back to post before Al did, but found that the xbasic from design mode in the import genie is correct. The xbasic obtained from the object on the control panel is missing these lines
    Code:
    delete args 
    dim args as sql::arguments
    adding those solves the problem. I'll report to Alpha.

    NOW my next issue has to do with Excel sheet names. My previous method of importing was with OLE to Excel - when Excel could save as DBF. If I did not know the sheet name I could always use a numeric sheet number to reference the sheet.

    With the ADO import, is there a way to reference a numeric sheet number, regardless of sheet name? FinesCost is sheet name, and imports correctly. I took a stab at Sheet1 (alpha, not numeric) which does not work.
    Code:
    a_import[].ObjectName = "FinesCost$"
    'a_import[].ObjectName = "Sheet1$"
    a_import[..].SQLSelectStatement = "SELECT * from [FinesCost$]"
    'a_import[..].SQLSelectStatement = "SELECT * from [Sheet1$]"
    Any thoughts on how to use a numeric sheet number? There is no autocomplete on "a_import[]." so difficult to guess.

    Bill.

  9. #9
    VAR
    Real Name
    Bill Parker
    Join Date
    Apr 2000
    Location
    Dallas, TX
    Posts
    1,714

    Default Re: DAO excel import

    One step further.

    Code:
    dim conn as SQL::Connection
    ?conn.open("{A5API='Excel',A5Syntax='Excel',FileName='D:\Book1.xls'}" )
    = .T.
    ?conn.ListTables()
    = abc$
    def$
    Sheet3$
    So I can preprocess to get the sheet names. Problem is that they are not in numeric order. "def$" is the first sheet and "abc$" is the second sheet. So I still don't know how to get sheet 1.

    Interestingly, ?conn does not show the ListTables() method, so there may be an obvious method that I am not seeing.

    Bill.

  10. #10
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: DAO excel import

    Hi Bill,
    Interestingly, ?conn does not show the ListTables() method, so there may be an obvious method that I am not seeing.
    If you would have placed a period after ?conn you would have seen all the possible functions associated with conn.....ListTables() is among them.

    I could not find anything regarding sheet numbers--sorry. Maybe Stan Mathews has some insight and will respond as I know he works with Alpha/Excel more than most here.
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  11. #11
    VAR
    Real Name
    Bill Parker
    Join Date
    Apr 2000
    Location
    Dallas, TX
    Posts
    1,714

    Default Re: DAO excel import

    I get
    Code:
    dim conn as sql::connection
    ?conn.Open("{A5API='Excel',A5Syntax='Excel',FileName='D:\FinesCost012309.xls'}")
    = .T.
    
    ?conn.
    ERROR: Property not found
    conn. subelement not found.
    
    ?conn
    = V AddEventScript(ScriptSource as C [, Name as C])  'Add a connection event script with functions to handle one or more of the following events: 
    
        UpdateBegin(Connection as SQL::Connection, RowsExpected as N, BYREF ProgressInterval as N, Cancel BYREF as L)
        UpdateProgress(Connection as SQL::Connection, RowsUpdated as N, RowsExpected as N, Cancel BYREF as L)
        UpdateEnd(Connection as SQL::Connection, RowsUpdated as N)
    L AddUser(UserName as C, Password as C)  'Create a new user in the currently connected database or server.
    ...
    Bill.

  12. #12
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: DAO excel import

    Bill,

    I simply used the interactive editor for convenience...the question mark makes no difference and I did NOT run ?conn.

    Once the period is placed, Alpha gives you the function bubblehelp is all--that is what I was referring to. Take a look at the screenshot...


    Attachment 19936
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  13. #13
    VAR
    Real Name
    Bill Parker
    Join Date
    Apr 2000
    Location
    Dallas, TX
    Posts
    1,714

    Default Re: DAO excel import

    Yes Mike, I had later looked through the autocomplete list. It does show properties as well as methods. But did not see anything to help. Think I will add a "feature request".

    Bill.

Similar Threads

  1. How do I import from Excel?
    By Barb Deleon in forum Alpha Five Version 8
    Replies: 16
    Last Post: 10-17-2008, 01:04 PM
  2. Import excel then export to excel
    By popellis in forum Alpha Five Version 8
    Replies: 4
    Last Post: 04-03-2008, 11:13 PM
  3. Import from Excel
    By Lanny Tonning in forum Alpha Five Version 6
    Replies: 6
    Last Post: 09-30-2005, 05:38 PM
  4. Import Excel DB
    By Michael Vermouth in forum Alpha Five Version 5
    Replies: 2
    Last Post: 12-19-2003, 01:29 PM
  5. Excel import
    By Ed Barley in forum Alpha Five Version 5
    Replies: 4
    Last Post: 02-10-2003, 11:57 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
  •