Optimising csv import in Numbers 3.6.1

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 :wink:

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.