Updating a DB with Excel file via fileupload

[Guest]

I want to upload a file via a web interface and then take that file and upload a table with it.  Do I have to upload the file to my server and then read it to update my db 2 steps or can I do it in 1 step, upload/read/update?  Using CF9.

[ilssac]

Depends on what you call a "STEP"?

Yes you have to upload the file.  ColdFusion has no access to data on the client machine.  The client has to send the data to the server (i.e. upload) before ColdFusion can do anything with it.

But since the upload is only a line to two and your table update may only be a few lines, it is pretty easy to do this in a single CFML template file.

Finally, if you are doing some type of bulk loading process, you may want to investigate what your database supports for directly reading common file types, like csv, and updateing directly.  Databases are often more efficient at this and it is usually pretty easy to set up ColdFusion to fetch the file, put it in a place accessible to the database and tell the database to read the file for upload.

[tclaremont]

I just did something similar this morning. Using CF to read the file, loop through it and perform the inserts took several orders of magnitude longer than simply using the SQL Server Import Data Wizard.

I would write your CF code to allow the upload, and place the Excel file in a dedicated directory. Then, later on your page, send the signal to the database to perform a stored procedure that imports the data.

[JR__Bob__Dobbs]

What database server are you using?  Does your database have the ability to import data from Excel?

[Reed_Powell-ttnmOb]

I'd recommend against the approach of uploading the file and just passing it off to the database for processing.  It's better to upload the file, check its contents for correctness, and then put the data into the database.  All you need is for the user to add a column, change a heading, put text into a numeric field, etc., etc., and either your database data quality goes down the drain, or the database Bulk Insert load fails.

Once you know that the spreadsheet is good, then you can either do the Bulk Insert or make a second pass over the data to do the INSERTs in a CFQUERY.

Hopefully you're running CF9, which makes the XLS interfacing easy, but if not then look at POI-based XLS interfaces from Ben Nadel or Mizrael that work very well and are easy to work with.

Spreadsheets are a great interface for dealing with user data, but just like using a CFFORM you need to do data validation.

-reed

[ilssac]

Reed Powell wrote:

I'd recommend against the approach of uploading the file and just passing it off to the database for processing.

While validating data is always advisable, I would just point out that this might be done in the database as well.  CFML may not necessarily be the best choice for the validation, nor is it necessarily a bad choice either. I am just saying that there are choices.

[Guest]

I got the upload to work but when try to read the file I get:

An error occurred while reading the Excel: java.lang.IllegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream.

Any ideas?