Upload and Read an Excel file
In the last few weeks we have had a few instances where clients were looking for a way to ‘mass update’ something in the database using a spreadsheet. Traditionally, we would require the file be exported as a tab delimited file. I chose tab delimited instead of the usual comma delimited because I don’t have control over what data is being uploaded, therefore having to sort out which commas are in text and which are not, and whether or not the file was exported correctly, was frankly becoming a nightmare. So, rather than have to sit on the phone with the client every time they wanted to update their tables, a new solution had to be sought.I ran across a function, I don’t remember where, but it uses the latest JDBC (previously installed on our server, which is btw, an awesome addition) with our version of ColdFusion 7. The function actually creates the connection on the fly using java objects, so it can be used in hosted environments, but it also works in our situation where we have full control of the server. I put the function into a CFC and made a few changes, the most important to me was removing spaces in the ‘column heading names’ because spaces are just annoying. So this CFC works like so: the file is uploaded (with some error checking) and then read in using the CFC, and placed into a query object. At that point you can reference the excel document just as you would any other query. Of course I have to check to make sure that the columns are what I expect (I use list Compare from the CFLib, to compare column lists), otherwise the query starts bombing randomly with no apparent reasoning. In one particular project, I am basically reloading a table based on the Excel input. So in order to expedite the process, I am dropping the table and recreating each time. I suppose it’s easier to use the truncate command, or delete all the rows to clear out the table, but I just feel better starting fresh. Of course this can also create problems if the table is being accessed somewhere else, and there’s a problem between the drop and re-create, so it’s not the best plan in the world. I guess I’m just stubborn and I like what I like, whether it’s good practices or not.