I have a customer who has no database for his plant inventory, so he keeps it in an Excel worksheet (8,000+ rows and growing). I created an odbc data source using the MS Excel ODBC driver and then put the worksheet in the same folder as the Access mdb and created a link to it within Access, made an upload page the the customer uploads his worksheet with and my form overwrites the existing table and then runs a query that either updates or inserts depending on what the query finds. Has been working flawlessly for quite a while.
Terry ----- Original Message ----- From: Dave Douglas <[EMAIL PROTECTED]> To: CF-Talk <[EMAIL PROTECTED]> Sent: Thursday, February 07, 2002 8:35 AM Subject: Importing Uploaded Excel Data into DB > Hello, > > I have searched past messages to the list and have seen some recommendations > for the csvtoquery custom tag > to put the spreadsheet data into a query. What makes my situation unique (I > think) is that the spreadsheet has > commas in the data in some of the columns. I tried CSVformat and am able to > print out the data on row at a time. Where I am stuck now is how to get > this information into the DB and how to get it in the right fields as right > now it is printing all columns for each employee on one line each and it > blows up the query when I try to insert #item# as the Value. Any Help is > GREATLY appreciated! > > here is my code so far: > > <cffile action="READ" > file="#File.ServerDirectory#\#File.ServerFile#" > variable="excelList"> > > <cfset NewList = Replace(excelList, "#chr(13)#", "^", "ALL")> > <cfset NewList = Replace(excelList, "#chr(10)#", "^", "ALL")> > > <cfloop index="item" > list="#NewList#" > delimiters="^"> > <cfoutput>#item#</cfoutput><br> > </cfloop> > > A working version, with csv and txt files available is at > http://www.high-techsolutions.net/development/UpLoad.cfm > > TIA, > > Dave > ______________________________________________________________________ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
