1 million+ Excel/CSV files

Hi everyone.

I’m dealing with Excel/CSV files that contain 1+ million of data and each file is 40+ MB in size. Is this something Airtable can cover?

Thanks and all the best to everyone during these mad times.

No. You need to look into an industrial strength database system like FileMaker Pro or beyond.

Thanks Scott. Can you send links to some systems of such? I looked into the one you mentioned already. I guess it is this: https://www.claris.com/filemaker/pro/ ?

This is actually the main page for FileMaker: https://www.claris.com/filemaker/

I don’t know of any other systems, but other people might. FileMaker is the one that I’m an expert at, in addition to being an expert Airtable consultant.

Hi Jovo, and welcome to the community!

I appreciate fast terse answers; no BS’ing around, quick, to the point, move the ball downfield! @ScottWorld is really the right guy to have inside the two-minute warning.

However, there are cases were it may be worthwhile to know more about the nature of the data before dismissing Airtable based on a record count alone. Sometimes, a time-out should be called to strategize the next play. Lil’ story…

I have a client with about 20,000 Airtable client records. Each of those clients produce from 100 to 1000 transactions a year with an average of about 500 transactions per client. That’s an equivalent of about 10 million transactions a year amongst the 20,000 clients and all of it nicely packed into a single Airtable table.

To achieve this I use a technique that is not often used and to be clear, it’s not a bed of roses. However, the use case works phenomenally well for this particular client and the way that it utilizes transaction data - in this case, social media activities.

This is how the design works:

The trick is storing transactions not as individual records in a linked (relational) table; rather, store them as JSON objects in a long text field. This is, of course, a sanctioned approach as evidenced by this new Airtable feature. To pull this off, a button (in the client record) fires a script block that unpacks and renders the JSON object containing all of the transactions related to the current client record in a script block grid control. Alternatively, users are able to copy the transactions into a table reserved for this and makes their viewing more pleasurable.

There are many nuances and opportunities associated with managing vast amounts of data like this and it clearly requires some JSON object considerations.

The takeaway is that it sometimes pays to understand the data, the use case, and the user requirements before setting any implementation strategy, let alone buying a specific technology like FileMaker Pro.

2 Likes

Very cool, @Bill.French!

This topic was automatically closed 15 days after the last reply. New replies are no longer allowed.