Export a Set from WAS to a CSV file

[[email protected]]

Hello,

No luck finding this in help/forum/textbook and am hoping someone does know how to do this and can either direct me to a working example or give me a working snippet of code to learn from.

The goal is to have a button on the web page execute code that reads the records in a SET and then exports those records in the form of a CSV file.

I can do this like a champ with a table but would really like to do this with a SET.

I appreciate any help on this.

[Steve Workings]

Re: Export a Set from WAS to a CSV file

Create the export on the desktop. While in the export design, click the blue XB button on the toolbar to get the Xbasic. Put that into your A5W page and make necessary adjustments.

[[email protected]]

Re: Export a Set from WAS to a CSV file

Create the export on the desktop. While in the export design, click the blue XB button on the toolbar to get the Xbasic. Put that into your A5W page and make necessary adjustments.

Thanks for responding Steve.

Sorry that I didn't indicate I'd tried that suggestion from my search of the forum. I tried doing just that with my SET and it fails.

Have you, has anyone, ever exported a SET (*not* a single table) to a CSV file? Can it even be done?

/waves at Alpha people (this info is not in the help file...)

[Al Buchholz]

Re: Export a Set from WAS to a CSV file

The export operation works on a table. For some exports, I copy the set to a table and then export from that table.

Other times I export each table from the set due to the one to many relationship which can cause some duplication of the parent tables when there are multiple children.

You can do all of this on the desktop and then move the code into your web page.

[StephenP]

Re: Export a Set from WAS to a CSV file

I finally got this to work. I used Al's suggestion, and copied the set to a table (only including the fields I needed, simplified in the example here), then I exported the new table to an Excel file. Every time I run the script it overwrites the temporary table (new_table) so that the information is current. Works like a charm, now.

Here is the code I finally ended up with:

dim copy_db_name as c
copy_db_name = "[PathAlias.ADB_Path]\new_table.dbf"

copy.fetch_parent = .t.

dim a_tbl as p
a_tbl = table.open("[PathAlias.ADB_Path]\current_set.set")

query.order = ""
query.filter = alltrim(CurrentForm.Controls.ExpInfo)
query.options = "I"
query.description = "Temporary Query"
i_indx = a_tbl.query_create()

copy.set = "[PathAlias.ADB_Path]\current_set.set"
copy.db = copy_db_name
copy.dd = .F.
copy.delete_o_dd = .T.
copy.records = .T.
copy.fields = 4
copy.field1 = "Table_id"
copy.field2 = "Student->Firstname"
copy.field3 = "Student->Lastname"
copy.field4 = "Student->Year"

a_tbl.copy()
a_tbl.close()
'=====================================================

dim filename   as c
dim exportfile as c
    exportfile = "export_"+rand_string(6)+".xls"
    filename = session.session_folder + exportfile
dim b_tbl as p
    b_tbl = table.open("[PathAlias.ADB_Path]\new_table",FILE_RO_SHARED)
    query.order = "aid"
       query.filter = "" 'alltrim(CurrentForm.Controls.ExpInfo.value)
    query.options = "I"
if ut(CurrentForm.Controls.ExpInfo) = "CURRENT" then
    b_tbl.query_create()
    export.type = 4
    export.names = .T.
    export.file = filename
    export.options = ""
    export.field_sep = ""
    export.record_sep = ""
    export.fields = 4
    export.field1 = "table_id"
    export.field2 = "firstname"
    export.field3 = "lastname"
    export.field4 = "year"
    b_tbl.export()
else
    b_tbl.query_create()
    export.type = 4
    export.names = .T.
    export.file = filename
    export.options = ""
    export.field_sep = ""
    export.record_sep = ""
    export.fields = 3
    export.field1 = "table_id"
    export.field2 = "firstname"
    export.field3 = "lastname"
    b_tbl.export()
end if
    if file.exists(filename)
        response.redirect(session.session_url + exportfile +"?"+ time("hms03")) 
    end if

b_tbl.close()

Hope this helps! If someone sees any major error I've made, let me know. But it does work. The trick was to remove all code that refered to the databases itself, and refer directly to the tables. Also, the xbasic generated by the copy and export operations kept putting in variables to help define the Order and Filter. For some reason the web server does not like doing it that way, so I just set it myself.

[[email protected]]

Re: Export a Set from WAS to a CSV file

The export operation works on a table. For some exports, I copy the set to a table and then export from that table.

Other times I export each table from the set due to the one to many relationship which can cause some duplication of the parent tables when there are multiple children.

You can do all of this on the desktop and then move the code into your web page.

Thanks for responding and confirming I can't export a SET.

[[email protected]]

Re: Export a Set from WAS to a CSV file

I finally got this to work. I used Al's suggestion, and copied the set to a table (only including the fields I needed, simplified in the example here), then I exported the new table to an Excel file. Every time I run the script it overwrites the temporary table (new_table) so that the information is current. Works like a charm, now.

Here is the code I finally ended up with:

dim copy_db_name as c
copy_db_name = "[PathAlias.ADB_Path]\new_table.dbf"

copy.fetch_parent = .t.

dim a_tbl as p
a_tbl = table.open("[PathAlias.ADB_Path]\current_set.set")

query.order = ""
query.filter = alltrim(CurrentForm.Controls.ExpInfo)
query.options = "I"
query.description = "Temporary Query"
i_indx = a_tbl.query_create()

copy.set = "[PathAlias.ADB_Path]\current_set.set"
copy.db = copy_db_name
copy.dd = .F.
copy.delete_o_dd = .T.
copy.records = .T.
copy.fields = 4
copy.field1 = "Table_id"
copy.field2 = "Student->Firstname"
copy.field3 = "Student->Lastname"
copy.field4 = "Student->Year"

a_tbl.copy()
a_tbl.close()
'=====================================================

dim filename   as c
dim exportfile as c
    exportfile = "export_"+rand_string(6)+".xls"
    filename = session.session_folder + exportfile
dim b_tbl as p
    b_tbl = table.open("[PathAlias.ADB_Path]\new_table",FILE_RO_SHARED)
    query.order = "aid"
       query.filter = "" 'alltrim(CurrentForm.Controls.ExpInfo.value)
    query.options = "I"
if ut(CurrentForm.Controls.ExpInfo) = "CURRENT" then
    b_tbl.query_create()
    export.type = 4
    export.names = .T.
    export.file = filename
    export.options = ""
    export.field_sep = ""
    export.record_sep = ""
    export.fields = 4
    export.field1 = "table_id"
    export.field2 = "firstname"
    export.field3 = "lastname"
    export.field4 = "year"
    b_tbl.export()
else
    b_tbl.query_create()
    export.type = 4
    export.names = .T.
    export.file = filename
    export.options = ""
    export.field_sep = ""
    export.record_sep = ""
    export.fields = 3
    export.field1 = "table_id"
    export.field2 = "firstname"
    export.field3 = "lastname"
    b_tbl.export()
end if
    if file.exists(filename)
        response.redirect(session.session_url + exportfile +"?"+ time("hms03")) 
    end if

b_tbl.close()

Hope this helps! If someone sees any major error I've made, let me know. But it does work. The trick was to remove all code that refered to the databases itself, and refer directly to the tables. Also, the xbasic generated by the copy and export operations kept putting in variables to help define the Order and Filter. For some reason the web server does not like doing it that way, so I just set it myself.

Thanks for responding and confirming I can't export a SET. I like your solution Stephen for combining the SET into a single table for export however, I can't have everyone using the same Temp DBF to export.

But as I read your message and see you creating a random file name for the XLS it's triggered a solution (for me) that overcomes the use of the same file name for the DBF.

I will make a "Temp dbf" for each user (using their unique log in data) which will prevent a file in use message (unless they do it to themselves and then they should suffer...j/k).

Thanks again