| Posted By |
Discussion Topic: New to Coldfusion Need some help
|
|
arthurclark |
05-05-2005 @ 1:12 PM |
|
|
Senior Member
Posts: 166
Joined: May 2005
|
Hi
I am looking for a solution to my current challenge. I have a CSV file that must be imported into a database on a regular basis.
I can get the CSV to import and to update but not do both. The problem is that once the CSV is imported to the database this code will populate the database with the same information that currently exists in the database. What I need it to do is to update existing records and insert new records.
Here is a sample of the code that I am using. Any suggestions and or help is very much appreciated.
<!--- then with your process.cfm file (example name only) write the file onto a temporary area of the server: --->
<cffile action="UPLOAD"
filefield="fileupload"
destination="c:\temp\mydatafile.csv"
nameconflict="Overwrite">
<!--- Set the Record Count --->
<cfset end_of_line = Chr(10)>
<!--- <cfset current_count = #current_count# + 1> --->
<!--- Then you want to read the contents of the file into a variable --->
<cffile action="READ"
file="c:\temp\mydatafile.csv"
variable="DataFile">
<!--- Remove all single quotes --->
<cfset clean_data_file = Replace(DataFile, "'", " ")>
<!--- Put the list into an Array --->
<cfset data_line_array = ListToArray(clean_data_file, end_of_line)>
<!--- Count the number of Arrays --->
<cfset arraycount = #ArrayLen(data_line_array)#>
<!--- Set the loop to find and set each field element(in this case 6 elements)in the array
and insert each into the database table (Named test) --->
<cfloop index ="x" from="1" to="#arraycount#">
<cfset data_element_array = ListToArray(Data_Line_Array[x])>
<cfset elementarraycount = #ArrayLen(Data_Element_Array)#>
<!--- Set the fielddata(column) to an element in the array --->
<cfset tmp_element_1 = '#data_element_array[1]#'>
<!--- all the element in this demo are text string and will
need single quotes around them in order to insert into the database --->
<cfset element_1 = '#PreserveSingleQuotes(tmp_element_1)#'>
<cfset tmp_element_2 = '#data_element_array[2]#'>
<cfset element_2 = '#PreserveSingleQuotes(tmp_element_2)#'>
<cfset tmp_element_3 = '#data_element_array[3]#'>
<cfset element_3 = '#PreserveSingleQuotes(tmp_element_3)#'>
<cfset tmp_element_4 = '#data_element_array[4]#'>
<!--- An unprintable character is appearing on the end of these strings.
Use RemoveChars to remove it --->
<cfset new_element_4 = RemoveChars(tmp_element_4, len(tmp_element_4), 1)>
<cfset element_4 = '#PreserveSingleQuotes(new_element_4)#'>
<CFQUERY datasource="Inventory2" name="Checkdata">
Select *
From test
</CFQUERY>
<CFIF IsDefined('#tmp_element_1#') AND ('#tmp_element_1#' NEQ '#Checkdata.ID#')>
<cfquery datasource="Inventory2" name="addMyData">
INSERT INTO test
(ID, Username, Password, Department)
VALUES('#tmp_element_1#', '#tmp_element_2#','#tmp_element_3#', '#tmp_element_4#')
</cfquery>
<CFELSE>
<cfquery name="Update2" datasource="Inventory2">
UPDATE test
SET
Username='#tmp_element_2#',
Password='#tmp_element_3#',
Department='#tmp_element_4#'
Where ID = '#tmp_element_1#'
</cfquery>
</CFIF>
<CFIF ('#tmp_element_1#' NEQ '#Checkdata.ID#')>
<cfquery datasource="Inventory2" name="addMyData">
INSERT INTO test
(ID, Username, Password, Department)
VALUES('#tmp_element_1#', '#tmp_element_2#','#tmp_element_3#', '#tmp_element_4#')
WHERE ID; <> '#tmp_element_1#'
</cfquery>
</CFIF>
</cfloop>
<!---Print out the Confirmation that the data has been uploaded and inserted into the database -à--->
<cfquery datasource="Inventory2" name="checkData">
Select *
from test
</cfquery>
<cfoutput>
<cfif #checkData.recordcount# is "0">
An error has occurred in the data upload! Please check the code!
<cfelse>
Your data has been successfully loaded
</cfif>
</cfoutput>
Arthur Clark
|
|
jamin |
05-05-2005 @ 1:26 PM |
|
|
Junior Member
Posts: 100
Joined: May 2005
|
gridname.RowStatus.Action [change_index]
Stores the edit type made to the edited grid row: D for delete, I for insert, or U for update.
I do basicly the same thing, I make a CFGRID populated from the CVS inside a form that submits to a control document, with code like this.
<cfquery name="rc_lot_info" datasource="cfSQL_dc_001">
SELECT LOT_CID, LOT_NUMBER, LOT_TMS, LOT_ADDRESS,BOUND_PHASE_LIST,LOT_PREMIUM,LOT_COUNTY
FROM dbo.LOT_INFO</cfquery>
<!-- cfgridupdate grid="BasicLotInfo" datasource="cfSQL_dc_001" tablename="LOT_INFO" -->
<html>
<head>
<title>Catch submitted grid values</title>
</head>
<body>
<cfif isdefined("Form.BasicLotInfo.rowstatus.action")>
<cfloop index = "Counter" from = "1" to = #arraylen(Form.BasicLotInfo.rowstatus.action)#>
<cfoutput>
Success!
#Counter# status:#Form.BasicLotInfo.rowstatus.action[Counter]#
<br>
</cfoutput>
<cfif Form.BasicLotInfo.rowstatus.action[counter] is "D">
<cfquery name="DeleteExistingEmployee" datasource="cfSQL_dc_001">
DELETE FROM dbo.LOT_INFO
WHERE
LOT_CID=#Form.BasicLotInfo.original.LOT_CID[Counter]#
</cfquery>
<cfelseif Form.BasicLotInfo.rowstatus.action[counter] is "U">
<cfquery name="UpdateExistingEmployee" datasource="cfSQL_dc_001">
UPDATE dbo.LOT_INFO
SET
LOT_NUMBER='#Form.BasicLotInfo.LOT_NUMBER[Counter]#',
LOT_TMS='#Form.BasicLotInfo.LOT_TMS[Counter]#',
LOT_ADDRESS='#Form.BasicLotInfo.LOT_ADDRESS[Counter]#',
BOUND_PHASE_LIST='#Form.BasicLotInfo.BOUND_PHASE_LIST[Counter]#',
LOT_PREMIUM='#Form.BasicLotInfo.LOT_PREMIUM[Counter]#',
LOT_COUNTY='#Form.BasicLotInfo.LOT_COUNTY[Counter]#'
WHERE
LOT_CID=#Form.BasicLotInfo.original.LOT_CID[Counter]#
</cfquery>
<cfelseif Form.BasicLotInfo.rowstatus.action[counter] is "I">
<cfquery name="InsertNewEmployee"
datasource="cfSQL_dc_001">
INSERT into dbo.LOT_INFO
(LOT_NUMBER, LOT_TMS, LOT_ADDRESS,BOUND_PHASE_LIST,LOT_PREMIUM,LOT_COUNTY)
VALUES ('#Form.BasicLotInfo.LOT_NUMBER[Counter]#',
'#Form.BasicLotInfo.LOT_TMS[Counter]#',
'#Form.BasicLotInfo.LOT_ADDRESS[Counter]#',
'#Form.BasicLotInfo.BOUND_PHASE_LIST[Counter]#',
'#Form.BasicLotInfo.LOT_PREMIUM[Counter]#',
'#Form.BasicLotInfo.LOT_COUNTY[Counter]#' )
</cfquery>
</cfif>
</cfloop>
</cfif>
with a little clean up you could modify this to help you out I think.
|
|
arthurclark |
|
05-05-2005 @ 2:01 PM |
|
|
Senior Member
Posts: 166
Joined: May 2005
|
I can get it to update with this code but I can not get it to insert a new record out of the CSV file. I looked at your code but it confused me like I said I am just a patawan. If you could break it down just a bit for me that would be awesome.
<!--- then with your process.cfm file (example name only) write the file onto a temporary area of the server: --->
<cffile action="UPLOAD"
filefield="fileupload"
destination="c:\temp\mydatafile.csv"
nameconflict="Overwrite">
<!--- Set the Record Count --->
<cfset end_of_line = Chr(10)>
<!--- <cfset current_count = #current_count# + 1> --->
<!--- Then you want to read the contents of the file into a variable --->
<cffile action="READ"
file="c:\temp\mydatafile.csv"
variable="DataFile">
<!--- Remove all single quotes --->
<cfset clean_data_file = Replace(DataFile, "'", " ")>
<!--- Put the list into an Array --->
<cfset data_line_array = ListToArray(clean_data_file, end_of_line)>
<!--- Count the number of Arrays --->
<cfset arraycount = #ArrayLen(data_line_array)#>
<!--- Set the loop to find and set each field element(in this case 6 elements)in the array
and insert each into the database table (Named test) --->
<cfloop index ="x" from="1" to="#arraycount#">
<cfset data_element_array = ListToArray(Data_Line_Array[x])>
<cfset elementarraycount = #ArrayLen(Data_Element_Array)#>
<!--- Set the fielddata(column) to an element in the array --->
<!---
This is the bulk of where you can change this file to your liking and continue to work with this same code. The data_element_array[1] through data_element_array[whatever] will enable you to continue to work with this file. The above code is kind of generic and will allow you to loop through the comma delimited files with the removal of particular different elements as well as add single quotes to your veriables in your tables. --->
<cfset tmp_element_1 = '#data_element_array[1]#'>
<!--- all the element in this demo are text string and will
need single quotes around them in order to insert into the database --->
<cfset element_1 = '#PreserveSingleQuotes(tmp_element_1)#'>
<cfset tmp_element_2 = '#data_element_array[2]#'>
<cfset element_2 = '#PreserveSingleQuotes(tmp_element_2)#'>
<cfset tmp_element_3 = '#data_element_array[3]#'>
<cfset element_3 = '#PreserveSingleQuotes(tmp_element_3)#'>
<cfset tmp_element_4 = '#data_element_array[4]#'>
<!--- An unprintable character is appearing on the end of these strings.
Use RemoveChars to remove it --->
<cfset new_element_4 = RemoveChars(tmp_element_4, len(tmp_element_4), 1)>
<cfset element_4 = '#PreserveSingleQuotes(new_element_4)#'>
<CFQUERY datasource="Inventory2" name="Checkdata">
Select *
From test
</CFQUERY>
<CFIF IsDefined('#tmp_element_1#') AND ('#tmp_element_1#' NEQ '#Checkdata.ID#')>
<cfquery datasource="Inventory2" name="addMyData">
INSERT INTO test
(ID, Username, Password, Department)
VALUES('#tmp_element_1#', '#tmp_element_2#','#tmp_element_3#', '#tmp_element_4#')
</cfquery>
<CFELSE>
<cfquery name="Update2" datasource="Inventory2">
UPDATE test
SET
Username='#tmp_element_2#',
Password='#tmp_element_3#',
Department='#tmp_element_4#'
Where ID = '#tmp_element_1#'
</cfquery>
</CFIF>
<!--- <CFIF ('#Checkdata.ID#' NEQ '#tmp_element_1#')>
<cfquery datasource="Inventory2" name="addMyData">
INSERT INTO test
(ID, Username, Password, Department)
VALUES('#tmp_element_1#', '#tmp_element_2#','#tmp_element_3#', '#tmp_element_4#')
</cfquery>
</CFIF> --->
</cfloop>
<!---
<CFIF NOT IsDefined('#Checkdata.Test_ID#')>
<cfquery datasource="Inventory2" name="addMyData">
INSERT INTO test
(ID, Username, Password, Department)
VALUES('#tmp_element_1#', '#tmp_element_2#','#tmp_element_3#', '#tmp_element_4#')
</cfquery>
</CFIF> --->
<!---Print out the Confirmation that the data has been uploaded and inserted into the database -à--->
<cfquery datasource="Inventory2" name="checkData">
Select *
from test
</cfquery>
<cfoutput>
<cfif #checkData.recordcount# is "0">
An error has occurred in the data upload! Please check the code!
<cfelse>
Your data has been successfully loaded
</cfif>
</cfoutput>
Arthur Clark
|
|
arthurclark |
|
05-05-2005 @ 2:01 PM |
|
|
Senior Member
Posts: 166
Joined: May 2005
|
I can get it to update with this code but I can not get it to insert a new record out of the CSV file. I looked at your code but it confused me like I said I am just a patawan. If you could break it down just a bit for me that would be awesome.
<!--- then with your process.cfm file (example name only) write the file onto a temporary area of the server: --->
<cffile action="UPLOAD"
filefield="fileupload"
destination="c:\temp\mydatafile.csv"
nameconflict="Overwrite">
<!--- Set the Record Count --->
<cfset end_of_line = Chr(10)>
<!--- <cfset current_count = #current_count# + 1> --->
<!--- Then you want to read the contents of the file into a variable --->
<cffile action="READ"
file="c:\temp\mydatafile.csv"
variable="DataFile">
<!--- Remove all single quotes --->
<cfset clean_data_file = Replace(DataFile, "'", " ")>
<!--- Put the list into an Array --->
<cfset data_line_array = ListToArray(clean_data_file, end_of_line)>
<!--- Count the number of Arrays --->
<cfset arraycount = #ArrayLen(data_line_array)#>
<!--- Set the loop to find and set each field element(in this case 6 elements)in the array
and insert each into the database table (Named test) --->
<cfloop index ="x" from="1" to="#arraycount#">
<cfset data_element_array = ListToArray(Data_Line_Array[x])>
<cfset elementarraycount = #ArrayLen(Data_Element_Array)#>
<!--- Set the fielddata(column) to an element in the array --->
<!---
This is the bulk of where you can change this file to your liking and continue to work with this same code. The data_element_array[1] through data_element_array[whatever] will enable you to continue to work with this file. The above code is kind of generic and will allow you to loop through the comma delimited files with the removal of particular different elements as well as add single quotes to your veriables in your tables. --->
<cfset tmp_element_1 = '#data_element_array[1]#'>
<!--- all the element in this demo are text string and will
need single quotes around them in order to insert into the database --->
<cfset element_1 = '#PreserveSingleQuotes(tmp_element_1)#'>
<cfset tmp_element_2 = '#data_element_array[2]#'>
<cfset element_2 = '#PreserveSingleQuotes(tmp_element_2)#'>
<cfset tmp_element_3 = '#data_element_array[3]#'>
<cfset element_3 = '#PreserveSingleQuotes(tmp_element_3)#'>
<cfset tmp_element_4 = '#data_element_array[4]#'>
<!--- An unprintable character is appearing on the end of these strings.
Use RemoveChars to remove it --->
<cfset new_element_4 = RemoveChars(tmp_element_4, len(tmp_element_4), 1)>
<cfset element_4 = '#PreserveSingleQuotes(new_element_4)#'>
<CFQUERY datasource="Inventory2" name="Checkdata">
Select *
From test
</CFQUERY>
<CFIF IsDefined('#tmp_element_1#') AND ('#tmp_element_1#' NEQ '#Checkdata.ID#')>
<cfquery datasource="Inventory2" name="addMyData">
INSERT INTO test
(ID, Username, Password, Department)
VALUES('#tmp_element_1#', '#tmp_element_2#','#tmp_element_3#', '#tmp_element_4#')
</cfquery>
<CFELSE>
<cfquery name="Update2" datasource="Inventory2">
UPDATE test
SET
Username='#tmp_element_2#',
Password='#tmp_element_3#',
Department='#tmp_element_4#'
Where ID = '#tmp_element_1#'
</cfquery>
</CFIF>
<!--- <CFIF ('#Checkdata.ID#' NEQ '#tmp_element_1#')>
<cfquery datasource="Inventory2" name="addMyData">
INSERT INTO test
(ID, Username, Password, Department)
VALUES('#tmp_element_1#', '#tmp_element_2#','#tmp_element_3#', '#tmp_element_4#')
</cfquery>
</CFIF> --->
</cfloop>
<!---
<CFIF NOT IsDefined('#Checkdata.Test_ID#')>
<cfquery datasource="Inventory2" name="addMyData">
INSERT INTO test
(ID, Username, Password, Department)
VALUES('#tmp_element_1#', '#tmp_element_2#','#tmp_element_3#', '#tmp_element_4#')
</cfquery>
</CFIF> --->
<!---Print out the Confirmation that the data has been uploaded and inserted into the database -à--->
<cfquery datasource="Inventory2" name="checkData">
Select *
from test
</cfquery>
<cfoutput>
<cfif #checkData.recordcount# is "0">
An error has occurred in the data upload! Please check the code!
<cfelse>
Your data has been successfully loaded
</cfif>
</cfoutput>
Arthur Clark
|
|
jamin |
|
05-05-2005 @ 2:58 PM |
|
|
Junior Member
Posts: 100
Joined: May 2005
|
``````````````````Get CVS FILE
<CFHTTP URL="http://www.myserver.com/news/log.txt" METHOD="GET"
NAME="newslog" COLUMNS="pcat,lnum,ptime,pip" DELIMITER="|"
RESOLVEURL="false" FIRSTROWASHEADERS="no">
````````````````or get it like this
<cfquery name="getInfo" datasource="myDatasource" dbtype="text/html">
SELECT * FROM filename.txt
</cfquery>
then put it in a grid.
<cfform name="submittodb" action="">
<CFGRID QUERY="newslog">
</CFGRID></cfform>
|
|
jamin |
|
05-05-2005 @ 3:02 PM |
|
|
Junior Member
Posts: 100
Joined: May 2005
|
then submit it. to database.
Update Line 1 = Line 1
if Line 1 EOF or BOF then Insert Line 1.
This is where a SQL stored procedure could come in handy, or you could do it all in ColdFusion...
if there has been changes to the CFGrid.
Google this!
ColdFusion rowstatus.action[counter]
|
|
arthurclark |
|
05-05-2005 @ 5:34 PM |
|
|
Senior Member
Posts: 166
Joined: May 2005
|
OK I get where you are going with the CFGRID but how do I take and pass the informatoin to a grid on the same page.
I am working with this set of code after taking a look at your advise and I can get the records to update but in insert statment continues to insert the last line of text from the CSV flie over and over.
NOt sure why Thanks again for your help.
<!--- then with your process.cfm file (example name only) write the file onto a temporary area of the server: --->
<cffile action="UPLOAD"
filefield="fileupload"
destination="c:\temp\mydatafile.csv"
nameconflict="Overwrite">
<!--- Set the Record Count --->
<cfset end_of_line = Chr(10)>
<!--- <cfset current_count = #current_count# + 1> --->
<!--- Then you want to read the contents of the file into a variable --->
<cffile action="READ"
file="c:\temp\mydatafile.csv"
variable="DataFile">
<!--- Remove all single quotes --->
<cfset clean_data_file = Replace(DataFile, "'", " ")>
<!--- Put the list into an Array --->
<cfset data_line_array = ListToArray(clean_data_file, end_of_line)>
<!--- Count the number of Arrays --->
<cfset arraycount = #ArrayLen(data_line_array)#>
<!--- Set the loop to find and set each field element(in this case 6 elements)in the array
and insert each into the database table (Named test) --->
<cfloop index ="x" from="1" to="#arraycount#">
<cfset data_element_array = ListToArray(Data_Line_Array[x])>
<cfset elementarraycount = #ArrayLen(Data_Element_Array)#>
<!--- Set the fielddata(column) to an element in the array --->
<!---
This is the bulk of where you can change this file to your liking and continue to work with this same code. The data_element_array[1] through data_element_array[whatever] will enable you to continue to work with this file. The above code is kind of generic and will allow you to loop through the comma delimited files with the removal of particular different elements as well as add single quotes to your veriables in your tables. --->
<cfset tmp_element_1 = '#data_element_array[1]#'>
<!--- all the element in this demo are text string and will
need single quotes around them in order to insert into the database --->
<cfset element_1 = '#PreserveSingleQuotes(tmp_element_1)#'>
<cfset tmp_element_2 = '#data_element_array[2]#'>
<cfset element_2 = '#PreserveSingleQuotes(tmp_element_2)#'>
<cfset tmp_element_3 = '#data_element_array[3]#'>
<cfset element_3 = '#PreserveSingleQuotes(tmp_element_3)#'>
<cfset tmp_element_4 = '#data_element_array[4]#'>
<!--- An unprintable character is appearing on the end of these strings.
Use RemoveChars to remove it --->
<cfset new_element_4 = RemoveChars(tmp_element_4, len(tmp_element_4), 1)>
<cfset element_4 = '#PreserveSingleQuotes(new_element_4)#'>
<CFQUERY datasource="Inventory2" name="Checkdata">
Select *
From test
</CFQUERY>
<cfquery name="Update2" datasource="Inventory2">
UPDATE test
SET
Username='#tmp_element_2#',
Password='#tmp_element_3#',
Department='#tmp_element_4#'
Where ID = '#tmp_element_1#'
</cfquery>
</cfloop>
<cfif #checkData.recordcount# is "0" OR ('#tmp_element_1#' NEQ '#Checkdata.ID#')>
<cfquery datasource="Inventory2" name="addMyData">
INSERT INTO test
(ID, Username, Password, Department)
VALUES('#tmp_element_1#', '#tmp_element_2#','#tmp_element_3#', '#tmp_element_4#')
</cfquery>
</CFIF>
<!---Print out the Confirmation that the data has been uploaded and inserted into the database -à--->
<cfquery datasource="Inventory2" name="checkData">
Select *
from test
</cfquery>
<cfoutput>
<cfif #checkData.recordcount# is "0">
An error has occurred in the data upload! Please check the code!
<cfelse>
Your data has been successfully loaded
</cfif>
</cfoutput><br>
<br>
<CFOUTPUT>#checkData.recordcount#</CFOUTPUT>
Arthur Clark
|
|
