Merging two lists?

[giebelsteins]

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.

[ilssac]

Well at the most basic lists are just strings with delimiters.  You can combine two lists by simply concatinating the two strings.

But, it sounds like you would also like to remove duplicates.  That will require some function.  There is not one built into ColdFusion.  You will need to build your own, or get one somebody else has already built.

http://www.cflib.org/ is a great place to find lots and lots of functions built by others for just about every type of requirement.

I.E.

ListUnion(List1, List2 [, Delim1] [, Delim2] [, Delim3] [, SortType] [, SortOrder])

http://www.cflib.org/index.cfm?event=page.udfbyid&udfid=371

Description:
Combines two lists, automatically removing duplicate values.  Allows  for optional delimiters for all lists.  Also allows for optional sort  type and sort order.

[Dan_Bracuk]

Lists are simply delimited strings.  Once you start talking about fields and records, maybe you are not talking about lists.  Just for fun, invent some sample data and show us the desired result.

[giebelsteins]

Let me be more clear on this.  I am getting a list of names addresses and such which gets uploaded into the system to be used for mailing and identification purposes.  Then later we might get a second list with primarily the same information however this list may include additional records, possibly fewer records, and have additional information that will be used as a sort field to distribute a product.  Is there a way to take these two lists and make them one without losing data from the original and matching data from both lists?  Again, thanks in advance.

[Dan_Bracuk]

Whether or not it's possible, and if so, how to do it, depends on what these lists look like.

[giebelsteins]

The list is usually uploaded in eithera txt, csv, or excel format.  Then it is inserted into a sql table.

[Owainnorth]

As per Dan's point, I think it's still not clear what's in these lists - I initially assumed it was a simple list:

john,barry,steve,phil

But you say the list may contain different information and fields? Which would imply it's more like:

john,41,London,mate

barry,24,Egypt,idiot

steve,63,France,whingebag

Either way if you're going to be changing fields periodically then I don't really see any other option than to write a big ol' custom function that loops through the list/file, does SELECT statements against the database to see if a match is found using your given criteria, and then does an UPDATE or an INSERT depending on its findings.

If you don't want to do this in CF, just insert the new data into a separate uploads table and write yourself a stored procedure to do a similar task.

Unless I've massively misunderstood?

O.

[giebelsteins]

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.

[Owainnorth · Accepted solution]

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.

[JR__Bob__Dobbs-qSBHQ2]

I think Owainnorth makes some good points.  I'll add that you might consider moving the duplicate removal process/logic off of ColdFusion and onto your database or a business logic server. What database (vendor and version) are you using?

[giebelsteins]

I think your right.  In a perfect world where all things are similar I dont think this would be a problem however, with the posibility of name changes or address changes such as boulavard and blvd., street and st and so on.  This may be more trouble than it is worth.  Thanks, for all your help.

[TLC-IT]

ColdFusion has a good set of operators for handling lists (i.e. comma-delimeted strings), arrays, and structures (i.e. hashes).

One strategy that I like to use for merging and de-duping things is to put all of the strings as keys to a structure, assigning each element a dummy don't-care value.  Then, extract the list of keys from the structure.

Truly, this is one of those situations where "any reasonably-clear implementation will do."