Gossamer Forum
Home : Products : DBMan : Customization :

Export to Excel mod

Quote Reply
Export to Excel mod
I've just finished testing an Export mod with my outdated browsers (IE3, IE4, NN3 and NN4). Was hoping that there are some interested parties willing to test with their browsers, especially IE5.

Use the Guest account and View option at www.public-knowledge.com/cgi-bin/dbman/db.pl .

I can post the mod if anyone is interested. It is fairly generic - exporting only non-admin fields.

Ideally I would like an Export Preview page that allows the user to select which fields to export. Any ideas here would be appreciated. I imagine another form in html.pl with checkboxes for allowed fields, then a loop to process this array, rather than the one I use now.
Code:
for (my $i =0; $i <= $#db_cols; $i++) {
if ($db_form_len{$db_cols[$i]} >= -1) {
# Export all non-admin fields (admin form length = -2)
print qq|<td width="$db_lengths{$db_cols[$i]}"> <$font>$rec{$db_cols[$i]}</font></td>|;
}
}

Maybe Carol can help here??? I have seen some of her other code wizardry.

Look forward to all comments and suggestions.




------------------
Don Mitchinson
Quote Reply
Re: Export to Excel mod In reply to
Thanks for the comments Eliot. Not much complicated going on in my code - other than some workarounds needed for Internet Explorer.

I don't know if I see a direct need for your other file format suggestions. The export mod could force output to a comma-delimited (CSV) file. This file type can be imported directly into Outlook's Address book and I assume Eudora just as easily.

As for Word Labels - I would use Mail Merge on the same CSV file.

Hope that helps.

Quote Reply
Re: Export to Excel mod In reply to
Don,

Great job! What I would like to see is the ability to export data from DBMAN into other file formats (I know this has been discussed before, Carol and others). But I would like to see what you are requesting, Don, in terms of choosing records and then exporting the records into the following programs/file types:

1) Outlook Address Book Entries
2) MS Word Address Labels
3) Eudora Address Book Entries

The Excel Spreadsheet Export is a wonderful starting point! Keep up the great work!

Smile

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us

[This message has been edited by Eliot (edited October 04, 1999).]
Quote Reply
Re: Export to Excel mod In reply to
Looks great to me. I don't see anything that needs any fixing! Smile

If you can put together the mod and add it to the Resource Center, that would be wonderful.


------------------
JPD





Quote Reply
Re: Export to Excel mod In reply to
Thanks Carol.

I have added the mod to the Resource Center. Hope you don't mind my extensive borrowing of the documentation format from one of your mods. It made it much easier to use a template.

I still would like to add the Export Preview idea some time in the future. Any ideas you might have would be appreciated.

Thanks again




------------------
Don Mitchinson
Quote Reply
Re: Export to Excel mod In reply to
No problem at all with using my mods as a template. I'm honored! Smile

I don't know if it's necessary to include the option of which fields to export. It's pretty easy, once you get into Excel to delete any columns you don't want.

You might consider moving the export button to the search results page, though, so the user can see the records that will be exported before actually doing it. It would just require a little hidden form with fields for each database field, each with a value of "$in{'fieldname'}".

The only reason I think about it is that, when I tried it out I didn't know how many records would be exported. (I could have done a search first and then gone back, but, frankly, it didn't occur to me.) It's kinda nice to have some idea of how big the file is going to be before you download it.

Even so, what you have is excellent. I'm sure most users would be smarter than I was and find out how many records would be in the file before they click the "Export" button.

(I'm really glad you made up the mod. I think I may have a use for it very soon! Smile )


------------------
JPD





Quote Reply
Re: Export to Excel mod In reply to
Mitch,

The export mod wouldn't work for me; the error message indicated that your CGI app had misbehaved by not returning a complete set of headers. I'd like to try it out, though, and Carol's suggestion about putting it on the results page is good too, I think.

Slightly off-topic -- HOW do you do the "sort by 1, sort by 2, sort by 3" thing? I looked in the URL and they were, "&sb1=x&sb2=y&sb3=z" but I can't think of how this works, based on a default dbman install. Can you help? Smile

[This message has been edited by Glen Payne (edited October 05, 1999).]
Quote Reply
Re: Export to Excel mod In reply to
Great ideas Carol. I will play around with adding the Export option to the Search Results page. It makes a lot more sense to show them what they're going to get before creating a file they don't need.

Glen:
Where did the export mod fail - On my url or in your script using the Resource Centre mod?
What browser and version are you using?

As for the multiple sort method, I have posted an earlier message about that mod. And even some code. I just haven't had the time to post a mod to the Resource Center.

If you want you can read and respond to the thread ...
www.gossamer-threads.com/scripts/forum/resources/Forum12/HTML/001080.html

Thanks

------------------
Don Mitchinson
Quote Reply
Re: Export to Excel mod In reply to
Glen:

Found my mistake - my novice perl skills are showing again. I had made a 'slight' change to the code to account for some IE3 browsers that might not have Excel installed.

I left off a "$" from a variable. Didn't test before uploading the mod. Oops.

All fixed. Please give it a try again. Sorry for any inconvenience.


------------------
Don Mitchinson
Quote Reply
Re: Export to Excel mod In reply to
Thanks, Don (sorry I called you mitch before -whoops, wasn't looking hard enough!).

Your export worked like a charm -- a very useful mod; did you add it to the resource center?
Quote Reply
Re: Export to Excel mod In reply to
Thanks for your comments Glen. No problem calling me mitch - its the handle I picked.

I have added it to the resource center but they have to look at it first before it is approved and officially entered into the library.

You can have a look at my entry beforehand if you want. I don't think I am breaking any protocols. If there are any problems with the install, please let me know.

You can get it at:
www.public-knowledge.com/cgi-bin/dbman/mods/exportXL.txt


------------------
Don Mitchinson
Quote Reply
Re: Export to Excel mod In reply to
Hi,
Great mod...I am using IE50 and wondering if it works with your mod? I have attemted install...but I get a fatal error when the temp csv file is trying to write to my /csv directory. Any thoughts?
Quote Reply
Re: Export to Excel mod In reply to
I think it has something to do with protections on the directory. It needs to be set to read/write for everybody (chmod 777).

If it is Windows NT there are other security issues. I'd prefer not to address them here unless that is the case.
Quote Reply
Re: Export to Excel mod In reply to
You don't say how old this 'old' version of Netscape is - i.e. what version? Is Excel setup as a Helper App for .csv file extensions?

Where does it output the 11 records - to the browser or to the file? Does the csv file have the full number of records in it?

You can cut and paste the link into IE to check or FTP to the /csv directory.

[This message has been edited by mitch (edited January 31, 2000).]
Quote Reply
Re: Export to Excel mod In reply to
OK head banging time - I have tried to get the csv file to write to my /csv directory with no luck; I get the following error:
"
CGI ERROR
==========================================
Error Message : fatal error: Unable to open CSV file location (>/ecginc/csv/Admin.9493385205869.csv). at ./html.pl line 1384.

"

I have created the /csv directory in three areas, as follows:

/www/csv
/www/cgi-bin/csv
/www/cgi-bin/dbman/csv

each set to 777. I am using IE 5.0. The script runs and appears to work until you depress the Export button and then the error page appears.

Now here's the weird part...if I use Netscape 4.06, the script runs but prints some of the data to the screen then quits....?

Just in case, here is the path and URL info in my CGI script.

Quote:

# If you run into problems, set $db_script_path to the full path
# to your directory.
$db_script_path = ".";
$db_csv_path = "/ecginc/csv/";
$db_csv_url = "http://www.ecginc.com/csv";

# Load the form information and set the config file and userid.

Endquote.

MUCH thanks for any thoughts.
Quote Reply
Re: Export to Excel mod In reply to
What is your actual Unix path to your web directory? You mention setting /www/csv to
chmod 777, but your script $db_csv_path points to "/ecginc/csv/".

If don't know your actual path, but you're running your script from the
/cgi-bin/dbman directory, you can try to
use
Code:
$db_csv_path = "../../csv/";

This should bring it back to the root and then down to the csv subdirectory.
Quote Reply
Re: Export to Excel mod In reply to
Well, thanks for the help...it turns out that this is the only place that I need to list the actual Unix path..../u55/wfp...etc. No skimping on the code as I have done elsewhere.

So, it works...but not with my older version of Netscape, so I put a warning prior to the Export button. Any ideas or recent info on why this occurs? Thanks again. dse
Quote Reply
Re: Export to Excel mod In reply to
Whoa! It runs but only outputs 11 records plus the header line. If you View the results you get over 100! Im looking at the code as I write.
Quote Reply
Re: Export to Excel mod In reply to
Mitch,
Sorry for the lack of detail.

Let see:
Netscape Communicator 4.06

Helper APP - Yes Excel is set for csv.

The 10 records plus 1 header row are correctly output to the file in the /csv directory - interesting to note that if you View the records, the same ones appear in the csv file as the ones that appear on the 1st page of View! It's as if the file stops writing after the first page of record hits.

I have double checked via FTP to look at the written records.

Thanks.
Quote Reply
Re: Export to Excel mod In reply to
Just to make sure I communicated the issue correctly; This is the data output to the screen via the View command.

Header
Rec 1
Rec 2
Rec 3
Rec 4
Rec 5
Rec 6
Rec 7
Rec 8
Rec 9
Rec 10
Rec 11 *

* this one would not appear in the csv file. No mention of EOF either.
______
Two other things also occurred - if I change $db_max_hits in .cfg to 15 - 15 records are displayed; however, after the tenth record, the record output in .csv shifts:

itemA, itemB, itemC
itemB, itemC, itemD





[This message has been edited by dse (edited January 31, 2000).]