Alpha Video Training
Results 1 to 10 of 10

Thread: Export routine ideas needed

  1. #1
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,142

    Default Export routine ideas needed

    Here is my problem: My rather novice users need to be able to create an export file (excel or text) with fields they select. The problem with using the export genie is twofold: (1) There are around 200 fields whereas the ones they would ever need to select from is no more than 20, and (2) a good many of those 20 have somewhat obscure field names that these users will not understand without some help (examples: “S_addr,” “C_Addr,” “Alt_Addr” and “M_Addr”).

    So my thought was that I would put just those 20 fields on a form or in an xdialog with more descriptive titles for what the field really is, along with a checkbox for selecting the fields to be included in the export. Then the user would click a button that would run a script which would build and run a custom xbasic export script.

    I know I can create an xbasic script that will build another rather complex script and run it because I have done it (see my Automatic Printer Overrides in Learnalpha.com). But it can be a lot of work, so I am hoping someone else has done something along the lines of what I am trying to do. Of course a working sample would be fantastic but probably too much to hope for. Any thoughts, suggestions or cautions would be greatly appreciated.

    Ray Lyons

  2. #2
    "Certified" Alphaholic Scott Emerick's Avatar
    Real Name
    Scott Emerick
    Join Date
    Dec 2000
    Location
    Central Virginia/ North Carolina Border
    Posts
    2,154

    Default

    Ray,

    As you probably already know, there are many ways to tackle this using arrays and lists. Since the only purpose for this field list is to select the ones the user wants for the export a quick and dirty way could be to create a XDialog with a multi select list box or check box if you prefer. Manually enter the field names and include lets say 20 spaces(you would need to adjust this) then a delimiter and the actual field name. So your field list would look something like:

    Code:
    Address                                   ^S_addr
    Alternate Address                         ^Alt_Addr
    ... and so on
    When you set the width of your list box on your dialog make sure its less then the carrot or what ever delimiter you choose so it will not show on the list.

    Then you can just parse the selection(s) by using WORD().

    And here's your example:

    Code:
    'Create an XDialog dialog box with a check box - list box
    DIM  vxfld_list as C
    DIM  varC_result as C
    DELETE a_vxfld_list		
    DIM a_vxfld_list[2] as c
    dim temp_list as c 
    temp_list = <<%list%
    Address                                         ^S_addr
    Alternate Address                               ^Alt_Addr
    %list%
    
    a_vxfld_list.initialize(temp_list)
    ok_button_label = "&OK"
    cancel_button_label = "&Cancel"
    varC_result = ui_dlg_box("Export Fields",<<%dlg%
    {region}
    Select Fields to Export:| [.35,5vxfld_list^$$a_vxfld_list];
    {endregion};
    {line=1,0};
    {region}
    <*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
    {endregion};
    %dlg%)
    
    IF varC_result <> OK THEN
    	end
    END IF
    
    'Parse vxfld_list
    kount = w_count(vxfld_list,crlf())
    FOR i = 1 TO kount
    	vfld = word(word(vxfld_list,i,crlf()),2,"^")
    	'Do what ever you need to now that you have the actual field name
    NEXT i
    Hope this helps,

    Scott
    Last edited by Scott Emerick; 10-08-2005 at 05:35 PM.

  3. #3
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,308

    Default

    Ray, beware of permitting so many to be set that the length of the resulting query expression blows past the max expression length (which I think is 1024 chars).

    I've found that with a bit of training on the use of query by form operators my customers prefer using QBF.

    -- tom

  4. #4
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,142

    Default

    Scott,

    Sorry for taking so long to get back to this but I have been very busy. Although I like the idea of an xdialog, and I am reasonably sure examples of actual field data could be added for each field (which I did not think of in the original post), I think it would be a lot easier to add the examples using a form and some variables.

    The harder part would be generating the export script. Hard but do-able, I believe, if I ever get the time.

    Then there is another part I did not mention and is probably impossible, which would be to use my descriptive field names for the fields instead of the actual field names for the first row of the Excel file. I don’t think the A5 excel export function has any way to do this. It could probably be done using an ASCII export that would be imported into excel with macros, but that I have no interest in messing around that much with excel macros. Actually, I do not even want the user to have to use ms excel in the first place–just an excel file.

    Anyway, thanks for the xdialog example.

    Ray

  5. #5
    Member
    Real Name
    Blake Watson
    Join Date
    Jan 2003
    Posts
    961

    Default

    Quote Originally Posted by Raymond Lyons
    I don’t think the A5 excel export function has any way to do this. It could probably be done using an ASCII export that would be imported into excel with macros, but that I have no interest in messing around that much with excel macros. Actually, I do not even want the user to have to use ms excel in the first place–just an excel file.Ray
    I'm pretty sure Excel will handle a properly formatted ASCII file without requiring a macro. (It may or may not put up a wizard, depend on whether or not it can figure out what it needs on its own.) An ASCII file has the advantage of being usable by just about any program that imports data.

  6. #6
    Member
    Real Name
    Mark Liermann
    Join Date
    Jun 2000
    Location
    Wisconsin
    Posts
    141

    Default

    I have something setup, that might help you. It is a form that lets you check off the fields that you want exported to an excel spreadsheet. Based upon the selections, it exports what the user needs.

    I will try and get the code and form pasted as an attachment in a few days...If your interested?

  7. #7
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,142

    Default

    Blake, I am sure Excel will handle the ASCII import just fine (actually I assume it would, as I do not use Excel), but the last kicker I threw in using something other than the dbf field names as the first row in the excel table. That I think would require an excel macro and I don't think there is any way to do it on the A5 side.

    Ray

  8. #8
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,142

    Default

    Sparky,

    YES!!! That's exactly what I am looking for. I assume the form part is easy. I'll be very interested to see how you did the rest of it. My thought was to generate and run an export script (which I know how to do and which I have shared in Learnalpha.com), but I was just hoping to save myself some time that I do not have right now. Besides your solution may go a different and perhaps better route. In any case, I'm waiting!

    Ray

  9. #9
    Member
    Real Name
    Blake Watson
    Join Date
    Jan 2003
    Posts
    961

    Default

    Quote Originally Posted by Raymond Lyons
    Blake, I am sure Excel will handle the ASCII import just fine (actually I assume it would, as I do not use Excel), but the last kicker I threw in using something other than the dbf field names as the first row in the excel table. That I think would require an excel macro and I don't think there is any way to do it on the A5 side.Ray
    Maybe I'm missing something but wouldn't you just do this (this is paste-able into the interactive window):

    Code:
    tbl = table.open("xJobBase")
    list = tbl.field_name_get() 
    'you would use whatever alternate names you wanted here
    'perhaps by enumerating over columns in a browse
    cc = ""
    tab = chr(9)
    for i = 1 to w_count(list,crlf())
    #cc = cc + iif(cc="", "", tab) + word(list, i, crlf())
    #next
    cc = cc + crlf()
    tbl.fetch_first()
    while .not. tbl.fetch_eof()
    #cc2 = ""
    #for i = 1 to w_count(list,crlf())
    #cc2 = cc2 + iif(cc2="", "", tab) + tbl.field_get(word(list, i, crlf())).value_get()
    #next i
    #cc = cc + crlf() + cc2
    #tbl.fetch_next()
    #end while
    file.from_string("c:\dump.txt", cc)

  10. #10
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,142

    Default

    I can't seem to make your code work with my test table.

    There seems to be some problem with that line:

    #cc = cc + iif(cc="", ....and so on. Maybe other things as well.

    Ray

Similar Threads

  1. Logon ideas needed
    By Raymond Lyons in forum Alpha Five Version 6
    Replies: 14
    Last Post: 06-25-2005, 10:35 AM
  2. Advice/Help on Export needed.
    By PhilD in forum Alpha Five Version 5
    Replies: 4
    Last Post: 01-18-2005, 06:53 AM
  3. Quarter Number routine needed
    By Mike Vance in forum Alpha Five Version 6
    Replies: 5
    Last Post: 11-17-2004, 05:41 AM
  4. Rocket Scientist Needed For Excel Export
    By Tom Scholis in forum Alpha Five Version 4
    Replies: 8
    Last Post: 02-04-2002, 11:47 AM
  5. Ideas needed !
    By Gren Brown in forum Alpha Five Version 4
    Replies: 3
    Last Post: 07-03-2000, 05:14 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
  •