Known Participant

Answered

CF File Action Write Help

Forum|Forum|4 years ago
September 20, 2021
2 replies
1040 views

Hello everyone,

I have a cfm file that I use to upload a csv document every morning to a database. Recently I came across a problem when it was using the first name column A instead of the prefered name column F. The output file looks like this:

My question is since I already have the csv set up with First Name A, Last name B etc is it possible for me to just change the code to this and it should apply that edit to show the joint AC first instead of the frist name during the upload?

Any help would be greatly appreciated.

Thanks,

This topic has been closed for replies.

Correct answer George____

I just made the edit to that line and added both Joint into both fields like this:

CASE WHEN UPPER(tblStudents.NAME_TAG) <> UPPER(tblStudents.Joint A/C holder) THEN tblStudents.NAME_TAG ELSE tblStudents.Joint A/C holder END +

For this line: SELECT DISTINCT RTRIM(tblStudents.FirstName + ' ' + ISNULL(LEFT(tblStudents.MI, 1),'')) + ',' +

I dont need to change first name there I can keep it as is?

If you wanted to swap the columns then your query should have changed to the below. By changing the case statement you changed the value of the Joint A/C holder field and still left it as the sixth column, which isn't what I thought you wanted to do.

<cfquery datasource="#connection_string#" name="qry">
SELECT DISTINCT 
CASE WHEN UPPER(tblStudents.NAME_TAG) <> UPPER(tblStudents.FirstName) THEN tblStudents.NAME_TAG ELSE tblStudents.FirstName END +
',' +
tblStudents.LastName + ',' +
IsNull(tblStudents.Email,'') + ',' +
tblStudents.BoxNumber + ',' +
tblStudents.StudentNumber + ',' +
RTRIM(tblStudents.FirstName + ' ' + ISNULL(LEFT(tblStudents.MI, 1),'')) +
',,,,,,,,,,Student,' +
CASE WHEN LEN(IsNull(tblStudents.email,'')) = 0 THEN 'No' ELSE 'Yes' END + ',' +
'No'+ ',Yes,' AS DataLine

BKBK

Community Expert

The output file looks like this:

<cffile action="write" file="#export_file#" output="First Name,Last Name,Email Id,Mailbox Id,Internal ID,Joint A/C holder,Company/Department,Address1,Address2,City,State,Postal code/ Zip Code,Country(ISO Code),Phone Number,Mobile,Recipient Type,Email Notification ?,SMS Notification ?,Active,Notes"/>

My question is since I already have the csv set up with First Name A, Last name B etc is it possible for me to just change the code to this and it should apply that edit to show the joint AC first instead of the frist name during the upload?

<cffile action="write" file="#export_file#" output="Joint A/C holder,Last Name,Email Id,Mailbox Id,Internal ID,First Name,Company/Department,Address1,Address2,City,State,Postal code/ Zip Code,Country(ISO Code),Phone Number,Mobile,Recipient Type,Email Notification ?,SMS Notification ?,Active,Notes"/>Any help would be greatly appreciated.

By @raelteh

<!--- The CSV file may already have rows of data.  --->
<!--- We wish to replace just the header.  --->
<cfset oldCsvHeader="First Name,Last Name,Email Id,Mailbox Id,Internal ID,Joint A/C holder,Company/Department,Address1,Address2,City,State,Postal code/ Zip Code,Country(ISO Code),Phone Number,Mobile,Recipient Type,Email Notification ?,SMS Notification ?,Active,Notes">
<cfset newCsvHeader="Joint A/C holder,Last Name,Email Id,Mailbox Id,Internal ID,First Name,Company/Department,Address1,Address2,City,State,Postal code/ Zip Code,Country(ISO Code),Phone Number,Mobile,Recipient Type,Email Notification ?,SMS Notification ?,Active,Notes">

<!--- Use a named lock to avoid race-conditions between file-read and file-write. --->
<!--- Read the file content and replace the header. --->
<cflock timeout="3" name="exportFileLock">
	<cffile action="read" file="#export_file#" variable="exportFile" >
	<cfset export_file_revised=replace(exportFile,oldCsvHeader,newCsvHeader)>
</cflock>

<!--- Write the revised content to file. --->
<cflock timeout="3" name="exportFileLock">
	<cffile action="write" file="#export_file#" output="#export_file_revised#"/>
</cflock>

BKBK

Community Expert

Let us suppose then that your CSV file has some rows of data. Take the following CSV file, for example:

Joint A/C holder,Last Name,Email Id,Mailbox Id,Internal ID,First Name,Company/Department,Address1,Address2,City,State,Postal code/ Zip Code,Country(ISO Code),Phone Number,Mobile,Recipient Type,Email Notification ?,SMS Notification ?,Active,Notes
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T

We have successfully switched the Joint A/C holder and First Name header columns using the code I gave earlier. I shall now propose a way to switch the first and sixth elements of each data-row. Without the use of a query.

<!--- Use a named lock to avoid race-conditions between file-read and file-write. --->
<!--- Read the file content. --->
<cflock timeout="5" name="exportFileLock">
	<cffile action="read" file="#export_file#" variable="exportFile" >


<!--- Treat the file content as a list consisting of lines. --->
<!--- Leave the first line intact, as it is just the header. --->
<!--- Treat each line in turn as a comma-delimited list. --->
<!--- Loop from line 2 to the last line, switching the first and sixth list elements. --->
<cfset numberOfDatalines=listLen(trim(exportFile),"#chr(13)##chr(10)#")>

<cfif numberOfDatalines GT 1>
	<cfloop from="2" to="#numberOfDatalines#" index="lineNumber">
	
		<cfset line=listGetAt(trim(exportFile),lineNumber,"#chr(13)##chr(10)#")>
	
		<!--- Each line is a comma-delimited list. Get the first and sixth elements. --->
		<cfset firstItemOnLine=listGetAt(line,1)>
		<cfset sixthItemOnLine=listGetAt(line,6)>
		
		
		<!--- Re-insert the first and sixth elements, switching positions --->
		<cfset line=listSetAt(line,1,sixthItemOnLine)>
		<cfset line=listSetAt(line,6,firstItemOnLine)>
	
		<!--- Re-insert the line, after the switch. --->
		<cfset exportFile=listSetAt(trim(exportFile),lineNumber, line, "#chr(13)##chr(10)#")>	
	</cfloop>
</cfif>
</cflock>

<!--- Write the revised content to file. --->
<cflock timeout="2" name="exportFileLock">
	<cffile action="write" file="#export_file#" output="#exportFile#"/>
</cflock>

If you use the test CSV as example, you will see that this code switches the positions of 1 and 6 in the second line, and of A and F in the third.

In fact you could ignore my earlier code and include the "header switch" in the present solution.

George____

Inspiring

In your example, what's in the output attribute is exactly what is being wrote into the file, and doesn't involve the file they upload. Most likey that line is only intended to write the column headers to a new file and further down is where you're populating the information from the uploaded file into the new exported file. So you can swamp "Joint A/C holder" wiht "First Name" on that line, but below that where you're pulling in the data you also need to make the swap.

R

raeltehAuthor

Known Participant

When you say below that where i'm pulling in the data where would I make the swap? when I checked the code I don't see any other fields prior to the one i'm editing that would have First Name or Joint A/C in them. There is also a FirstName field as one word but I think its uploading via First Name and not FirstName in the csv. This is what I have under the initial code I changed.

<cfoutput>Building file content (#export_file#)<BR></cfoutput>

<cffile action="write" file="#export_file#" output="Joint A/C holder,Last Name,Email Id,Mailbox Id,Internal ID,First Name,Company/Department,Address1,Address2,City,State,Postal code/ Zip Code,Country(ISO Code),Phone Number,Mobile,Recipient Type,Email Notification ?,SMS Notification ?,Active,Notes"/>

<cfloop query="qry">
<cffile action="append" file="#export_file#" output="#qry.DataLine#"/>
</cfloop>

Sending file content<BR>

<cfhttp url="#baseurl#/api/recipients/upload" method="post" result="stage_0_uploadresult" >
<cfhttpparam type="header" name="Authorization" value="Bearer #accessToken#" />
<cfhttpparam type="file" name="recipientFile" file="#export_file#" />
<cfhttpparam type="FormField" name="stage" value="0" />
<cfhttpparam type="FormField" name="updateAll" value="1" />
</cfhttp>


<cfdump var="#stage_0_uploadresult#" label="CFHTTP stage_0_uploadresult" format="html" output="#expandPath('stage_0_uploadresult.html')#">

<cfset result = deserializeJSON(stage_0_uploadresult.filecontent) />
<cfset uploadID = result["upload-id"]>

George____

Inspiring

This is where you're writing the data into the file

<cfloop query="qry">
<cffile action="append" file="#export_file#" output="#qry.DataLine#"/>
</cfloop>

Somewhere above that is where you do the cfquery to generate the "qry" variable. You need to modify the select in that query to swap the "Joint A/C holder" and "First Name" values.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded