| Posted By |
Discussion Topic: Insert via cffile read
|
|
dlackey |
03-03-2008 @ 5:53 PM |
|
|
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 |
|
|
Administrator
Posts: 4533
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 
<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 |
|
|
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 |
|
|
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 |
|
|
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 |
|
|
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 |
|
|
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.
Cheers,
Chip
|
