EasyCFM.COM ColdFusion Forums / Oracle Related Issues / Insert via cffile read

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Insert via cffile read

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

dlackey
03-03-2008 @ 5:53 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

<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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 951
Joined: Mar 2006

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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 951
Joined: Mar 2006

Would somthing like this work perhaps?

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

Rob

dlackey
03-04-2008 @ 3:40 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

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


Website Designed and Developed by Pablo Varando.