Member Avatar for 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

  • 4 Contributors
  • 9 Replies
  • 544 Views
  • 2 Months Discussion Span
  • Latest Post Latest Post by Dandello

Dani AI

Generated

This thread (original posts from Nov 2011–Jan 2012) correctly identifies that a spreadsheet is not an ideal web data store. Below are concise, practical next steps and a minimal, safe example you can apply now without re-reading every reply.

A simple, reliable workflow

  1. Export the sheet to a clean text file (use tab or pipe if your cells contain commas). Ensure UTF-8, remove any Excel BOM, and fix any date-format changes Excel made.
  2. Choose a data store: a hosted MySQL (very common) for multiuser web apps, or SQLite for a tiny single-file app. Create a table with proper column types, a primary key, and indexes on columns you’ll search.
  3. Import the file (phpMyAdmin, LOAD DATA INFILE, or sqlite3 import). Verify types and date formats after import.
  4. Build a simple web form + server endpoint that uses parameterized queries (never concatenate raw input into SQL). Add pagination and a sensible result limit to avoid timeouts on large datasets.
  5. Give only the web app write access; don’t give casual users raw DB access. For data edits, create a small admin form rather than letting people edit the database directly.

Minimal safe PHP example (PDO, prepared statement)

<?php
$pdo = new PDO('mysql:host=localhost;dbname=yourdb;charset=utf8mb4','user','pass',
    [PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]);
$q = trim($_GET['q'] ?? '');
$stmt = $pdo->prepare('SELECT id,name,value FROM items WHERE name LIKE :q LIMIT 100');
$stmt->execute([':q'=> "%$q%"]);
echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC));

Troubleshooting & cautions

  • Watch encoding and date conversions during export/import.
  • Index the fields you search to keep queries fast.
  • Validate and escape all inputs; use prepared statements as shown.
  • For a non-programmer, consider a low-code hosted option (sheet-to-web services) or build a tiny admin UI so only the web layer touches the DB.

As , and implied, move the data into a proper backend, protect the raw data, and give the non-technical user a simple form for updates.

Recommended Answers

All 9 Replies

Member Avatar for 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.

Member Avatar for 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?

Member Avatar for 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.

Member Avatar for 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?

Member Avatar for 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.

Member Avatar for 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.)

Edited by drjohn because: n/a
Member Avatar for 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.

Member Avatar for 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.

Edited by teedoff because: n/a
Member Avatar for Dandello

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.