Re: scripting / data problem.

On 11/09/2009 03:31 AM, Hal Burgiss wrote:
On Sun, Nov 08, 2009 at 11:41:34PM -0800, Patton Echols wrote:



file 1 - | email |
file 2 - | email | f-name | l-name |
file 3 - | f-name | l-name | company | phone |
file 4 - | company | address | city | state |

(By the way, each one has some duplicates with less than complete info.)

What we need is to be able to populate all of these files with all the
available data for each contact. The concept I have in mind is to
compare the files in pairs. So file 1 would go into file-L (for Last)
Then file 1 would be compared with each line of file-L and if there is a
record with matching email, then any extra info is added , but not
overwriting anything that is already in any field So the final file
would look like this:


[...]


I'll vote for the mysql approach. Create a csv for each file, import that into
mysql so you have 4 tables. Then create a query that will dump out the data
the way you want. You can dump that into another table or export it. For this
to work, the fields will have to match in each table. So Comany in file3 is
equivalent to Comany in file4 (not something like Acme, Inc vs Acme,
Incorporated).


Do you mean that all the field data must match? Or just, for example,
the email fields? Because part of the problem is that I have non
matching / missing data depending on file.


In order for the relations to work across all 4 files, the fields that are
being keyed on, *should* match exactly. You can get around the missing data by
using a 'left join' syntax.


Ok, I'll read up on that syntax. Since the point of the exercise is to
merge records where there is overlap AND no conflicting data.



SELECT a.email, b.firstname, b.lastname, c.company, d.address FROM file1 a,
file2 b, file3 c, file4 d WHERE a.email = b.email and
concat(b.firstname,b.lastname) = concat(c.firstname,c.lastname) and c.company
= d.company INTO OUTFILE '/tmp/output.txt' (crude and not tested).

Data manipulation is much more flexible once its in a database. At least when
you have something this squirrelly.



Does the concat commands mean that there would no longer be a separate
first name and last name?


No, that's just part of the query that contstructs a relationship across all
four tables. The output part is the 'b.firstname'. The concat thing is just
one way to make sure the columns you are trying to match, actually do match.
This will be problematic if one table has 'John Doe' and the other has
'Johnathon Q. Doe, III' type of stuff.


My current sense of the data is that the missing info is the basic
problem, not conflicting info. What may need to happen is that we merge
the missing info and leave "duplicate" records to be dealt with by hand
where "John" has his name spelled different ways.



Also, it looks like the "=" is a requirement. Is that what you meant by
the fields having to be the same? Or could I arrange for it to have
file 1 overwrite file 2 for certain fields. Or only blank fields?


An "equivalency" is a lot cleaner for the query part anyway (where you are
matching data in one table against another). But you can do anything you want
with the output part. So, instead of using b.firstname, you could use
c.firstname instead. They would both be in their respective tables (a, b, c,
and d).




Ok, so the output is the part between the SELECT and FROM statements?

So much to learn, so little time! (kidding, this is really helpful)

Thanks.

--
ubuntu-users mailing list
ubuntu-users@xxxxxxxxxxxxxxxx
Modify settings or unsubscribe at: https://lists.ubuntu.com/mailman/listinfo/ubuntu-users

Relevant Pages

  • Re: scripting / data problem.
    ... equivalent to Comany in file4 (not something like Acme, Inc vs Acme, ... matching / missing data depending on file. ... that's just part of the query that contstructs a relationship across all ...
    (Ubuntu)
  • Re: opposite of a inner join?
    ... > From my query, I want to return the rows that DO NOT exist in the ... > table to the INNER JOIN. ... > I want to return ONLY the Rows in dbo.Clients that DO NOT have a matching ... > duplicates, 3,000 of the same result. ...
    (microsoft.public.sqlserver.programming)
  • Re: Showing records with one or more null fields
    ... , Countas NumB ... In the query grid, you would add all the fields you want to check and then put Is Null as criteria for each field. ... How can I run a single query that will show me all of the records that have missing data? ...
    (microsoft.public.access.queries)
  • Re: Concatenating address line for report
    ... Assuming the missing data is NULL ... only data in the fldCompanyName, the query returns "Acme Ltd,". ... The 1st comma is added to fldAddress1 so if you are getting a comma after ... fldCompanyName, one of the the other fields must be a zero length string. ...
    (comp.databases.ms-access)
  • RE: missing data
    ... Keith Bontrager - Bicycle Builder. ... Any way I can run a query with a function that fills up the two fields with ... missing data in the following format: ...
    (microsoft.public.access.queries)