csv file upload

Question

Hi,

In my Grails app, I would like admin users to be able to upload a CSV file that contains data such as:

1. List of users to be added to system
2. List of groups to be added to system
3. Assignment of users to groups

I have no idea how the user will generate these CSV files - most likely from Excel, Access or similar, and therefore I've no way of knowing which column will contain which data. So I'm planning to allow the user to specify which column contains users, groups, etc.

I'm wondering if there's a JavaScript component that could help with this. Ideally I'd like to implement the following:

• User uploads file
• In browser, user is shown first N lines of uploaded file and prompted to select the column that contains the users, groups, etc.
• Column information is uploaded to server

Is there a client/server side component that could help with this, or an entirely different approach which would be superior to that outlined above?

I should emphasise that the users of this system will not be technically gifted, so expecting them to provide an XML/JSON file instead is out of the question (and you can definitely forget about asking them to call a Web Service instead of uploading a file).

Thanks, Don

Answer 1

I like your solution so far, given that the users are non-technical, and that you want to be able to accept this data as a file upload, rather than have the users enter it directly into your application.

I would simply suggest that when the user uploads the file, the server returns the first five (or so) lines back to the client as an HTML table. Then you can have <select> drop-downs as the headers for each column, with the pre-set options you're looking for. You can validate that the user has assigned all available options to each column (use JS to remove options from the select as they use them, but be sure to provide a method to undo and change selections), and allow some columns not to be labeled (which the server will just ignore when parsing the file.

If possible, also illustrate (perhaps in a graph format or just an example sentence, if applicable) how their label choices will apply to the relationships. For example, "New user ABC will be a member of new group XYZ." If ABC and XYZ are unexpectedly backwards, the user will recognize they made a mistake.

Also, some users will inevitably upload a file where they used rows as columns and columns as rows. Either provide a GUI function to reverse this ("rotate" the table), or let them choose which axis to label.

I would also suggest providing your users with a collection of example files in various formats (Excel, Access, etc), and give them explicit instructions for how to enter the data they want, and step by step instructions to export as CSV and upload.

Answer 2

I have no idea how the user will generate these CSV files - most likely from Excel, Access or similar, and therefore I've no way of knowing which column will contain which data.

I should emphasize that the users of this system will not be technically gifted

With these two things in mind, are you sure that CSV import is the best way to handle bulk user creation? It's a great technical solution, but the question is, will your users be able to take advantage of it?

It may be worth implementing an alternative bulk create option for those who don't get CSV or are scared off by Excel. Perhaps a JS grid that has the required fields where they could manually enter the data for each field and enter as many as they need at once, with a link to upload a CSV file as an option for those who would use it.

For the CSV option, since your users are not technically-minded, it would be better to give them instructions on how to create the csv files that specify the order fields should be in. Along with a screen shot and a sample file.

Another option is to require the field names be the first row of the document, and require that they use specific labels for the fields. If you do that, you could figure out from the first row what order the data is in. You could also put in a check that looks for the titles in the first row and if they're not found, tell the user they need to add the field names to the CSV and re-upload.