Write Structure to File.

[crimmelcp]

Re: Write Structure to File.

Keith and others:
if you copy the attached code to a button on a form
It will Pop up a list of tables in the database
check to see if the directory "structures" exists
If not it will create it
Write the structure of the selected table to a text file.

I put comments in the code to explain my directory structure.
yours may be different so you might have to change the lines
that get the paths.
Charlie Crimmel

[Tbaker]

Re: Write Structure to File.

This is a revision to the original script/function

It now presents you with a dialog box. The Dialog box will show and alphabetized list of all the tables in the current database. You can select "ALL" which will send the table strutures to text file in the same folder as the database, or you can select each individual table you would want to send to a text file.

FWIW

Tom Baker

[Keith Hubert]

Re: Write Structure to File.

Hi Tom,

This is very neat.

What is the extra line needed to show the decimal value?

[Tbaker]

Re: Write Structure to File.

Keith

Here is the revised script

This is the one that should have gone out

It has the decimal in this one.

Sorry

Tom Baker

[Davidk]

Re: Write Structure to File.

Here's my take on it...

I used the original f_TablesFieldsIndexes function (now called f_TablesFieldsIndexesDoc) and added Documentation. You'll need to create a table (didnt' want to do this automatically - but it can be added) named tbltabledefs with a structure of

fldname
fldtype
fldwidth
flddecimal
fldrules

The function will write out the currently selected table structure to tbltabledefs. If there's data in tbltabledefs then it will ask to clear it.

After the selected table's structure is written out to tbltabledefs you can create a report.

I'm changing it a bit further to add a key to handle the structure for multiple tables.

[Davidk]

Re: Write Structure to File.

And I'll add my changes to Charlies code too...

[CALocklin]

Re: Write Structure to File.

I added some features to Tom's Dump_tbl_structure function.

The main changes are:
- Ability to select from a list of tables if none is given. (Can double click the table name.)
- Ability to output with tabs as separators so output can be cut and pasted into another table in design mode.
- Added decimal values.
- Automatically open the result file in whatever application is used to open .txt files. (I recommend WordPad or Notepad but quite often Word sets itself up as the default.)

'Date Created: 11-Sep-2010 03:48:00 PM
'Last Updated: 12-Sep-2010 12:44:08 PM
'Created By  : Tom Baker
'Updated By  : Cal Locklin
 
FUNCTION Dump_tbl_structure as L ( Tablename="" as C, Format_for_pasting=.F. as L )
 
    DIM base_name as C
    DIM dlg_text as C
    DIM format_for_pasting as L
    DIM hdr_txt as C
    DIM msg as C
    DIM ok_pressed as L
    DIM padout as N
    DIM struct as C
    DIM tbl as P
    DIM tbl as P
    DIM tlist as C
    DIM txt_file as C
 
    IF Tablename = ""
        'Show list of ALL tables in the .adb folder - in the control panel or not.
        'tlist = filefind.get( a5.Get_Path() + "\*.dbf", FILE_FIND_NOT_DIRECTORY, "n" )
        'Show list of all tables in the control panel:
        'Since they are in the Control Panel, table.open() works even if they are in a different folder.
        tlist = A5.Table_Enum()
        ok_pressed = .F.
        dlg_text = <<%dlg%
{ysize=.3}{units=F}{font=Arial,9,n}{sp};
(Format_for_pasting){font=Arial,9,b}Format for pasting into design mode.{font=Arial,9,n}{sp}{sp}<%I=$$generic.help;B=N%Help>;
{stretch=height,width}[.80,15tablename^#tlist!tch_*];
{stickto=bottom,center}
{sp};
{justify=center}<*15OK> <15Cancel>
{comment Putting "Format_for_pasting" in a second time and hiding it was the only way I could get the pasting to work when using double click}
{condition=1=2}(Format_for_pasting)
%dlg%
 
        DIM dlg_result as C
        dlg_result = ui_dlg_box( "SELECT TABLE", dlg_text, <<%code%
IF left( a_dlg_button, 4 ) = "tch_"
    IF a_dlg_button = "tch_dblclick"
        ok_pressed = .T.
    ELSE
        a_dlg_button = ""
    END IF
ELSEIF a_dlg_button = "Help"
    msg = "This creates an output format that may not look as good in the printout "
    msg = msg + "but any field(s) can be cut and pasted into the table design mode." + crlf(2)
    msg = msg + "This can be handy for copying standard fields such as create and "
    msg = msg + "change dates or address fields to other tables."
    ui_msg_box( "INSTRUCTIONS", msg, ui_information_symbol )
    a_dlg_button = ""
ELSEIF a_dlg_button = "OK"
    IF tablename = ""
        msg = "Please select a table or press 'Cancel' to quit."
        ui_msg_box( "SELECTION REQUIRED", msg, ui_attention_symbol )
        a_dlg_button = ""
    ELSE
        ok_pressed = .T.
    END IF
END IF
%code% )
 
       IF .not. ok_pressed
            END'
        END IF
        base_name = Tablename
 
    END IF
 
    'If Tablename includes the path (JIC), get the "base_name" for display.
    base_name = file.filename_parse( Tablename, "N" )
 
    hdr_txt = upper( base_name ) + " -> Table Structure" + crlf() + chr_multi( 45, 45 ) + crlf(2)
 
    txt_file = base_name + "_tbl_structure.txt"
 
    tbl = table.open( Tablename )
    struct = tbl.Field_Info_Dump( "n,t,W,D" )
    tbl.close()
 
    IF .not. format_for_pasting
        DIM foo as C
        DIM new_struct as C
        DIM n1 as C
        DIM n2 as C
        DIM n3 as C
        DIM n4 as C
        new_struct = ""    'FYI
        FOR EACH foo in struct
            n1 = word( foo.value, 1, "," )
            padout = 20 - len( n1 )
            n1 = n1 + space( padout )
            n2 = word( foo.value, 2, "," ) + space(2)
            n3 = word( foo.value, 3, "," )
            n4 = space(4) + word( foo.value, 4, "," )
            SELECT                        'this allow for space for the length of the type field from 1 to 3 spaces
                CASE len( n3 ) = 1
                    n3 = space(2) + n3
                CASE len( n3 ) = 2
                    n3 = space(1) + n3
                CASE ELSE
                    n3 = n3
            END SELECT
            new_struct = new_struct + n1 + n2 + n3 + n4 + crlf()
        NEXT
        struct = new_struct
    ELSE    'use tabs so field info can be cut and pasted to the table design mode.
        struct = stritran( struct, ",", chr(9) )
    END IF
 
    save_to_file( hdr_txt + struct, txt_file )
    sys_open( txt_file )
 
END FUNCTION
 
dump_tbl_structure()

NOTES:
- The dump_tbl_structure() call at the end just allows you to test it in the code editor.
- The second (Format_for_pasting) that is at the end of the "dlg_text" and hidden using "condition=1=2" (which is usually false:D) is there only because the value of Format_for_pasting always ended up false, even though the box remained checked, whenever a double click was used to select the table. After much trial and error, the only solution I could find was to add a second (Format_for_pasting). (Sometimes ya just have to do what works!:()

[dchiass]

Re: Write Structure to File.

Cal is there anyway that the list of DBFs are sorted alphabetically? Is there anyway to allow someone to dump to txt for all vs each one? This is a beauty!!!!! thanks.

[CALocklin]

Re: Write Structure to File.

Holy cow! I don't even remember doing that. Of course, it was 7 years ago.

If you want the list sorted, just add this after the line "tlist = A5.Table_Enum()":

tlist = sortsubstr( tlist )

or ... change the line to:

tlist = sortsubstr( A5.Table_Enum() )

Hmmm, I didn't check this so I don't recall if the A5.Table_Enum() function returns the whole path or not. I don't think it does unless the table is not in the same folder as the application (.adb) file but I'm not sure.

As for dumping all to a text file, it could be done but would involve more time to add a loop and the appropriate prompts, checks, and logic to handle it all.

[dchiass]

Re: Write Structure to File.

thanks for the quick reply.....tried the sort and got this error...

sort error.jpg

[dchiass]

Re: Write Structure to File.

should have mentioned the DBF's are in same folder as the ADB

[Al Buchholz]

Re: Write Structure to File.

tlist = sortsubstr( A5.Table_Enum() ,crlf())

[dchiass]

Re: Write Structure to File.

That did the trick....thanks. Al & Cal, both first rate!!!! You make this one of the best MB on the net.

[CALocklin]

Re: Write Structure to File.

Thanks for showing that correction Al. I swear there's a missing spot somewhere in my memory. I forget to add that delimiter about 80% of the time when I use that sortsubstr() function - and I've used it quite a bit. I guess it just seems like something that should have a default.

[Al Buchholz]

Re: Write Structure to File.

It's the same affliction I have - CRS - Can't Remember Stuff

Thank goodness it's in the documentation.