Random row order in csv export

[Ana_Lara]

Hi,
I exported a view to csv file so I could work on it and ID duplicates for some of my data. However, when I compare the csv export with the data in the table, the order of the rows in the csv appears to be random and does not follow the same order in my table. How can I export to csv and maintain the same order I have in my table?
Thanks in advance

[ScottWorld · Accepted solution]

Welcome to the community, @Ana_Lara!

This is one of my big pet peeves of Airtable.

Strangely, unlike all other database and spreadsheet systems on the market, Airtable doesn’t actually know what order your records are displayed in — unless you sort or group your records first.

Otherwise, if your records are ungrouped and/or unsorted, and you try to export records, Airtable exports your records in a completely random & unpredictable order.

It’s the gigantic mystery of Airtable as to what sort order your records will be exported in. Or even be displayed in on your screen. It’s literally a total crapshoot.

Some people think that it is “creation order” of the records, but it’s not. It only SOMETIMES displays/exports records in creation order, but the display/export order constantly changes based on what recent sorting and unsorting activity you’ve done in your table.

So, your only solution is to either use the “group” function or the “sort” function, both available on the toolbar. Both of those options should (hopefully) force the export to put your records in the proper order.

If you feel that this behavior should be changed, please submit a feature request in the #show-and-tell:product-suggestions category. You can even reference this discussion to give some additional context.

[Justin_Barrett]

ScottWorld:

Strangely, unlike all other database and spreadsheet systems on the market, Airtable doesn’t actually know what order your records are displayed in — unless you sort or group your records first.

That’s not entirely accurate. It does keep track of the order internally for display purposes—e.g. if you drag records around, it maintains that order between sessions—but that order isn’t always reflected when the records are passed to other processes.

I just ran three quick CSV export tests on a table with 8 records:

1. Records in starting order (they’d been manually shuffled around in a prior test, so I knew they weren’t in creation order any more).
2. Same records manually shuffled.
3. Records with a sort applied on a specific field.

All exports had the CSV rows in the same order as the view at the time of export.

@Ana_Lara You might consider writing to Airtable support. Perhaps it’s a browser issue? I’m using Chrome on a Mac, and there has been evidence elsewhere of browser/OS differences affecting some of Airtable’s operations, though mostly at a very minor level.

[ScottWorld]

Thanks, Justin! Yes, that is true that Airtable thankfully retains record order between sessions, which I am extremely thankful for. It would be unusable if it shuffled our records every time we re-entered a base.

My experience is that when you group and/or sort records and then ungroup/unsort them, they don’t always return back to the same order. Sometimes they do, sometimes they don’t. They return to a random order. The order is non-figure-outtable. It’s not by creation date. It has something to do with how you manually shuffled your records PRIOR to grouping/sorting, and it also has something to do with how you sorted them.

Worse, if my records are unsorted, the export function exports the records randomly & differently every single time.

I’m experiencing this right now on a test database that I created. The records are being exported in a random order every time I export the records. (Note that in between each export, I am grouping or sorting, and then I am ungrouping or unsorting.)

I really wish that this wasn’t the case. I wish that things were always 100% predictable, like in FileMaker — arguably the world’s most reliable & dependable database program that is available to consumers. It’s not as fun and exciting as Airtable, though, :stuck_out_tongue_winking_eye:

[Justin_Barrett]

ScottWorld:

I’m experiencing this right now on a test database that I created. The records are being exported in a random order every time I export the records. (Note that in between each export, I am grouping or sorting, and then I am ungrouping or unsorting.)

Interesting. Two of my earlier tests were prior to sorting that view, which I hadn’t done before this order experiment. When exporting the sorted view, the CSV still matched. However, after disabling the sort, the CSV download no longer matched. It stayed in the order of the sorted records.

I then manually dragged two records. The CSV was still ordered like the sorted version. More dragging, and finally the CSV wasn’t sorted like before, but it also didn’t match the view. It was close in some respects, but not in perfect sync.

However, I did notice another pattern emerging. Each time that I would sort, and then kill the sort, two things stood out:

1. The next CSV download matched the sort, even though the records were no longer sorted.
2. Airtable returned the records to their original creation order.

I know that #2 is true because I had a entered very specific data in the primary fields when I made that test table weeks ago. Every time I sort, then unsort, the records return to that original order.

[ScottWorld]

That’s very interesting. Thank you for your further analysis on this, Justin!! :slightly_smiling_face:

What I find extremely interesting is your #2 revelation above. On my end, Airtable does NOT always return my records to their original creation order. (I am looking at the creation date field.) I would say that on my end, it only returns me to the original creation order about 20% of the time. About 80% of the time, my records go into some unknown order that doesn’t seem to have a pattern to me.

Very strange.

[Bill_French]

Ana_Lara:

How can I export to csv and maintain the same order I have in my table?

In my view (and in practice) to get precise and predictable record order, you best take the responsibility into your own hands. Relying on Airtable (or worse, Zapier or Integromat) to magically do what you want in a sustained and predictable fashion will lead to disappointment.

You’ve heard the term “we’ll fix it in post” from the movie industry. The integration economy has a corollary term; we’ll fix it in the stream - ergo, as the data is wending its way from point (a) to point (b), we’ll assert the specific ordering that we need.

One way to do this is to use a script block that has the ability to not only formulate an exported CSV, but also the ability to sort, filter, and even transform values should the destination target service need specific data transformations. Here’s a script that might help you get there.

Caution - requires some scripting and you’d have to apply the sorting capability, but that’s likely one line of code.

[ScottWorld]

Bill.French:

Relying on Airtable (or worse, Zapier or Integromat)

All 3 of these programs have sorting capabilities, so I don’t see a problem with using any of them. My clients sort their records every day with all 3 platforms — Airtable, Zapier, and Integromat — and then create CSV files in the desired order.

So the only real issue here is exporting a CSV file of records without the records being in a “sorted state” first. We now know through trial & error that Airtable simply doesn’t respect the record order on our screen — unless, like you said, we take the responsibility into your own hands. Sorting falls 100% on our shoulders.

[Bill_French]

ScottWorld:

All 3 of these programs have sorting capabilities, so I don’t see a problem with using any of them.

I see it a little differently because users must be trained and/or know that exporting data for process (x) has sorting/view requirements they must think about in advance of clicking “export”. I tend to encourage clients to avoid relying on users to remember such things because invariably, they will forget and then the system fails.

ScottWorld:

So the only real issue here is exporting a CSV file of records without the records being in a “sorted state” first.

Again. the success of any integration process should not be gated by any user’s preamble requirements, sorting or otherwise. If the integration itself can perform these steps, it should be instrumented to do so.

This is more of a philosophical recommendation based on experience that shows only in rare exceptions that users are generally not dependable in such matters (by and large). If you are building a system that depends on sorted information, you best build that into the integration machinery, not into the human process leading up to the automated process.

Forcing a user to remember how something should be sorted is not much different than asking them to rename a field before exporting and renaming it back after exporting. Do you want sustainability, or would you prefer a shi*show? :winking_face:

BTW - I’m okay with either pathway @Ana_Lara chooses; I’m just pointing out that one approach comes with process debt which accrues compound interest that must be repaid (many times over typically), and another which has no brittleness or compounding debt repayments.

Ideally, Airtable would add a global dictionary of data views - i.e., a collection of aliases that we could establish for various pre-sorted/filtered views that are sustained and difficult to change by average users. Didn’t FileMaker have exactly this feature?

[ScottWorld]

Bill.French:

I tend to encourage clients to avoid relying on users to remember such things because invariably, they will forget and then the system fails.

Well, this is precisely what Airtable’s views are for. You create a view with a preset filter & sort, and then you do all of your exporting from that view. You could even call the view “Export Screen” or whatever.

That’s a good reminder for you, @Ana_Lara: Once you setup a view with a filter/sort/group, your exports will work just fine from that point forward. You could even create your own special view just for exports, as I described above.