We do this here at work and we've managed to have a ton of problems handling Excel spreadsheets. What we finally did we have our users export the spreadsheet as a CSV file (one of the options from "File->Save As". Then, the users would Zip the file and upload the zipped version. This is considerably smaller than uploading a spread sheet.

You do not want to use GET as you will almost certainly get a "414 Request URL Too Long" error. This is configurable by your Web server, but it would still be ridiculous (and I'd just love to see your Web access logs with that one :). You want to use CGI.pm to upload the data. To unzip it, Archive::Zip works really well. If you have problems with Archive::Zip truncating data, make sure that you have the latest version of Compress::Zlib installed as this is a common problem.

To process the contents of the CSV file, you can try Text::CSV or any of another handful of modules to deal with this.

I would consider uploading the files to the server and then having another process deal with them. You're dealing with large files and the script might timeout on the user. This is Not Good. Be sure you have the user's email address so that you can send them feedback on the success or failure of incorporating the data into the database. Another reason you might want to consider having another process handle adding the data to the database is that it will be much easier to control the program flow. You never know when someone is going to click on the "Stumbit" button.

With uploads this large, I would seriously rethink a MySQL decision unless you have a version that supports atomic transactions. If you need to roll back the upload after processing the 6000th line, you're going to hate life if you can't issue a simple $dbh->rollback command.

Be sure to test the heck out of this. Be sure to insert gobs of dummy data into the system. Use bad dates, quote numbers, stuff in an extra field or 17. Short some of your records by a few fields, etc.

Also, I would consider having them FTP the data instead. Periodically check the FTP directory and process (and subsequently delete) files that are in there. Users wouldn't have to worry about waiting for their browser and it's an easier problem for you to code around.

Basic security: make sure you use placeholders with your DBI statements. Since you'll likely have repetitive statements, this means you script will operate faster. It also ensures that people can't "accidentally" try to insert dangerous characters in the spreadsheet that might break your script. Make sure that you delete the files after you are done with them (don't want to fill up the harddrive with files this large) or, better yet, figure out an archival scheme. Also, limit the max total directory size of the directory these files are uploaded. No sense in having 30 people upload 50 meg files and having your server crash from lack of disk space.

Cheers,
Ovid

Vote for paco!

Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

If MySQL minus transactions is what's in production, an easy way around the lack of transactions would be to make a temp table to process that data in. If it works, lock them both and update.

-Lee

"To be civilized is to deny one's nature."

Why not upload the whole file and then grab the data out of it using Spreadsheet::ParseExcel?

"The first rule of Perl club is you don't talk about Perl club."

And there is also Spreadsheet::WriteExcel::FromDB which does the opposite. FYI.

--
Tatsuhiko Miyagawa
miyagawa@cpan.org

The Questions:
1) Am I going about this the wrong way? Should I not use a form and/or upload the excel file?

Well, not the necessarily the wrong way (TMTOWTDI and all that), here's an alternative approach:

1. Provide an upload widget for the user to upload the Excel file
2. Spawn a seperate process that will parse the file (see Spreadsheet::ParseExcel) and update the MySQL database
3. return a message to the user letting them know their request is being processed

--
tune

With CGI.pm you can upload a file quite easily. Over a LAN it will be reasonably quick. As people have pointed out exporting Excel to another format and zipping the file will make the post go even faster.

Once you have the file on the server you can decompress it and stick it into mySQL. Using the suggested modules.

Given that an Excel file may take a while to upload, extract and store in mySQL, I'd also suggest copious logging, so you can track down what's going on if people complain. As monks have mentioned you need to give positive feedback to the broser asap, to prevent multiple submits and user frustration.

To avoid multi-click behaviour of users, merlyn has a recent WebTechniques article about one click processing, which is well worth reading, even if you don't use it in this project.

If you are using CGI.pm you can pretty easily create a file upload field. to upload the file.

HTML form text fields are limited to 32K in size, so it may be hard to paste 7000 rows there.

--mandog

While I encourage you to follow the advice of those advocating a file upload rather than a form field paste, you might be interested to know that (at least in IE5) you can change the enctype attribute for a form to 'text/plain' and it will send the server un-escaped text from your form rather than the normal 'x-form/urlencoded' (something like that) encoding type.

Some things that could foobar the parsing are vertical tabs and two byte fonts like Japanese (both of which have caused me trouble in the past), curly quotes and ticks, cr/lf (no macs?), and maybe new versions of Excel and embedded binary, for starters. It pays to limit allowed characters and flag anything wierd (any Europeans using commas instead of decimal points?) and so on.

So get the Excel, parse it and email them if it looks wierd. You could keep them waiting, though it takes time during which you have to keep the browser from timing out, especially if you use a tool I once tried to turn Excel into HTML (think lots of time, cpu, and memory for a large file). Maybe you want to provide a utility so they can re-upload a given day, and see if they missed one.

HTTP POST is fine (set a limit in your script), though there may be other options. For example you could write a Perl program which runs on their computer and ftp's it up automatically. Maybe someone might be interested in taking a swing at an activex/COM object which I won't even take a swing at.. There is this node. It seems something interesting would be possible with the perlctrl or perlcom parts of activestates's perl dev kit (but I don't own it).

I mention these because something always goes wrong. So even with a form, you can upload the wrong file, the filename is bad, it's really a shortcut, there was a holiday and they forgot, the computer's clock went nuts, etc. etc. If you can somehow get identification data into their table data from the beginning, you will be happier.