Pasting Tab delimited Text into a Table

[Paradigm]

If I drag and copy let say 7 rows 6 columns of data from an Excel or Numbers spreadsheet I essentially have an array. How would I go about creating a script that takes that data and then puts it into 7 different records with 6 fields.

I just read where:

It could be scripted.

1. Paste the text into a single text field.

2. Use a script with export field contents to export the data to a text file.

3. Now the script can do a tab delimitted Import of the data back into FileMaker to get the data into the respective fields.

But I would like to do this without creating that text file.

I'm thinking I would paste the text into a variable and then parse each line but how do you identify the delimiter in the array? What is the delimiters in an array when you copy data from an Excel or Numbers spreadsheet onto the clipboard? It's a Tab right?

[comment]

You can't paste into a variable - but you can into a global field. The delimiters are (most likely) a carriage return between rows and a tab between column values.

Another option (on Mac platform only) is to use AppleScript to access the clipboard data AND to create the records.

[Paradigm]

You can't paste into a variable - but you can into a global field.

That right and I actually knew that (doh!) but wasn't really thinking intelligently about that when I wrote my post. Thanks for pointing that out.

...The delimiters are (most likely) a carriage return between rows and a tab between column values.

Again that's what I was thinking but how do you identify a Tab in a FileMaker calculation? What's the symbol for a Tab?

Another option (on Mac platform only) is to use AppleScript to access the clipboard data AND to create the records.

That is good to know too but in this case the solution has to work for both the Mac and Windows platforms.

[bruceR]

If I drag and copy let say 7 rows 6 columns of data from an Excel or Numbers spreadsheet I essentially have an array. How would I go about creating a script that takes that data and then puts it into 7 different records with 6 fields.

I just read where:

But I would like to do this without creating that text file.

I'm thinking I would paste the text into a variable and then parse each line but how do you identify the delimiter in the array? What is the delimiters in an array when you copy data from an Excel or Numbers spreadsheet onto the clipboard? It's a Tab right?

You can also handle this with script triggers.

See the script triggers attached to "Table View" layout in this demo.

ArrowKeyRecordNav.fp7.zip

[comment]

What's the symbol for a Tab?

In version 10 and higher, you can use Char (9). In previous versions, you'd simply use the tab character itself in quotes - same as any other character.

[Paradigm]

You can also handle this with script triggers.

See the script triggers attached to "Table View" layout in this demo.

ArrowKeyRecordNav.fp7.zip

Wow that is great Bruce and it exactly what I was hoping to do. Having looked at what you did now what I am going to do is start from scratch tomorrow and try to build that from the ground up so that I get the education. That's a great help, thanks.