Topic: Insert via cffile read


dlackey    -- 03-03-2008 @ 5:53 PM
  Okay, my last post on a very similar topic didn't yield any results so lets see if this does...

Does anyone know how to read from a text file so that I can insert into a tmpTable on my database?  I know how to do the insert statement, I just don't know how to read the contents from a file into memory.  

Any help would be appreicated.  

Cheers,
Chip


Webmaster    -- 03-03-2008 @ 6:49 PM
  <cffile action="read" file="PATH TO FILE" variable="theFileContents" />


now you have a variable called "theFileContents" that contains the file contents.

If you want to insert the ENTIRE file then you would do:

<cfquery .....>
  insert into table (userid, resume )
  values ( #val(userid)#, '#theFileContents#' )
</cfquery>

You can also use cfqueryparam; but that is a different matter altogether Smile

<cfquery .....>
  insert into table (userid, resume )
  values ( #val(userid)#, <cfqueryparam cfsqltype="cf_sql_longvarchar" value="#theFileContents#" /> )
</cfquery>

Does that help?

Pablo Varando
Senior Application Architect
EasyCFM.COM, LLC.

904.483.1457 \\ mobile
webmaster@easycfm.com \\email


dlackey    -- 03-04-2008 @ 11:50 AM
  I figured out the cffile read part late last night but wasn't sure that was the right direction.  Looks like I'm off to a good start.  Using CFFILE does change my thinking just a little bit though for the data insertion statements.  Originally, I envisioned reading a record from the text file, and insert that record into the table, reading the next line, inserting the next record, and so on.  I haven't seen the text file yet so I dont know exactly what it will look like, I only know the delimiters since I got to pick that.

With your example, it appears you are inserting the entire contents (theFileContents) into one column.  I'm needing to insert data into my table but there will be multiple records and multiple columns (the text file will have a column delimiter and a row delimiter).  So, I assume I will need to use a replace or a replacenocase to prepare it for the sql insert statement and loop over it with a listgetat??  I was thinking of populating an array but we could be potentially talking about 200+ records producting a multi-deminsional array (I think that is how you refer to arrays).  Would that be efficient to use memory wise?  

Cheers,
Chip


SirRawlins    -- 03-04-2008 @ 12:14 PM
  Hey Chip,

It might be worth you looking at BULK INSERT which inserts a text file content directly into a table. I use this method to load statistics data into a temp table in my application, I then use other INSERT statement to move the data from the temp table into its more perminant home.

Might be worth a looksy.

Let me know what you think.

Rob


dlackey    -- 03-04-2008 @ 12:18 PM
  that works for SQL but I'm inserting data into Oracle.  For bulk inserts with oracle, you use an external table.  With linux, you have to setup permissions and run a script which our DBA has to setup, thus slowing up my development time (real drag).  I'm hoping I might be able to get around that by looping the inserts but I also don't want to hit the server with 200+ loops, unless that isn't really a problem.  

Your input?

Cheers,
Chip


SirRawlins    -- 03-04-2008 @ 12:32 PM
  Would somthing like this work perhaps?

http://www.orafaq.com/wiki/SQL*Loader_FAQ

Rob


dlackey    -- 03-04-2008 @ 3:40 PM
  Excellent find but that is what we are using right now.  The client will send the file to us and then we process the file.  Our team is trying to develop a method where the client can perform their own update by uploading the file they would normally send to us through their web site.  We are trying to allow the client to administer their web site without us intervening as much as possible.  They have to submit a request which can take some time for us to get to whereas if they can update it themselves, then the updates are immediate.  Smile

Cheers,
Chip


EasyCFM.COM ColdFusion Forums : http://archive.easycfm.com/forums
Topic: http://archive.easycfm.com/forums/viewmessages.cfm?Forum=33&Topic=13218