Access-like queries on a Mac

December 2, 2010 12:40 PM

How can I combine datasets using a Mac? I'd use Access on Windows typically.

I frequently need to match together more than one spreadsheet. Example: I have two Excel spreadsheets. One has a list of students with their names, student numbers, and GPAs. Another has student numbers and their majors. I'd want to combine the two sheets together using the student number as the join points.

On a PC I'd use MS Access. What can I use for a Mac? I've heard of FileMaker Pro but I don't need all of its features for the price, just the ability to do those kinds of fairly simple queries. I'd like to avoid having to remote into a PC.

Something tells me SQL has the answer?

(I saw this previous Ask thread, which brings up online tools, but I can't use those due to confidentiality issues.)

posted by davextreme to Computers & Internet (9 answers total)

If you want something access-like, neooffice has its version.
posted by mandymanwasregistered at 12:45 PM on December 2, 2010

Libreoffice also has an Access analogue.
posted by Threeway Handshake at 12:47 PM on December 2, 2010

You can install some database like mySQL or postrgeSQL. Or you could get something like NeoOffice. Or you could get Bento (file maker's little brother).
posted by Brian Puccio at 12:53 PM on December 2, 2010

Best answer: Is there one major per student? If so, I do this on my Mac all the time in Excel using VLOOKUPs. With big datasets it tends to crawl along pretty slowly, but if you only need to do this once per spreadsheet it might work in a pinch.

A database program would of course be a better tool, especially if your student-id/major table isn't unique on student.
posted by Jugwine at 12:59 PM on December 2, 2010

Base is a cheap and pretty front-end to SQLite, which handles a whole bunch of backend things on your Mac.
posted by scruss at 1:08 PM on December 2, 2010

Response by poster: Oh, I should have mentioned, also, that I've tried OpenOffice and found it exceedingly clunky.

I played with Bento a bit but didn't see this sort of query tool. Is it in there?
posted by davextreme at 1:58 PM on December 2, 2010

If you are comfortable using code to do this, here is a rather simple way:

• Make sure that column names don't have spaces (use student_name over student name) and save both files as comma separated files (csv)
• Download, install and open R
• Press ⌘ D and change working directory to where the csv files are.

The type in the following code:

file1=read.csv("name_of_first_file.csv", header=T)
file2=read.csv("name_of_file_2.csv", header=T)
new_file=merge(file1, file2, by="student_id")
write.csv(new_file, file="merged_file.csv")


Now open merged_file.csv in Excel and do whatever you need to do with it.
posted by special-k at 3:55 PM on December 2, 2010

You realize that this could potentially just be done in Excel? Are there missing student numbers or students exclusive to one or the other data set?

If not, all you need to do is sort the columns in each respective data set by student number. Then simply cut and paste columns of data over from one file to the other. Done.
posted by drpynchon at 9:14 PM on December 2, 2010

Response by poster: Thanks, all. I've been practicing with VLOOKUP (which Numbers has, too), and it's done the trick.
posted by davextreme at 4:43 PM on December 9, 2010