HTML Form to Query Excel Data

[millsy007]

Hi
I have an excel spreadsheet I want to query online where a use inputs parameters and the relevant data is then shown, what is the best method/tools for going about this?
Thanks

[Dandello]

Excel isn't a web application, so I assume you're actually referring to converting the spreadsheet to a database and then querying it?

In that event, there are numerous possibilities, Perl, PHP with MySQL, anything with the capacity to query a database...

The most common method for a web application would probably be a php form querying a MySQL database.

[millsy007]

Thanks, I have seen a few things online (that look complicated) about how it can be possible to query excel online.
But as you say excel isnt a web application so would I be best advised to import the data into access and then use the access database to query online?

[Dandello]

That would be the least complex method. And for a novice, I would suggest the PHP/MySQL method. (I would use Perl, but that's MY thing.)

In this case, set up your MySQL database, convert the spreadsheet you want to a format MySQL can import, import it and that's done. (And the conversion may or may not be as simple as saving it as a CSV file, depending on the structure of your spreadsheet.)

Then build your PHP form to query the database.

[millsy007]

Thanks, I think PHP/MySQL would be the sensible choice. However the person who will be working on it has no programming knowledge. I saw that frontpage and access allow a fairly similar (albeit limited) functionality? Would this be a possible way of doing the same but by using wizards etc?

[Dandello]

My personal recommendation - point your person to PHP & MySQL for Dummies. Seriously. It has enough to get started programming PHP and MySQL. Assuming this is supposed to be an online application, I have doubts that many hosts offer Microsoft Access. But nearly all of them offer MySQL, which really isn't that hard to use.

[drjohn]

FrontPage!!!! One of the most rubbish web tools around. Even MS disowned it several years ago.
I too have never seen a web host offering Access as the database, so that's not worth trying at all.

It is much better to just follow the advice given above. Doing things the correct way is so much better than forcing things to work the wrong way.

Incidentally, I find that using a tab delimited txt file from excel is a safer bet than a csv one, as you might have a comma in some of your data, and that will wreck havoc with your database. I receive 40000 - 50000 lines of data from an office's database to stick into an on-line MySQL database, in csv format, and the first thing I do is open it in excel and save as tab delimited. (and that means I have to re-define the date format and excel converts it from yyyy-mm-dd to dd-mm-yyyy, and of course MySQL's date format is the first one, of course.)

[Dandello]

Personally I prefer pipe delimited (|). :icon_cheesygrin: (And I do a lot of conversions from Excel into various formats, including the date formats when needed.)
But the Dummies Guide is a great way to start - seriously.
Learn to do it right and you won't have to do it over.

[teedoff]

Thanks, I think PHP/MySQL would be the sensible choice. However the person who will be working on it has no programming knowledge. I saw that frontpage and access allow a fairly similar (albeit limited) functionality? Would this be a possible way of doing the same but by using wizards etc?

One other point. The "person working on it" I take it to mean a person who might be adding updating or deleting records in this spreadsheet/database? If so, as a programmer you might want to work up a user application for her. Something simple so she can type data in textfields and then an update or delete query could be run to alter the db tables. No reason to involve any more ppl in direct database manipulation as neccessary.

[Dandello]

Very true - the fewer people with full access to the 'raw' data, the better.