Re: Importing Uploaded Excel Data into DB
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: > > file="#File.ServerDirectory#\#File.ServerFile#" > variable="excelList"> > > > > >list="#NewList#" > delimiters="^"> >#item# > > > 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Importing Uploaded Excel Data into DB
Ok, right now I am finishing up development on the app with Access, b ut it will be SQL 7 in production. - Original Message - From: "C. Hatton Humphrey" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, February 07, 2002 11:40 AM Subject: RE: Importing Uploaded Excel Data into DB > What kind of DB are you using? One way we've accomplished this in the past > is to actually create a "floating" Excel data source. We upload th e file > and use CFFile to copy it into the right position and give it the r ight > name. Once in place, we query the Excel data source and then inser t into > the SQL Server one. > > That was an old solution and we have a looping process in place as well that > limits the number of records dealt with to 100 per load. If you're using > SQL Server there are DTS packages or Bulk Insert options available as well. > > HTH > Hatton > > > > -Original Message- > > From: Dave Douglas [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, February 07, 2002 11:35 AM > > To: CF-Talk > > 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: > > > > > file="#File.ServerDirectory#\#File.ServerFile#" > > variable="excelList"> > > > > > > > > > > > list="#NewList#" > > delimiters="^"> > >#item# > > > > > > 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/cf-talk@houseoffusion.com/ > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists __ 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Importing Uploaded Excel Data into DB
What kind of DB are you using? One way we've accomplished this in the past is to actually create a "floating" Excel data source. We upload the file and use CFFile to copy it into the right position and give it the right name. Once in place, we query the Excel data source and then insert into the SQL Server one. That was an old solution and we have a looping process in place as well that limits the number of records dealt with to 100 per load. If you're using SQL Server there are DTS packages or Bulk Insert options available as well. HTH Hatton > -Original Message- > From: Dave Douglas [mailto:[EMAIL PROTECTED]] > Sent: Thursday, February 07, 2002 11:35 AM > To: CF-Talk > 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: > > file="#File.ServerDirectory#\#File.ServerFile#" > variable="excelList"> > > > > >list="#NewList#" > delimiters="^"> >#item# > > > 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
