I need to know if it is possible to merge two lists using coldfusion. I have one list uploaded to the site then they want the ability to upload a second list with may have additional fields and/or different number of records. Need to know if this is possible, how to do it if it is, and how the two list will match up. Thanks in advance for the help.
no you are not misunderstanding. the list we recieve will be...
John Doe 123 anywhere city state zip
Jane Dont 231 somewhere city state zip
blah
blah
blah
the second list we get may be like this..
John Doe 123 anywhere city state zip homeroom gradelevel
Jane Dont 231 somewhere city state zip homeroom gradelevel
blah
blah
blah
steve stone 245 uptown city state zip homeroom gradelevel
or the possibility of name variations like John Doe being changed to Jon Doe or Johnny Doe but being the same person
maybe that will clear it up.
Right, well in that case this isn't really anything to do with merging lists per se, hence the confusion I believe. Technically, you'd do this:
<cfset newData = fileOpen("c:\newlist.csv","read") />
<!--- Loop through your file, one line at a time --->
<cfloop condition="NOT fileIsEof(newData)">
<!--- Create a string of the line, this is a true list --->
<cfset thisLine = fileReadLine(newData) />
<!--- Get the elements of the list we want for the query --->
<cfset thisForename = listGetAt(thisLine,1) />
<cfset thisSurname = listGetAt(thisLine,2) />
<!--- Check if this person exists --->
<cfquery datasource/username/password name="qCheckExists">
SELECT id
FROM mytable
WHERE firstname = <cfqueryparam cfsqltype="cf_sql_varchar" value="#thisForename#" />
AND surname = <cfqueryparam cfsqltype="cf_sql_varchar" value="#thisSurname"#" />
</cfquery>
<!--- If we found rows, update --->
<cfif qCheckExists.RECORDCOUNT eq 1 >
<cfquery datasource/username/password>
UPDATE mytable
SET forename = <listGetAt,thisLine, FIELD>,
surname = <listGetAt,thisLine, FIELD>,
address = <listGetAt,thisLine, FIELD>,
etc etc etc
WHERE id = qCheckExists.id
</cfquery>
<cfelseif qCheckExists.RECORDCOUNT >
<!--- here you found more than one matching row, up to you what to do --->
<cfelse>
<!--- here you didn't find a match, so do an insert --->
</cfif>
</cfloop>
<cfset fileClose(newData) />
That's how you'd do it on a technical level, the Business decisions are yours. What if it's John not Jon? What if it's Jonathan? That's not a programming problem, that's up to you to decide - I'm not sure we can help you there.
O.
Already have an account? Login
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
AdChoices