EasyCFM.COM ColdFusion Forums / Oracle Related Issues / Bulk Insert from text file

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Bulk Insert from text file

remove book mark from this topic Printer-friendly Version  send this discussion to a friend  new posts last

02-27-2008 @ 4:56 PM
Send P.M.
My Gravatar!
Powered by Gravatar
Posts: 935
Joined: Jun 2007

I'm not sure how to start this process so any guidance would be appreciated...

I have a web site where the client can update information about their systems.  The system is broken up into tabs describing various aspects of the system.  For a scenario, lets say it is a property.  The client needs to update the property, one tab might be the physical house, another tab might be the current owners, another tab could be the upgrades performed on the house, the next tab could be the tax information.  

Okay, so far so good?  I hope so.  Now, I have a client who has been tracking this information in an excel spreadsheet and wants to use my web based solution instead.  He has a 100+ houses to enter and would like to use the spreadsheet (or csv file or text file) to insert his data into the system.  

Still with me?  Good.

I've used flatfiles as external tables before so that is the approach I'm thinking I'll take unless someone here can suggest a better way?  They will only be doing inserts at this time (don't get me started on how they want to do updates :rolleyes: ).  

So, if using external tables as the solution, I'm thinking something like this:

1) Client uploads the file.
2) File is uploaded to server.
3) Server treats the file as an external table
4) CF performs the select on the external table (csv file) and inserts data into a temp table
5) Tmp table is checked for data consistency
6) If all checks ok, data is inserted into production tables.  If fails, process is aborted.

Now regarding step 6, I'm thinking I might try to flag the row if an error was found and only insert only rows that weren't flagged but I'm sort of inclined to think I may abort the whole process.  I don't want the client to mess up other records.  

Still with me?  Great.  Now, my other delima with this process is how do I get the client to adhere to a format I need the data to be in?  Would you recommend me supplying them an excel template?

Sorry for the long post but this project really has me excited so I want to make sure I dont run off an start coding without a well thought out plan.


Website Designed and Developed by Pablo Varando.