8

I'm looking for a good solution when users need to enter large amounts of data into a web application.

Example

  • A store owner wants to enter 30-40 new products into the system every month. The store owner has a spreadsheet of all the products, their quanities, prices, manufactures, contact information, serial codes, etc (probably about 10 different fields).

Right now I'm thinking some sort of grid view that allows the user to copy and paste data from say Excel or whatever spreadsheet applicaton they use.

What is the best web solution to allow the user to enter this much data (without having to enter each item individually)?

4 Answers 4

3

How about a UI to upload the spreadsheet file to be automatically processed by a database importing routine?

7
  • My concern with this solution is that it's not very user friendly because we have to wait until it's uploaded to check for data input errors and then the user has to go back to the spreadsheet and fix them then reupload.
    – Mark
    Dec 28, 2010 at 1:34
  • 1
    @Mark: you could implement some pre-upload checking on the client side by using javascript. Yes, it calls for duplication of validation code, but validation is often a two-location thing. Client-side for speed and server-side to ensure that nothing on the client side is forgotten. Dec 28, 2010 at 9:20
  • Where does this spreadsheet come from? Why would the data be invalid? Maybe your site can also allow the user to download a spreadsheet template to be filled however it's filled with its own built-in validity checks. Dec 28, 2010 at 14:49
  • You might get some ideas from this related question: ui.stackexchange.com/questions/482/… Dec 28, 2010 at 15:06
  • Hm, thanks for the responses. I'm thinking right now the best soltuion would be to use a Excel template then let them upload it and edit/fix their errors in a web UI. Making it a bit easier, so they don't have to continually switch applications to complete the upload task. I was hoping for a more user friendly solution though.
    – Mark
    Dec 28, 2010 at 18:51
3

Apologies, new poster.

So, if I read this correctly, what you are asking for is a way for a web application to read data being pasted in from the clipboard coming in from an Excel spreadsheet? It would certainly help knowing what technologies you are working with. Pasted Excel data will look like formatted text, like so:

0055486645655 BEANS, GREEN 700070  
0005525551154 BEANS, PINK 99010

I would just set up a text field that data could be pasted into. The string could then be sanitized and parsed out into a table based on your formatting expectations. It certainly wouldn't be a pretty solution, but it would get the job done. In JSF you could simply use AJAX to pass the data to the server, hide the input field, and have the server update a data table with the contents for the user to approve to ensure that the proper information is being passed into the database.

2

What would be great is if you already knew the format in which they save their data, that way you could have a simple file input field (then make sure the user can review what he uploaded before it's actually commited to the database): users love "magic buttons" where you just click one thing and it calculates everything on its own :-)

And even if you don't know the order of columns, if it's a known format like CSV, you could display the first two rows of data and ask the user to match the data with the right column, that way you will know the order and can import everything at once without forcing users to store their data in an extremely specific format, and you can even remember the format for next time. Personally, that would be my favorite choice.


Otherwise for manual input, I would suggest to display only a single row initially:

  • Then when the user filled at least all required columns for that row, add a second row of input below the first one, that way, unless the user made a mistake and comes back to an earlier row, he will most likely enter data sequentially and focus on only row at a time instead of being faced with a giant wall of input fields.

  • you should save drafts from time to time (e.g. Stackexchange) because there is nothing more frustrating for long data inputs than having to type it all again if you accidentally press the wrong key (actually, when leaving the page without submitting, you may want to show a confirmation box mentionning that the data haven't been saved yet)

  • make sure to highlight the whole row containing the focused cell (and perhaps column as well), that way if he wants to modify a column far away on the side, he might fill the wrong line if there are many rows on screen. Zebra-coloring could help as well, but opinions are divided on that matter.

    • if the grid of rows is really long (I wouldn't suggest it, it could easily get confusing, but if you really have to), then make sure the title of the columns are always visible on top even if you scroll. For example, see the Thead jQuery plugin demo.
0

A simple text box, with this label:

Enter a list of products like this:
Stock Number; Product Name; Name on bill; Number in stock
separate columns by semicolon, one product per line

and parse the data.

It's a bit of work to get right, however: When validating input, detect malformatted and unexpected data, point that out to the user, and allow them to correct it without losing it. The simplest, server-side way would be:

I have recognized the following 75 products:

Is this correct? [Yes, enter data] [No, let me correct it]

Even better would be a client side preview (like posting on stackoverflow).


Rationale: Compared to a grid, you lose cell navigation and column alignment, which is important when reviewing and modifying the data.However, when entering data, the cell and row separator matters less, and Return has the more appropriate behavior to start a new line.

You get a very simple interface, quick to use for entering one or two products, and to paste a large list of records freom whatever source available,. The same code can be used to process uploaded files (as @Michael Zuschlag suggested).

I would avoid putting the values into individual input fields - often this helps the programmer more than the user.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.