Pasting from GSheets or Excel totally fickle

[Jim_Hitch]

Have had this issue before, but haven’t really tried as hard as I just have to solve it.

I copy and paste in a list of names from a spreadsheet directly into a ‘link to another record column’ for contacts. This work awesomely to bring out a ton of information I can then export to another app in the right form.

What’s really wierd is I need to do this for a different set of data from a different source in a different table. I set them up the same way, but Airtable won’t let me paste the data straight into the “link to another record column” for contacts, it doesn’t matter whether I’m doing it from GSheets or Excel. HOWEVER, if I paste some of the names from the other sheet used above, it DOES work.

So, in summary, Airtable is letting me paste names directly into a “link to another record column” from one spreadsheet, but not from another. How can this be?

[Justin_Barrett]

It probably has something to do with how the data is stored in the system clipboard by each application, and how Airtable then interprets that data when you try to paste it. It’s possible Google Sheets and Excel include some formatting or other secondary data along with the primary data itself, causing Airtable to choke on that excess data, while the other spreadsheet (which you didn’t name) doesn’t include that stuff.

Some applications deal with this by offering a “Paste without Formatting” or “Paste and Match Style” option in the Edit menu. Neither of these exist in the Airtable Mac desktop app, but Chrome does have this feature, so you could give it a shot while logged into Airtable via the browser.

[Andrew_Enright]

One big issue, at least for me, is pasting of “blank” cells. I have to add a hyphen to empty cells at the XLS or CSV level to avoid data corruption upon paste.

[W_Vann_Hall]

Earlier, I found that even when copy-and-pasting into what I thought was an extremely vanilla text editor, what was pasted was not always what the application displayed.

My suggestion would be to download a copy of frhed and copy-and-paste from each of the two spreadsheets into it. Then use the hexedit feature to examine exactly what was pasted from each. You may find, as @Justin_Barrett suggests, the different apps insert different formatting or control characters into the data stream. It won’t necessarily tell you how to fix the problem, but you’ll at least have an idea of what’s causing it to pass along to support@airtable.com.

[Jim_Hitch]

Sorry, Justin, I wasn’t clear, you’re right. BOTH of the spreadsheets are GSheets, that is what has been driving me crazy!

[Jim_Hitch]

Thanks for the tip. I’ll try that and report back.

[W_Vann_Hall]

In this reply to a related topic, @Jason_Ennis mentioned an online hex editor that looks pretty sweet: hexed.it.

[Jim_Hitch]

W_Vann_Hall:

frhed

Thanks, but I have had a look at the Hex editors and I have no idea what I’m looking at!

[W_Vann_Hall]

Jim -

No problem. What you want to do is find an example of data from each spreadsheet that acts differently when you paste it into Airtable. Mark-and-copy it, but instead of pasting it into Airtable, paste it into the hex editor (frhed, hexed.it, or another). The hex editor should show control characters that otherwise might be hidden. What I’m expecting is that you’ll find one pasted block is, say, surrounded by quotation marks and the other isn’t, or one block is prefaced by several control characters – that is, non-alphanumeric characters – having to do with signalling the encoding used.

If there isn’t anything obviously different between the two blocks — and, again, my hope is that you’ll see something different in the first few bytes of the blocks — you’ll have to look for embedded control characters in the data. Essentially, you’ll be looking in the hexadecimal part of the display for anything less than x20. That’s hex ‘two-oh’, not twenty; hex digits range from 0 to f, so the number before x20 is x1f. In other words, you want to look for hex values where the first digit is either 0 or 1.

Now, some control characters are to be expected. Depending on how the data is being passed, each line of data should end with either 0a or 0d0a. Those are the control codes for linefeed and carriage return, either of which can signal the end of a line of text.

For instance, in this Frhed screenshot, the bytes (characters) highlighted in blue are the end-of-line indicators one expects to find at the end of each record’s worth of data. (I’m using a brief CSV export from an Airtable base.) If you look to the plain text rendition in the right-hand column, you’ll see the various field values and a lot of commas. SInce control characters are, by definition, non-display, Frhed uses a period (’.’) to indicate their presence. (Although there are extended ASCII icons for most control characters, many hex editors use a period to represent anything that’s not a true alphanumeric.)

Still, their presence isn’t cause for alarm: They simply show where each line ends.

The table I exported, though, includes values containing embedded line feeds. Again, while this is normal for this data — the embedded EOLs are used to force line breaks in Airtable Blocks — if I wasn’t expecting them to be there, they could screw up (or at least confuse) my importation of the data. In the following screenshot, the bytes marked in pink indicate embedded line feeds — that is, a control character contained within a field value.

Their presence within a field might cause or contribute to your inconsistent paste attempts.

If you find something anomalous or different between the two blocks, reply here, and we’ll see if anyone knows of a way around it.

[Desmond_Beatty]

We copy five horizontal cells from a web page table.
When we paste it into google sheets it works fine - it starts at the higlighted cell and copies into that and the next four to the right.
However, when we paste the same into airtable, they are pasted vertically!