in reply to Uploading Excel data into MySQL via a browser

You will need to have the Excel file in your hands to try when things go wrong (which they will). I'm convinced Excel can't be trusted to export good CSV, though Access seems to work. You could also import from Access directly, if you don't need excel. I'd parse and not use CSV at any time.

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.

  • Comment on Re: Uploading Excel data into MySQL via a browser