By default, Numbers for Mac and Numbers for iOS support importing the following file formats:

• Numbers ’09 (2.0 or later)
• Microsoft Excel - Office Open XML (.xlsx) and Office 97 or later (.xls)
• Comma Separated Values (CSV)
• Tab-delimited text files

But opening files is not the only way to add data to a document. Using the scripting support in Numbers 3.1, you can read data directly from files, to use in the creation of new tables, or to fill-in a selection range of an existing table.

This topic concerns how to read and use the contents of Tab Separated Values (TSV) files, like the one shown below:

DEMO CONTENT: Tab-Separated-Values file

The sub-routine shown below, is used by the example scripts in this section to read tabbed data from TSV files:

Read Tab-Separated-Values File Sub-Routine
Open in AppleScript Editor
01		on readTabSeparatedValuesFile(thisTSVFile)
02		try
03		set dataBlob to (every paragraph of (read thisTSVFile))
04		set the tableData to {}
05		set AppleScript's text item delimiters to tab
06		repeat with i from 1 to the count of dataBlob
07		set the end of the tableData to (every text item of (item i of dataBlob))
08		end repeat
09		set AppleScript's text item delimiters to ""
10		return tableData
11		on error errorMessage number errorNumber
12		set AppleScript's text item delimiters to ""
13		error errorMessage number errorNumber
14		end try
15		end readTabSeparatedValuesFile

The result of this sub-routine will be an AppleScript list of data lists, like this:

{{"Sal", "Sue", "Bob", "Carl", "Wanda"}, {"Egbert", "Fred", "Danita", "Shirley", "Scott"}, {"Quincy", "Bertha", "Jane", "Julia", "Frank"}}

For a description of how table data is represented in scripts, see Populating Tables with Data, in the previous section of this site.

This script will import and interpret tabbed data from a TSV file, into the selection range of an existing table. To use, select a tange of cells in a table, that matches the parameters of the data to import. For example, if the data in the file consists of three data groups of five data items, then select three rows of cells crossing five columns in the table.

The script will prompt the user to choose the source file, and then declare whether the data is to be row-oriented or column-oriented. It will also check that the data and selection range attributes match.

 (⬆ see above ) Data imported into selection range as column-based data.
 (⬇ see below ) Data imported into selection range as row-based data.

DEMO CONTENT: The tables contained in the Selection Import Example Numbers file, are designed to use the example data file, linked in the first segment at the top of this page.

Write Data from TSV File into Table Selection
Open in AppleScript Editor
01		tell application "Numbers"
02		activate
03		try
04		if not (exists document 1) then error number 1000
05		tell document 1
06		try
07		tell active sheet
08		set the selectedTable to ¬
09		(the first table whose class of selection range is range)
10		end tell
11		on error
12		error number 1001
13		end try
14		tell selectedTable
15		-- determine the attributes of the selection range
16		set the selectionRowCount to count of rows of selection range
17		set the selectionColumnCount to count of columns of selection range
18		set the selectedCellNames to the name of every cell of the selection range
19		set the selectedCellCount to the length of the selectedCellNames
20
21		-- prompt user for the TSV file to read
22		set thisTSVFile to ¬
23		(choose file of type "public.tab-separated-values-text" with prompt ¬
24		"Pick the TSV (tab separated values) file to import:")
25
26		-- read the data
27		set thisTSVData to my readTabSeparatedValuesFile(thisTSVFile)
28
29		-- determine data structure
30		set the datagroupCount to the count of thisTSVData
31		set the dataSetCount to the count of item 1 of thisTSVData
32
33		-- create read data summary
34		set the infoMessage to ¬
35		"The read TSV data is composed of " & datagroupCount & ¬
36		" groups, with each group containing " & dataSetCount & " items." & ¬
37		return & return
38
39		-- prompt for desired data layout method
40		set dialogMessage to ¬
41		infoMessage & "Is the tabbed data to be grouped by row or column?"
42		display dialog dialogMessage ¬
43		buttons {"Cancel", "Row", "Column"} default button 1 with icon 1
44		set the groupingMethod to the button returned of the result
45
46		-- check to see that data parameters match the selection parameters
47		if the groupingMethod is "Row" then
48		if selectionRowCount is not equal to datagroupCount then ¬
49		error "SELECTION MISMATCH. " & ¬
50		"The number of rows in the table selection (" & selectionRowCount & ¬
51		"), does not match the count of rows in the imported data (" & ¬
52		datagroupCount & ")."
53		if selectionColumnCount is not equal to dataSetCount then ¬
54		error "SELECTION MISMATCH. " & ¬
55		"The number of columns in the table selection (" & selectionColumnCount & ¬
56		"), does not match the count of columns in the imported data (" & ¬
57		dataSetCount & ")."
58		else
59		if selectionColumnCount is not equal to datagroupCount then ¬
60		error "SELECTION MISMATCH. " & ¬
61		"The number of columns in the table selection (" & selectionColumnCount & ¬
62		"), does not match the count of columns in the imported data (" & ¬
63		datagroupCount & ")."
64		if selectionRowCount is not equal to dataSetCount then ¬
65		error "SELECTION MISMATCH. " & ¬
66		"The number of rows in the table selection (" & selectionRowCount & ¬
67		"), does not match the count of rows in the imported data (" & ¬
68		dataSetCount & ")."
69		end if
70
71		-- THE SELECTED CELLS WILL BE TARGETED USING THEIR CELL NAMES
72		-- So order the cell name list to be all cells by row or column
73		if the groupingMethod is "Row" then
74		-- by default, Numbers provides cell names by top to bottom row, left to right
75		set the orderedCellNames to selectedCellNames
76		-- {"B2", "C2", "D2", "E2", "F2", "B3", "C3", "D3", "E3", "F3", "B4", "C4", "D4", "E4", "F4"}
77		else
78		-- sort the row-based list of cell names to column-based
79		set the orderedCellNames to {}
80		set groupCount to selectedCellCount div selectionRowCount
81		repeat with i from 1 to groupCount
82		repeat with q from i to selectedCellCount by groupCount
83		set the end of the orderedCellNames to item q of selectedCellNames
84		end repeat
85		end repeat
86		-- {"B2", "B3", "B4", "C2", "C3", "C4", "D2", "D3", "D4", "E2", "E3", "E4", "F2", "F3", "F4"}
87		end if
88
89		-- import the data
90		set counter to 1
91		repeat with i from 1 to the count of thisTSVData
92		set thisDataGroup to item i of thisTSVData
93		repeat with q from 1 to the count of thisDataGroup
94		set the targetCellName to item counter of orderedCellNames
95		set the value of cell targetCellName to item q of thisDataGroup
96		set the counter to counter + 1
97		end repeat
98		end repeat
99
100		(* -- optional styling properties
101		set font size of selection range to 16
102		set alignment of selection range to center
103		*)
104		end tell
105		end tell
106		on error errorMessage number errorNumber
107		if errorNumber is 1000 then
108		set alertString to "MISSING RESOURCE"
109		set errorMessage to "Please create or open a document before running this script."
110		else if errorNumber is 1001 then
111		set alertString to "SELECTION ERROR"
112		set errorMessage to "Please select a table before running this script."
113		else
114		set alertString to "EXECUTION ERROR"
115		end if
116		if errorNumber is not -128 then
117		display alert alertString message errorMessage buttons {"Cancel"}
118		end if
119		error number -128
120		end try
121		end tell
122
123		on readTabSeparatedValuesFile(thisTSVFile)
124		try
125		set dataBlob to (every paragraph of (read thisTSVFile))
126		set the tableData to {}
127		set AppleScript's text item delimiters to tab
128		repeat with i from 1 to the count of dataBlob
129		set the end of the tableData to ¬
130		(every text item of (item i of dataBlob))
131		end repeat
132		set AppleScript's text item delimiters to ""
133		return tableData
134		on error errorMessage number errorNumber
135		set AppleScript's text item delimiters to ""
136		error errorMessage number errorNumber
137		end try
138		end readTabSeparatedValuesFile

This script reads tabbed data from a TSV file, and creates a new table, on the active sheet, to match the dimensions of the imported data.

Create Table from TSV File
Open in AppleScript Editor
01		property useHeaders : true
02
03		tell application "Numbers"
04		activate
05		try
06		if not (exists document 1) then error number 1000
07		tell document 1
08		tell active sheet
09		-- prompt user for the TSV file to read
10		set thisTSVFile to ¬
11		(choose file of type "public.tab-separated-values-text" with prompt ¬
12		"Pick the TSV (tab separated values) file to import:")
13
14		-- read the data
15		set thisTSVData to my readTabSeparatedValuesFile(thisTSVFile)
16
17		-- determine data structure
18		set the datagroupCount to the count of thisTSVData
19		set the dataSetCount to the count of item 1 of thisTSVData
20
21		-- create read data summary
22		set the infoMessage to ¬
23		"The read TSV data is composed of " & datagroupCount & ¬
24		" groups, with each group containing " & dataSetCount & " items." & ¬
25		return & return
26
27		-- prompt for desired data layout method
28		set dialogMessage to ¬
29		infoMessage & "Is the tabbed data to be grouped by row or column?"
30		display dialog dialogMessage ¬
31		buttons {"Cancel", "Row", "Column"} default button 1 with icon 1
32		set the groupingMethod to the button returned of the result
33
34		if useHeaders is true then
35		set datagroupCount to datagroupCount + 1
36		set dataSetCount to dataSetCount + 1
37		set startingAdjustment to 1
38		else
39		set startingAdjustment to 0
40		end if
41
42		if groupingMethod is "Row" then
43		set thisTable to ¬
44		make new table with properties ¬
45		{row count:datagroupCount, column count:dataSetCount}
46		else if groupingMethod is "Column" then
47		set thisTable to ¬
48		make new table with properties ¬
49		{row count:dataSetCount, column count:datagroupCount}
50		end if
51
52		-- import the data
53		if the groupingMethod is "Row" then
54		repeat with i from 1 to the count of thisTSVData
55		set thisDataGroup to item i of thisTSVData
56		tell row (i + startingAdjustment) of thisTable
57		repeat with q from 1 to the count of thisDataGroup
58		set the value of cell (q + startingAdjustment) to item q of thisDataGroup
59		end repeat
60		end tell
61		end repeat
62		else
63		repeat with i from 1 to the count of thisTSVData
64		set thisDataGroup to item i of thisTSVData
65		tell column (i + startingAdjustment) of thisTable
66		repeat with q from 1 to the count of thisDataGroup
67		set the value of cell (q + startingAdjustment) to item q of thisDataGroup
68		end repeat
69		end tell
70		end repeat
71		end if
72
73		end tell
74		end tell
75		on error errorMessage number errorNumber
76		if errorNumber is 1000 then
77		set alertString to "MISSING RESOURCE"
78		set errorMessage to "Please create or open a document before running this script."
79		else
80		set alertString to "EXECUTION ERROR"
81		end if
82		if errorNumber is not -128 then
83		display alert alertString message errorMessage buttons {"Cancel"}
84		end if
85		error number -128
86		end try
87		end tell
88
89		on readTabSeparatedValuesFile(thisTSVFile)
90		try
91		set dataBlob to (every paragraph of (read thisTSVFile))
92		set the tableData to {}
93		set AppleScript's text item delimiters to tab
94		repeat with i from 1 to the count of dataBlob
95		set the end of the tableData to ¬
96		(every text item of (item i of dataBlob))
97		end repeat
98		set AppleScript's text item delimiters to ""
99		return tableData
100		on error errorMessage number errorNumber
101		set AppleScript's text item delimiters to ""
102		error errorMessage number errorNumber
103		end try
104		end readTabSeparatedValuesFile