Hello,
I customised the script found on https://iworkautomation.com/numbers/table-read-file.html to read and import a csv file into a Numbers table that is already existing.
I am using it to import a Facebook guest list exported from an event.
It is working fine but the script takes a lot of time to execute and process goes to more than 400% in the activity monitor.
Would you have some suggestions for optimising it ?
Thank you !
Here it is :
property useHeaders : false
set numberFile to #My Numbers File
on readCommaSeparatedValuesFile(thisCSVFile)
try
set testUTF to read thisCSVFile as «class utf8»
set dataBlob to (every paragraph of (testUTF))
set the tableData to {}
set AppleScript's text item delimiters to {","}
repeat with i from 1 to the count of dataBlob
set the end of the tableData to ¬
(every text item of (item i of dataBlob))
end repeat
set AppleScript's text item delimiters to ""
return tableData
on error errorMessage number errorNumber
set AppleScript's text item delimiters to ""
error errorMessage number errorNumber
end try
end readCommaSeparatedValuesFile
tell application "Numbers"
activate
try
open numberFile
if not (exists document 1) then error number 1000
tell document 1
tell (sheet "Facebook event")
-- prompt user for the CSV file to read
set thisCSVFile to ¬
(choose file of type "public.comma-separated-values-text" with prompt ¬
"Pick the CSV (comma separated values) file to import:")
-- read the data
set thisCSVData to my readCommaSeparatedValuesFile(thisCSVFile)
-- determine data structure
set the dataGroupCount to the count of thisCSVData
set the dataSetCount to the count of item 1 of thisCSVData
-- create read data summary
set the infoMessage to ¬
"The read CSV data is composed of " & dataGroupCount & ¬
" groups, with each group containing " & dataSetCount & " items." & ¬
return & return
-- prompt for desired data layout method
set dialogMessage to ¬
infoMessage & "Would you like to import" & thisCSVFile & "into the table Guests ?"
display dialog dialogMessage ¬
buttons {"Cancel", "Import"} default button 1 with icon 1
set the groupingMethod to "Row"
if useHeaders is true then
set dataGroupCount to dataGroupCount + 1
set dataSetCount to dataSetCount + 1
set startingAdjustment to 1
else
set startingAdjustment to 0
end if
-- import the data
if the groupingMethod is "Row" then
repeat with i from 1 to the count of thisCSVData
set thisDataGroup to item i of thisCSVData
tell row (i + startingAdjustment) of (table "Guests")
repeat with q from 1 to the count of thisDataGroup
set the value of cell (q + startingAdjustment) to item q of thisDataGroup
end repeat
end tell
end repeat
else
repeat with i from 1 to the count of thisCSVData
set thisDataGroup to item i of thisCSVData
tell column (i + startingAdjustment) of (table "Guests")
repeat with q from 1 to the count of thisDataGroup
set the value of cell (q + startingAdjustment) to item q of thisDataGroup
end repeat
end tell
end repeat
end if
end tell
end tell
on error errorMessage number errorNumber
if errorNumber is 1000 then
set alertString to "MISSING RESOURCE"
set errorMessage to "Please create or open a document before running this script."
else
set alertString to "EXECUTION ERROR"
end if
if errorNumber is not -128 then
display alert alertString message errorMessage buttons {"Cancel"}
end if
error number -128
end try
end tell
You may try :
set path2csv to choose file kind {"public.comma-separated-values-text"}
tell application "Numbers"
open path2csv
end tell
Yvan KOENIG running El Capitan 10.11.4 in French (VALLAURIS, France) mercredi 27 avril 2016 17:30:26
Merci Yvan !
The command open is instantaneous but if I understand well “open” is opening the cvs in a new sheet.
My script is importing in a specified sheet and specified table and each value in a different cell.
I can’t change that behaviour because existing cells are referenced in existing formulas.
Is it possible to do it with “open” ?
Hi. Welcome to MacScripter.
The fastest way I know to get text into a Numbers document is to paste it in. If you’re starting with comma-delimited text, you’d need to convert it to tab-delimited text first, which is also pretty fast in AppleScript.
I haven’t looked very closely at the fine details of your own script, but here’s a proof-of-concept illustration of what I’m saying:
property useHeaders : false
-- Choose and read a CSV file.
set csvFile to (choose file of type "public.comma-separated-values-text" with prompt ¬
"Pick the CSV (comma separated values) file to import:")
set csvText to read csvFile as «class utf8»
-- Ditch the headers line, if not wanted. (Assumed to be only one.)
if (not useHeaders) then set csvText to text from paragraph 2 to -1 of csvText
-- Convert to tab-delimited text, except within quoted sections:
set astid to AppleScript's text item delimiters
-- Chop the text into quoted and non-quoted sections.
set AppleScript's text item delimiters to quote
set quotedNnonquoted to csvText's text items
-- The odd-numbered sections are the non-quoted ones.
repeat with i from 1 to (count quotedNnonquoted) by 2
set nonquoted to item i of quotedNnonquoted
if ((count nonquoted) is 0) then
-- If this odd-numbered text item is "", it's the insertion point between two adjacent quotes, which represent a quote character in a quoted section.
set item i of quotedNnonquoted to quote
else
-- Otherwise replace all the commas in this section with tabs.
set AppleScript's text item delimiters to ","
set nonquoted to nonquoted's text items
set AppleScript's text item delimiters to tab
set item i of quotedNnonquoted to nonquoted as text
end if
end repeat
-- Reassemble the text without replacing the quotes round the quoted sections.
set AppleScript's text item delimiters to ""
set tabbedText to quotedNnonquoted as text
set AppleScript's text item delimiters to astid
-- Set the clipboard to the result.
set the clipboard to tabbedText
-- This appears to work with both Numbers 2.3 and Numbers 3.6.1.
-- The template document is assumed to be already open.
tell application "Numbers"
activate
-- Select the appropriate top-left anchor cell.
tell table 1 of sheet 1 of front document
if (useHeaders) then
set selection range to range "A1:A1"
else
set selection range to range "A2:A2"
end if
end tell
end tell
-- Paste and Match Style.
tell application "System Events" to keystroke "v" using {command down, option down, shift down}
Hi Nigel !
Thank you for your script it is working flawlessly for my need.
I will study it in detail
Have a good evening !
Nicolas
I was ready to post a script when I saw Nigel’s message.
As I use an other scheme, I post my own script.
set path2csv to choose file kind {"public.comma-separated-values-text"}
tell application "Numbers"
activate
# A cell is supposed to be selected in the table in which the datas will be stored
# Grab the information about the target table
set {leDocument, laFeuille, laTable, numLigne1, numColonne1, numLigne2, numColonne2} to my get_SelParams()
set importedDoc to open path2csv
tell me to delay 0.2
tell importedDoc to tell sheet 1 to tell table 1
set fullRange to "A1:" & name of last cell
set selection range to range fullRange
set nbRows to count rows
set nbCols to count columns
end tell
tell me to delay 0.5
tell application "System Events" to tell process "Numbers"
set frontmost to true
keystroke "c" using {command down} # Copy the imported datas
end tell
close importedDoc without saving
tell document leDocument to tell sheet laFeuille to tell table laTable
set nbRows2 to count rows
set nbCols2 to count columns
if nbRows2 < nbRows then
repeat (nbRows - nbRows2) times
add row below row 5
end repeat
end if
if nbCols2 < nbCols then
repeat (nbCols - nbCols2) times
add column after last column
end repeat
end if
set fullRange to "A1:" & name of last cell
set selection range to range fullRange
end tell
--tell me to delay 0.5
tell application "System Events" to tell process "Numbers"
set frontmost to true
keystroke "v" using {command down, option down, shift down}
end tell
end tell
#=====
# Version pour Numbers 3.2.x
on get_SelParams()
try
tell application "Numbers"
set t to front document's active sheet's first table whose selection range's class is range
tell t's selection range
tell first cell to set {firstRowNum, firstColNum} to {its row's address, its column's address}
tell last cell to set {lastRowNum, lastColNum} to {its row's address, its column's address}
end tell
# It seems that at least one version of Numbers doesn't recognize the function parent so use an other way to get the name of the active sheet
return {front document's name, name of front document's active sheet, t's name, firstRowNum, firstColNum, lastRowNum, lastColNum}
end tell
on error
display dialog "Problem getting values. Did you select cells?" buttons "Cancel"
end try
end get_SelParams
#=====
I wish to add that Nigel’s script doesn’t work for a French user.
Here, a csv usable with Numbers doesn’t use comma as delimiter but use semi colon.
So, if the source file use the comma as delimiter, a French user would have to use Nigel’s script.
If the source file use the semi colon as delimiter, he would have to use my script.
Yvan KOENIG running El Capitan 10.11.4 in French (VALLAURIS, France) mercredi 27 avril 2016 21:30:28
Hi Yvan.
The above doesn’t compile on my computer. It should be:
set path2csv to choose file of type {"public.comma-separated-values-text"}
Hmmm. I knew there were several interpretations of “comma”-separation, but I didn’t realise different countries had different preferences. Presumably it’s to do with the characters they commonly use in spreadsheets. Presumably too spreadsheet applications have some way of discovering which character’s the “comma” in any particular file.
Fortunately, my script seems to suit the CSVs voisincolas wants to import. The script in his original post assumes comma separators too.
I am French but the UI of my Mac is set up in english 
The particular CSV I am working with is comma separered.
But I noticed that CSV files exported from Numbers are semicolon separated “;”
Maybe it is linked to the system region setting …
Et merci Yvan pour ta version je regarde ca dès demain matin.