Re: Bulk data loading
Maybe I have been lucky but I have found sql loader to be an incredibly easy tool to use for this need. On 9/6/06, Douglas Knudsen [EMAIL PROTECTED] wrote: yes. You can even do this 1) have user upload cvs file 2) use sqlloader called via cfexecute to load the data into the DB This will require installing the oracle client on the server and some patience :) DK ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252645 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Bulk data loading
This is something that the database should do, not CF. Not being an Oracle guy, I can't tell you what that is, but it has been discussed here recently. (Hint. :) ) Anyways, you're looking for whatever Oracle's equivalent is to MSSQL's DTS. That should at least get you on the right track. Ray -Original Message- From: Chris Tilley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 06, 2006 2:20 PM To: CF-Talk Subject: Bulk data loading Does anyone have a tested method of uploading bulk data to a database? I have a need (annually) for the admin person to be able to take data from an Excel spreadsheet and have it inserted into an Oracle database. Any sample code would be greatly appreciated. I've played with a CFC that makes Java calls but without success. Thanks in advance, -- Chris Tilley ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252247 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Bulk data loading
Does anyone have a tested method of uploading bulk data to a database? Just set a datasource using the Microsoft Excel ODBC driver and query in the data. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252248 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Bulk data loading
You're talking about a CSV right? What amounts to a simple list, but with zillions of rows? The trick I found to getting this done (which the good folks here helped with enormously) is to use java to read each line incrementally rather than having cffile try to scarf down the whole file into memory. I don't have code handy right this second (I can get hold of it when I get back home tonight) but basically once you read in a single line you just use list processing to feed the individual fields into an insert statement. I actually used listToArray() and then array notation in my code, to keep from having to use listGetAt() or somesuch on every spot on my insert. Was easier for me as well if I wanted to manipulate the field prior to saving as well. On 9/6/06, Chris Tilley [EMAIL PROTECTED] wrote: Does anyone have a tested method of uploading bulk data to a database? I have a need (annually) for the admin person to be able to take data from an Excel spreadsheet and have it inserted into an Oracle database. Any sample code would be greatly appreciated. I've played with a CFC that makes Java calls but without success. Thanks in advance, -- Chris Tilley ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252249 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Bulk data loading
Well, yea, I guess, depending on the size of this spreadsheet, this would work just as well. Chris, how large is this Excel sheet? -Original Message- From: Claude Schneegans [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 06, 2006 2:31 PM To: CF-Talk Subject: Re: Bulk data loading Does anyone have a tested method of uploading bulk data to a database? Just set a datasource using the Microsoft Excel ODBC driver and query in the data. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252250 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Bulk data loading
Matt Robertson wrote: You're talking about a CSV right? What amounts to a simple list, but with zillions of rows? The trick I found to getting this done (which the good folks here helped with enormously) is to use java to read each line incrementally rather than having cffile try to scarf down the whole file into memory. I don't have code handy right this second (I can get hold of it when I I blogged the method at www.opensourcecf.com =) Rick ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252251 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Bulk data loading
Rick Root wrote: I blogged the method at www.opensourcecf.com =) specifically... http://www.opensourcecf.com/1/2006/07/Reading-large-files-with-java-versus-CFFILE.cfm ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252252 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Bulk data loading
Does anyone have a tested method of uploading bulk data to a database? I have a need (annually) for the admin person to be able to take data from an Excel spreadsheet and have it inserted into an Oracle database. Any sample code would be greatly appreciated. I've played with a CFC that makes Java calls but without success. Thanks in advance, -- Chris Tilley With Oracle, 9i or newer your best bet is to set up an externally organized table. It's basically stored SQL loader which allows you to access an external text file as you would any read-only oracle table via SQL. You would export your Excel data into csv format, then set up the EOT to point to that file. You can then overwrite that file at any time with new data. Here's an example of the PL/SQL to create one of these. Most of it will be cut and paste - you'll need to create a directory in Oracle (pointer to the filesystem directory you will put the CSV in) and fill it in for MYDATA_DIR CREATE TABLE EXT_ACCOUNT_BALANCE ( ACCOUNTVARCHAR2(20 BYTE), BEGIN_BAL NUMBER ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY MYDATA_DIR ACCESS PARAMETERS ( RECORDS DELIMITED BY 0x'0a' CHARACTERSET WE8ISO8859P9 LOGFILE 'beginbalance.log' BADFILE 'beginbalance.bad' READSIZE 1048576 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY '' LDRTRIM REJECT ROWS WITH ALL NULL FIELDS ( account CHAR(255) TERMINATED BY , OPTIONALLY ENCLOSED BY '', begin_bal CHAR(255) TERMINATED BY , OPTIONALLY ENCLOSED BY '' ) ) LOCATION (MYDATA_DIR:'beginbalance.csv') ) ; ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252253 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Bulk data loading
Ray, Approx. 145kb, 1000 rows with 16 columns Matt, I'd love to see your sample code, if you don't mind sharing? Chris On 9/6/06, Ray Champagne [EMAIL PROTECTED] wrote: Well, yea, I guess, depending on the size of this spreadsheet, this would work just as well. Chris, how large is this Excel sheet? -Original Message- From: Claude Schneegans [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 06, 2006 2:31 PM To: CF-Talk Subject: Re: Bulk data loading Does anyone have a tested method of uploading bulk data to a database? Just set a datasource using the Microsoft Excel ODBC driver and query in the data. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252254 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Bulk data loading
On 6 Sep 2006, at 11:19, Chris Tilley wrote: Does anyone have a tested method of uploading bulk data to a database? I have a need (annually) for the admin person to be able to take data from an Excel spreadsheet and have it inserted into an Oracle database. Any sample code would be greatly appreciated. I've played with a CFC that makes Java calls but without success. You'll probably get the best performance using the Oracle SQL*Plus tools, specifically SQL*Loader: http://www.orafaq.com/faq/sql_loader -- Joseph Lamoree ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252257 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Bulk data loading
I've tried it but getting an error message: 12:52:15.015 - Expression Exception - in D:\Inetpub\cf\WorkingCapitalFund\FileReader.cfc : line 65 Variable RETURNSTRING is undefined. Any ideas why? On 9/6/06, Rick Root [EMAIL PROTECTED] wrote: Rick Root wrote: I blogged the method at www.opensourcecf.com =) specifically... http://www.opensourcecf.com/1/2006/07/Reading-large-files-with-java-versus-CFFILE.cfm ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252258 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Bulk data loading
Chris Tilley wrote: I've tried it but getting an error message: 12:52:15.015 - Expression Exception - in D:\Inetpub\cf\WorkingCapitalFund\FileReader.cfc : line 65 Variable RETURNSTRING is undefined. Any ideas why? Your cfc is referencing a variable that isn't defined? I don't know, I can't see your code =) returnstring isn't in my code anywhere... Rick ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252260 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Bulk data loading
Joseph Lamoree wrote: You'll probably get the best performance using the Oracle SQL*Plus tools, specifically SQL*Loader: http://www.orafaq.com/faq/sql_loader You are probably right - but for 1000 records, 145KB of data, and it only occurs once per year (I think that's what he said), it's probably not worth the effort. Rick ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252261 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Bulk data loading
On 6 Sep 2006, at 12:45, Rick Root wrote: You are probably right - but for 1000 records, 145KB of data, and it only occurs once per year (I think that's what he said), it's probably not worth the effort. True. I thought we were talking about a jazillion rows. I have a similar requirement for one of my projects -- to generate a batch of a few hundred thousand serial numbers and do a bulk import to PostgreSQL. The serial numbers are appended to a CSV flat file as they are created. I use the native psql program and a bit of shell scripting to bring them into the appropriate table. It takes far longer to generate the serial numbers with a PRNG than it does to copy them to the database, but less time than it takes to get another cup of coffee. -- Joseph Lamoree ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252264 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Bulk data loading
Sorry to come in late in the convo... If you can put the file in a public directory, I have found from some simple testing that CFHttp is faster than a buffered file reader... http://bennadel.com/blog/200-ColdFusion-CFHttp-To-Query-Much-Faster-Than-Jav a-Buffered-Reader.htm DISCLAIMER: I am a ColdFusion programmer, not a Java programmer, so there are probably issues. ... Ben Nadel www.bennadel.com Certified Advanced ColdFusion Developer Need Help? www.bennadel.com/ask-ben/ -Original Message- From: Chris Tilley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 06, 2006 3:26 PM To: CF-Talk Subject: Re: Bulk data loading I've tried it but getting an error message: 12:52:15.015 - Expression Exception - in D:\Inetpub\cf\WorkingCapitalFund\FileReader.cfc : line 65 Variable RETURNSTRING is undefined. Any ideas why? On 9/6/06, Rick Root [EMAIL PROTECTED] wrote: Rick Root wrote: I blogged the method at www.opensourcecf.com =) specifically... http://www.opensourcecf.com/1/2006/07/Reading-large-files-with-java-ve rsus-CFFILE.cfm ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252267 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Bulk data loading
yes. You can even do this 1) have user upload cvs file 2) use sqlloader called via cfexecute to load the data into the DB This will require installing the oracle client on the server and some patience :) DK On 9/6/06, Joseph Lamoree [EMAIL PROTECTED] wrote: On 6 Sep 2006, at 11:19, Chris Tilley wrote: Does anyone have a tested method of uploading bulk data to a database? I have a need (annually) for the admin person to be able to take data from an Excel spreadsheet and have it inserted into an Oracle database. Any sample code would be greatly appreciated. I've played with a CFC that makes Java calls but without success. You'll probably get the best performance using the Oracle SQL*Plus tools, specifically SQL*Loader: http://www.orafaq.com/faq/sql_loader -- Joseph Lamoree ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252287 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Bulk data loading
Ben Nadel wrote: Sorry to come in late in the convo... If you can put the file in a public directory, I have found from some simple testing that CFHttp is faster than a buffered file reader... http://bennadel.com/blog/200-ColdFusion-CFHttp-To-Query-Much-Faster-Than-Jav a-Buffered-Reader.htm DISCLAIMER: I am a ColdFusion programmer, not a Java programmer, so there are probably issues. Hey, that's pretty cool. Your method does work very well provided you're looking at well formatted, query like data (like a quality CSV) and the file is accessible via http. Of course, you can't do *ANY* validation on the data until after the data is loaded into the query... so that could be a disadvantage. but still that's pretty cool. Using CFHTTP to generate a query object was somethingthat I'd never really had a need for before. Rick ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252293 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Bulk data loading
Hey, that's pretty cool. Your method does work very well provided you're looking at well formatted, query like data (like a quality CSV) and the file is accessible via http. One way to get around poor quality CSV is similar to getting around poor quality WSDL(?) type stuff... grab the file, parse it locally, re-save it, and then point cfhttp at it. I have to do this for one site that has tabs and weird chars in the CSV file. Works great. I also have some code that will take a real excel file (not CSV) and import it using jexcelapi. Used it with 11000 rows so far, takes about 10seconds. Turns it into a table or a query, IIRC... and it will pull out all of the workbook spreadsheets. Been meaning to finish off my how to create a entry with a how to import one... sad, sad blog: http://loggedden.blogspot.com. Big thanks to whover wrote the coloredcode bit of blogCFC... :-) ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:252310 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
