Thread: SQL Query in Excel csv using MS Query

I have a batch of 12 csv files, one for each month. What I'm trying to do is amalgamate them all into one file with no duplicate data.

So, I've started in July 09 and created a master sheet. Then I want to query June 09 and only withdraw the records that don't match a column in the master sheet.

I've tried:-

Code:

SELECT DISTINCT `June 2009`.CRMContactId, `May 2009`.ContactName `May 2009`.ClientName, `May 2009`.SchemeName, `May 2009`.IORef, `May 2009`.Provider, `May 2009`.PlanType, `May 2009`.PlanNumber, `May 2009`.LeadPractitionerName
FROM `May 2009.csv` `May 2009`, `Master Sheet.csv` `Master`
WHERE (`May 2009`.ContactName='Policy  Check') AND (`May 2009`.PlanNumber Not In (`Master`.PlanNumber))

but it still returns records that so match the Master.PlanNumber column.

I've also tried using a subquery :-

Code:

Not In (SELECT `Master`.PlanNumber FROM `Master Sheet.csv` Master)

and that returns no records when there are some that aren't in the master sheet.

Any ideas how I can achieve this?

I would first try running only the inner query and see if that works. Also, I'm not sure if single quotes are used around table names will work in MSQuery. You might try brackets instead of the single quotes, since it is likely that the data between the single quotes can be seen as a string.

Hopefully that will help.

Couldn't seem to get the queries to work in Excel.....It just seemed to keep doubling the records, so a spreadsheet that started at 1000 odd rows would end up at 8,000+ rows when it should have been alot less than 1000.

Just ended up importing each sheet into a MySQL table and dealing with them using an SP in MySQL.

Stoopid Excel / MS Query....