Updating Numbers Spreadsheet with CSV

Question

Level 1

4 points

Updating Numbers Spreadsheet with CSV

I called Apple support earlier with this question and am astounded that their answer was "No. Numbers can't/doesn't do that." They literally directed me here for direction.

I have a Filemaker Pro database with a few thousand records in it. It's basically a mailing list with contact info, mailing information, and some data for a membership organization. This database is routinely updated by importing an identical CSV (same header row, field names, etc.) exported from our membership website, by using a unique ID Number to match the records. In other words, on CSV import, Filemaker will update the records if the Unique IDs match, and if not, will add the records as new records.

I'd like to port this database to Numbers, but I don't see a way in Numbers to perform these routine updates. In fact, I don't see any way (other than manually) to routinely update the same data from a CSV.

I'm open to any workflow suggestions or guidance. Much thanks in advance.

Terrell

iMac with Retina 5K display, macOS 10.15

Posted on Jan 28, 2020 11:30 AM

Reply

Answer 1

t quinn

Level 5

7,063 points

Jan 30, 2020 8:09 AM in response to thorn222

Hi Terrell,

It is possible to flag duplicates with a function.

A function would not

Deletes the existing, effectively replacing the existing with the new.

An Applescript might be writen to do this.

What I don't understand is why you are not just pasting the updated data wholesale into your working table since the Filmmaker database is the bible.

Remember, while Numbers spreadsheets can be stretched to perform many function of a proper database it is still simply a user friendly spreadsheet program.

quinn

Reply

Answer 2

Barry

Level 9

60,144 points

Jan 28, 2020 7:26 PM in response to thorn222

HI Terrell,

"I called Apple support earlier with this question and am astounded that their answer was "No. Numbers can't/doesn't do that." "

Were you astounded that Numbers couldn't do that, or astounded that the person you talked to provided that information straight out?

I don't find it astounding that Filemaker has talents not shared with Numbers. FilemakerPro is, as the name (and price) suggests, professional grade software, focussed on creating and managing database records. Numbers, available without charge to anyone with a current Mac, is a consumer grade general purpose spreadsheet application, quite good at what it does, but not as feature rich in specific areas as some other applications.

"They literally directed me here for direction."

Often good advice. The users in this community are people who use the software, and will be aware of several quirks, and ways to do some things that Numbers wasn't consciously designed to do.

Regarding your issue:

Are the imported records 'complete'? Could the new ones REPLACE the existing records with the same ID numbers?

If so, it might be possible to use a dedicated column to identify either the existing records or the incoming ones.

If that can be done, there are ways to Sort the records by ID number, bringing all the updates and previous record pairs together, then (if necessary) doing a sub sort on the marker column to place the old and new records in the same order relative to each other.

Can't run with that right now, but someone may pick it up before I get a chance to think it out further.

Regards,

Barry

Reply

Answer 3

t quinn

Level 5

7,063 points

Jan 30, 2020 2:53 PM in response to thorn222

Hi Terrell,

Functions can not only reference different tables but they can reference tables on different sheets too. They all have to be in the same document.

It is possible to set up 2 different tables that reference each other on the same sheet, then cut one table and paste it into a different sheet. This can be really useful if you are setting up a report table.

I am thinking it would be possible to set up a table that extracts only the non-duplicates from your imported table so they are ready to be pasted into your master.

quinn

Reply

Answer 4

SGIII

Level 8

37,734 points

Jan 29, 2020 3:09 AM in response to thorn222

Not sure I understand the problem.

Have you tried just copy-pasting the data into your existing Numbers table holding the imported membership information?

SG

Reply

Answer 5

thorn222 Author

Level 1

4 points

Jan 30, 2020 6:30 AM in response to thorn222

@Barry: Maybe "astounded" is a bit much, but yes, I was surprised to hear that Numbers had no way of comparing incoming data (being imported, copied & pasted, etc.) with existing data. I mean, how do users keep databases updated and avoid duplicates, if they are routinely adding records from another source? Two apple reps both said "manually." I know there is a better way. I also know that I don't know everything, thus, the post asking for help.

To be a bit clearer, and since Numbers doesn't have this function, I'm assuming a workaround may include building a function or series of functions that does this in batch:

Matches new records (imported or pasted) with existing records (using a unique ID)
Deletes the existing, effectively replacing the existing with the new.
Leaves the new which were NOT matched with existing as a new record.

Much thanks in advance,

Terrell

Reply

Answer 6

thorn222 Author

Level 1

4 points

Jan 30, 2020 8:52 AM in response to t quinn

t quinn wrote:

why you are not just pasting the updated data wholesale into your working table since the Filmmaker database is the bible.

Filemaker has moved away from being awesome database software that does cool tricks with presentation and layout, to a cloud-based app builder platform with neat database integration. That's why I'm looking at Numbers and trying to fill in the functionality gap.

I host an online membership association with 400-500 members who update their profiles daily. I keep an offline mailing list of over 3,000 current, past, and potential members. Whenever we do a USPS mailing, I update this offline mailing list with the latest (most up-to-date) export from our online membership database (CSV format). Manually sorting, searching, updating and eliminating just isn't an option for us. If Numbers just isn't capable of offering an elegant solution, then so be it. I truly had hoped it would be because I love Numbers and its a joy to work with.

I continue to appreciate your help and guidance.

Terrell

Reply

Answer 7

t quinn

Level 5

7,063 points

Jan 30, 2020 9:16 AM in response to thorn222

Hi Terrell,

Here is a simple function that will count duplicate ids. It is filled down.

COUNTIF(A$2:A2,$A2)

Tom and Mable are pasted additions. They are flagged as dups with column E being greater than 1. This functon will not sort. SO you would have to locate the 2s.

With a further complication to the formula you would get conditonal highlighting to flag the dups.

quinn

Reply