exporting numbers 3.0 to csv using applescript to a specific folder

[neelaym]

I am trying to export csv files from Numbers 3.0 to a specific location. I had an earlier script in Numbers 2.3 which worked fine, but on upgrade I found that Numbers 3.0 doesn’t have a suite in the AppleScript Dictionary so you can’t “tell” objects such as document, sheet, table any more.

As a work around, I am accessing its menu items. With menu items it gets to point where it exports csv files to a predefined folder. By default which is Documents. I want to save my csv’s to a specific folder. Can anyone here suggest how I can go about doing it.

Here is the current script:

 do shell script "defaults write com.apple.iWork.Numbers CSVExportEncoding  -int 4"
        my do_submenu("Numbers", "File", "Export To", "CSV.") --enter ellipse with option semicolon
        on do_submenu(app_name, menu_name, menu_item, submenu_item)
	try
		-- bring the target application to the front
		tell application app_name
			activate
		end tell
		tell application "System Events"
			tell process app_name
				tell menu bar 1
					tell menu bar item menu_name
						tell menu menu_name
							tell menu item menu_item
								tell menu menu_item
									click menu item submenu_item
									keystroke return
									delay 2
									keystroke return
								end tell
							end tell
						end tell
					end tell
				end tell
			end tell
		end tell
    return true
	on error error_message
		return false
	end try
end do_submenu

Any help would be appreciated thanks.

[kel1]

Hi neelaym,

Someone else is probably working on this, but you could do this. Get the names of all the files in Documents folder at the beginning of your script. At the end of your script, find the file that does not belong to that list. then move that file to the folder that you want.

gl,
kel

[Yvan_Koenig]

When you use GUIScripting to save documents,

Set the filename in the dedicated text field
issue
keystroke “d” using {command down}
which sets the target folder to Desktop.
Knowing this location, it’s easy to add instructions moving the file to the wanted folder.

Yvan KOENIG (VALLAURIS, France) jeudi 7 novembre 2013 22:49:43

PS : look at :
http://macscripter.net/viewtopic.php?id=41651

[neelaym]

Thanks, Kvan, saving it to Desktop helps. In this way, I can be sure where the files are exported always and then move it accordingly.

[Yvan_Koenig]

Here is a complete script.

# trigger Numbers's File > Export. > CSV.
(*
Use my select_SubMenu("Numbers", 3, 13, 3) during tests to check that we trigger the correct items
*)
my selectSubMenu("Numbers", 3, 13, 3)
(*
Use the pathname to be sure that we speak to Pages 5.0 and not Pages 4.3
which is supposed to be "/Applications/iWork '09/Numbers.app"
*)
activate application "/Applications/Numbers.app"
tell application "System Events" to tell (first process whose frontmost is true)
	set procID to its id
	set dName to name of first window whose subrole is "AXStandardWindow" # take care of possible inspector whose subrole is "AXFloatingWindow"
	(*
Builds the name of the CSV file with a datetime stamp _yyyyMMddThhmmss.csv
So we are sure that it will not duplicate. *)
	if dName ends with ".numbers" then
		set csvName to (text 1 thru -9 of dName) & "_" & my dateTimeStamp() & ".csv"
	else
		set csvName to dName & "_" & my dateTimeStamp() & ".csv"
	end if
	(*
Wait for the availability of the Export sheet *)
	repeat
		delay 0.1
		if exists sheet 1 of window dName then exit repeat
	end repeat
	#class of UI elements of sheet 1 of window dName
	--> {group, static text, list, UI element, button, button, button, button}
	keystroke return # idem click [Continue]
	repeat
		delay 0.1
		if (class of UI elements of sheet 1 of window dName) contains text field then exit repeat
	end repeat
	# class of UI elements of sheet 1 of window dName
	--> {button, button, button, group, checkbox, text field, static text, static text, text field}
	(*
Now we are in the Save sheet *)
	keystroke "d" using {command down} # set target folder to Desktop
	(*
Set the name of the new CSV *)
	set value of text field 1 of sheet 1 of window dName to csvName
	keystroke return # idem click [Save]
end tell # System Events.
set p2csv to (path to desktop as text) & csvName
tell application "System Events"
	repeat until exists disk item p2csv
		delay 0.1
	end repeat
end tell
set p2csv to quoted form of POSIX path of p2csv
set destFolder to quoted form of POSIX path of (path to documents folder as text)
# Moves the csv to its final location
do shell script "mv " & p2csv & space & destFolder

#=====
(*
my selectMenu("Pages",5, 12)
==== Uses GUIscripting ====
*)
on selectMenu(theApp, mt, mi)
	if theApp is "Pages" then
		set p2app to "/Applications/Pages.app"
	else if theApp is "Numbers" then
		set p2app to "/Applications/Numbers.app"
	else if theApp is "Keynote" then
		set p2app to "/Applications/Keynote.app"
	else
		set p2app to theApp
	end if
	activate application p2app
	tell application "System Events" to tell (first process whose frontmost is true) to tell menu bar 1 to ¬
		tell menu bar item mt to tell menu 1 to click menu item mi
	
end selectMenu

#=====
(*
my selectSubMenu("Pages",6, 4, 26)
==== Uses GUIscripting ====
*)
on selectSubMenu(theApp, mt, mi, ms)
	if theApp is "Pages" then
		set p2app to "/Applications/Pages.app"
	else if theApp is "Numbers" then
		set p2app to "/Applications/Numbers.app"
	else if theApp is "Keynote" then
		set p2app to "/Applications/Keynote.app"
	else
		set p2app to theApp
	end if
	activate application p2app
	tell application "System Events" to tell (first process whose frontmost is true) to tell menu bar 1 to ¬
		tell menu bar item mt to tell menu 1 to tell menu item mi to tell menu 1 to click menu item ms
end selectSubMenu

#=====
(*
useful to get the indexs of the triggered item
my select_Menu("Numbers", 5, 16) (* Insert > FileName *)
*)
on select_Menu(theApp, mt, mi)
	if theApp is "Pages" then
		set p2app to "/Applications/Pages.app"
	else if theApp is "Numbers" then
		set p2app to "/Applications/Numbers.app"
	else if theApp is "Keynote" then
		set p2app to "/Applications/Keynote.app"
	else
		set p2app to theApp
	end if
	activate application p2app
	tell application "System Events" to tell (first process whose frontmost is true) to tell menu bar 1
		get name of menu bar items
		(*{
01 - "Apple", 
02 - "Numbers", 
03 - "Fichier", 
04 - "Édition", 
05 - "Insertion", 
06 - "Tableau", 
07 - "Format", 
08 - "Disposition", 
09 - "Présentation", 
10 - "Fenêtre", 
11 - "Partage", 
12 - "Aide"}
*)
		get name of menu bar item mt
		-- {"Insert"}
		tell menu bar item mt to tell menu 1
			get name of menu items
			(* {
01 - "Feuille", 
02 - missing value, 
03 - "Tableau", 
04 - "Graphique", 
05 - "Figure", 
06 - "Zone de texte", 
07 - "Fonction", 
08 - "Ligne de connexion", 
09 - missing value, 
10 - "Remplissage", 
11 - missing value, 
12 - "Rangs copiés", 
13 - "Colonnes copiées", 
14 - missing value, 
15 - "Date et heure", 
16 - "Nom du fichier", 
17 - "Numéro de page", 
18 - "Nombre de pages", 
19 - missing value, 
20 - "Commentaire", 
21 - "Lien", 
22 - "Saut de colonne", 
23 - missing value, 
24 - "Choisir."}
*)
			get name of menu item mi
			--{"Nom du fichier"}
			click menu item mi
		end tell -- menu bar.
		
	end tell -- System Events
end select_Menu

#=====
(*
useful to get the indexs of the triggered item
my select_SubMenu("Numbers", 6, 14, 3) (* Table > Footer rows > 2 *)
*)
on select_SubMenu(theApp, mt, mi, ms)
	if theApp is "Pages" then
		set p2app to "/Applications/Pages.app"
	else if theApp is "Numbers" then
		set p2app to "/Applications/Numbers.app"
	else if theApp is "Keynote" then
		set p2app to "/Applications/Keynote.app"
	else
		set p2app to theApp
	end if
	activate application p2app
	
	tell application "System Events" to tell (first process whose frontmost is true) to tell menu bar 1
		get name of menu bar items
		(*{
01 - "Apple", 
02 - "Numbers", 
03 - "Fichier", 
04 - "Édition", 
05 - "Insertion", 
06 - "Tableau", 
07 - "Format", 
08 - "Disposition", 
09 - "Présentation", 
10 - "Fenêtre", 
11 - "Aide"}
*)
		get name of menu bar item mt
		-- {"Tableau"}
		tell menu bar item mt to tell menu 1
			get name of menu items
			(* {
01 - "Nouveau.", 
02 - "Ouvrir.", 
03 - "Ouvrir un document récent", 
04 - missing value, 
05 - "Fermer", 
06 - "Tout fermer", 
07 - "Enregistrer.",
08 - "Enregistrer sous.", 
09 - "Dupliquer", 
10 - "Renommer.", 
11 - "Déplacer vers.", 
12 - "Revenir à ", 
13 - "Exporter vers", 
14 - missing value, 
15 - "Enregistrer comme modèle.", 
16 - missing value, 
17 - "Réduire la taille du fichier", 
18 - missing value, 
19 - "Définir le mot de passe.", 
20 - missing value, 
21 - "Imprimer.", 
22 - "Pas d'aperçu avant l'impression."}
*)
			get name of menu item mi
			-->"Exporter vers",
			tell menu item mi to tell menu 1
				get name of menu items
				(* {
1 - "PDF.", 
2 - "Excel.", 
3 - "CSV.", 
4 - "Numbers '09."}
*)
				get name of menu item ms
				--> "CSV."
				click menu item ms
			end tell -- menu item.
			
		end tell -- menu bar.
		
	end tell -- System Events
end select_SubMenu

#=====

on dateTimeStamp()
	# thanks to Nigel Garvey, returns yyyyMMddThhmmss
	tell (current date) to return (((its year) * 10000 + (its month) * 100 + (its day)) as text) & "T" & text 2 thru -1 of ((1000000 + (its hours) * 10000 + (its minutes) * 100 + (its seconds)) as text)
end dateTimeStamp

#=====

Yvan KOENIG (VALLAURIS, France) vendredi 8 novembre 2013 19:12:10