Pasting from GSheets or Excel totally fickle
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.
