Automatically create numbers documents from one worksheet with a table

I repeat that the terms of use prohibit rewards.

What is your exact goal ?

Do you keep the Pages documents AND the PDFs or only the PDFs ?

Do you keep the separate PDFs or only the concatenated one ?

All these needs may be easily satisfied with my script.

I have already a copy saving only in PDF format.

I am adding a feature :

use the late conversion rate as the default value in the dialog.

There is also an other possible addition :

download the current rate from the net but I'm not sure that you must use the current rate.

Maybe it must be the one matching the date stored in the cell D4.

Yvan KOENIG (VALLAURIS, France) mercredi 7 septembre 2011 11:44:32

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.0

My iDisk is : <http://public.me.com/koenigyvan>

Please : Search for questions similar to your own before submitting them to the community

enigma2k wrote:

my end goal would be just having the concatenated PDF. The .numbers documents can be delelted after I have the PDF.

Would be great if you already have this PDF function for this script.

At this time I have already a version which creates only the PDFs.

I have an other script which concatenate several PDFs.

I already tested it under 10.7 and was forced to change a detail because the first level in the folder hierarchy no longer accept that we create a file in it.

I didn't wished to require a password to apply so I changed the destination folder.

So, problem quite solved.

Wow this sounds like a really nice feature. Getting the current currency rate according to the date stored in cell D4 would be awesome. Is something advanced like this possible?

I already have scripts downloading the current conversion rate. I will try to see which change is required to get the rate according to a given date.

I have just a question :

Are the CSVs files containing datas related to a single date or to several ones ?

In first case the script must issue a sigle request,

in the 2nd one it must issue several ones.

Before midnight I will file a script concatenating the PDFs.

I have other works in progress so the code grabbing the conversion rate will come later.

Yvan KOENIG (VALLAURIS, France) mercredi 7 septembre 2011 18:01:18

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.0

My iDisk is : <http://public.me.com/koenigyvan>

Please : Search for questions similar to your own before submitting them to the community

(1) Thanks for the precision.

(2) The script concatenating the PDFs is running.

Iit delete the individual PDFs.

(3) I discovered an odd behavior on my machine.

A date like 29.08.11 is wrongly treated because here the format with period separators is yy.mm.dd

So, I wish to know if the dates are correctly inserted on your machine.

I assume that it would be safer to edit on the fly the string dd.mm.yy into dd/mm/yy which will be treated correctly.

Yvan KOENIG (VALLAURIS, France) mercredi 7 septembre 2011 19:27:25

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.0

My iDisk is : <http://public.me.com/koenigyvan>

Please : Search for questions similar to your own before submitting them to the community

enigma2k wrote:

I have just a question :

Are the CSVs files containing datas related to a single date or to several ones ?

In first case the script must issue a sigle request,

in the 2nd one it must issue several ones.

The data in a CSV contain different dates. So each line in the CSV is a different date (or it can be the same as well).

Thanks again for your time, I really appreciate this!

I had a similar question from the beginning: Will there be multiple clients in the CSV document, needing to be put on separate Invoices?

Jerry

enigma2k wrote:

I had a similar question from the beginning: Will there be multiple clients in the CSV document, needing to be put on separate Invoices?

Jerry

Yes each line in the csv is a new client and thus a new invoice.

So, does this mean that each row will have the possibility of multiple items and costs? Are there limits to the number of items?

Jerry

Right now it is just for 1 item with price, but I think adding more items is simple. With more items the csv must contain just more colums that are transfered to numbers.

I must have been fooled by the template you cited on the internet, which had multiple items. Sorry to interrupt the conversation.

I have great respect for Yvan's work and I'm glad that you and he have cooked up a solution with Applescript. Not being Applescript conversant, I would definitely use the MailMerge route here, especially since there's a one-to-one relationship between CSV records and Invoices. I read all of Yvan's scripts and someday hope to get on that wagon myself.

Thanks for bringing me up to speed.

Jerry

enigma2k wrote:

Jerrold Green1 wrote:

enigma2k wrote:

I had a similar question from the beginning: Will there be multiple clients in the CSV document, needing to be put on separate Invoices?

Jerry

Yes each line in the csv is a new client and thus a new invoice.

So, does this mean that each row will have the possibility of multiple items and costs? Are there limits to the number of items?

Jerry

Right now it is just for 1 item with price, but I think adding more items is simple. With more items the csv must contain just more colums that are transfered to numbers.

You are a funny guy.

You just think wrongly.

The script rely on the fact that it knows the stucture of the datas which it receive.

Changing this structure which is what you reveal now will fool it.

The code building each individual invoice must be entirely redesigned.

More items requires the insertion of rows in the table.

Were are you planning to insert these values in the set of datas describing an invoice.

At this time, the structure of a descriptor is :

1. DATE

2. Name

3.Total

4. email

5. Street

6. City

7. Country

8. Item name

9. Payments Status

I don't know where you got such a template but it violates every rules for an invoice.

It starts from the total and calculate the unit cost substracting Paypal fees and VAT when it is applying.

The fact that it 's made the reverse way is not my problem.

What is mine is that what may be achieved whith a single item can't be done this way with several ones.

A complete new structure is required starting from individual costs and calculating the total .

Inserting new rows will change the location of the cell receiving the "Payment Status"

The existence of new rows require serious changes in the formulas in rows PARTIAL SUM, VAT,SUBTOTAL, Paypal fee,and Total.

I just discover an other oddity in what you passed.

The formula calculating VAT use the cell D14 as a flag but there is nothing in this cell

The country is stored in cell D13

From my point of view the value labeled Total must be dropped and the datas must be passed with this structure :

1. DATE

2. Name

3. email

4. Street

5. City

6. Country

7. Payments Status

8. Item name

9. price

10. item#2

11. Price #2

12. item #3

13. Price #3

…

With that, the script would be able to fill the cells

"D4", "D9", "D23", "D10", "D11", "D12", "D13" (or "D14"), "A26"

Then, according to the number of values, it will fit articles lines which means:

insert a new row if there is more than one item

insert the item's name,

insert the item's price

insert the formula converting the Dollar price in the Euro one.

To do that, it would be a good idea to move the cell storing the conversion rate from E26 to E15 so that the formula calculating the converted price doesn't change. =ROUND($D*$E$15,2)

It's not a difficult task.

It's just boring to do the job twice.

--{code}

--[SCRIPT invoices_from_CSV_to_PDF]

(*

Enregistrer ce script en tant que Script : invoices_from_CSV_to_PDF.scpt

déplacer le fichier créé dans le dossier

<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:

Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.

Le fichier modèle "facture.nmbtemplate" doit être stocké dans le dossier :

<disqueDeDémarrage>:Utilisateurs:<compteUtilisateur>:Bibliothèque:Application Support:iWork:Numbers:Modèles:Mes modèles:

menu Scripts > Numbers > invoices_from_CSV_to_PDF

crée de nouveaux documents à partir du modèle personnel prédéfini

et insère des valeurs extraites d'un fichier csv.

Les fichiers sont stockés dans le dossier :

<disqueDeDémarrage>:Utilisateurs:<compteUtilisateur>:Documents:mes_factures:

--=====

L'aide du Finder explique:

L'Utilitaire AppleScript permet d'activer le Menu des scripts :

Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.

Cochez la case "Afficher le menu des scripts dans la barre de menus".

+++++++++

Save this script as Script : invoices_from_CSV_to_PDF.scpt

Move the newly created file into the folder:

<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:

Maybe you would have to create the folder Numbers and even the folder Applications by yourself.

The template file must be stored in the folder :

<startupVolume>:Users:<userAccount>:Library:Application Support:iWork:Numbers:Templates:My Templates:

menu Scripts > Numbers > invoices_from_CSV_to_PDF

will create new documents from the defined user template

and insert values grabbed from a csv file.

The files ares stored in the folder:

<startupVolume>:Users:<userAccount>:Documents:my_invoices:

--=====

The Finder's Help explains:

To make the Script menu appear:

Open the AppleScript utility located in Applications/AppleScript.

Select the "Show Script Menu in menu bar" checkbox.

--=====

Yvan KOENIG (VALLAURIS, France)

2011/09/05

2011/09/06 changed the treatment of the invoice #. Now the script read and write it only once when applied to a CSV file

2011/09/06 corrected a wrong instruction, added code to quit on exit, code to insert the conversion rate ($ to €)

2011/09/07 no longer issue Numbers documents but a single PDFs gathering the invoices.

2011/09/07 added a special treatment for the date which was treated wrongly on some machines.

*)

--=====

property |alias_du_modèle| : ""

property dossier_de_stockage : ""

property fichierNum : ""

--=====

(*

Entry point used if you double clic the script’s icon

or if you trigger it from the Scripts menu.

*)

on run

if my parleAnglais() then

set un_csv to choose file with prompt "Choose the csv file storing the datas" of type {"public.comma-separated-values-text"}

else

set un_csv to choose file with prompt "Sélectionner le fichier CSV contenant les données" of type {"public.comma-separated-values-text"}

end if

my main(un_csv)

end run

--=====

(*

Entry point used if you drag an drop the CSV’s icon on the script’s one.

*)

on open sel

set un_csv to item 1 of sel

tell application "System Events"

set maybe to type identifier of disk item (un_csv as text) is "public.comma-separated-values-text"

end tell

if maybe then

my main(un_csv)

else

if my parleAnglais() then

error "" & un_csv & return & "isn’t a CSV file !"

else

error "" & un_csv & return & "n’est pas un fichier CSV !"

end if

end if

end open

--=====

on main(le_csv)

(*

Define the properties : fichierNum, dossier_de_stockage , |alias_du_modèle|,

*)

local numbersLoaded, |mon_modèle|, dossier_factures, p2documents, d1, d2, p2ApplicationsSupport

local le_dossier, p2num, two_parms, nn, default_rate, conversionRate, en_liste, delim, i, une_ligne

local nom_facture, noms_factures, les_UNIXs, quotedPath2py, alias_existe, chemin_du_gros_PDF

tell application "System Events" to set numbersLoaded to (name of application processes) contains "Numbers"

(*

Define two parameters *)

if my parleAnglais() then

set |mon_modèle| to "invoice.nmbtemplate"

set dossier_factures to "my_invoices"

else

set |mon_modèle| to "facture.nmbtemplate"

set dossier_factures to "mes_factures"

end if

(*

Create the storage folder if it doesn’t exist. *)

set p2documents to (path to documents folder) as text

set dossier_de_stockage to p2documents & dossier_factures & ":"

tell application "System Events"

if not (existsfolderdossier_de_stockage) then makenewfolderat end of folderp2documentswith properties {name:dossier_factures}

end tell -- System Events

(*

Grab the localized names of the two default subfolders storing the templates *)

tell application "Numbers"

set d1 to localized string "Templates" (* nom local du dossier "Modèles" *)

set d2 to localized string "My Templates" (* nom local du dossier "Mes Modèles" *)

end tell -- Numbers

(*

Build a path to the template to use *)

set p2ApplicationsSupport to (path toapplication supportfromuser domain) as Unicode text

set |alias_du_modèle| to p2ApplicationsSupport & "iWork:Numbers:" & d1 & ":" & d2 & ":" & |mon_modèle| & ":"

(*

Check that the template is available in the standard folder *)

tell application "System Events"

if not (exists disk item |alias_du_modèle|) then

if my parleAnglais() then

error |alias_du_modèle| & return & "is unavailable ! Please make sure that the template file “" & |mon_modèle| & "” is installed in the folder “Numbers:" & d1 & ":" & d2 & "” before running this script."

else

error |alias_du_modèle| & return & "est introuvable ! Veuillez installer le fichier modèle « " & |mon_modèle| & " » dans le dossier « Numbers:" & d1 & ":" & d2 & " » avant de relancer ce script."

end if

end if

end tell

set |alias_du_modèle| to |alias_du_modèle| as alias

(*

According to the template’s name, define the name of the text file storing the last used invoice # and the late currency rate *)

set fichierNum to |mon_modèle| & "_" & reverse of (characters of |mon_modèle|) & ".txt"

tell application "System Events"

set le_dossier to path of container of |alias_du_modèle|

set p2num to le_dossier & fichierNum

if not (exists file p2num) then

makenewfileat end of folderle_dossierwith properties {name:fichierNum}

write ("0" & return & 0.70602) tofilep2num(* mettez le numéro de départ de votre choix *)

end if -- not…

end tell -- System Events

(*

Read two parameters : late invoice # and late currency rate *)

set two_parms to (readfilep2num)

set nn to (first paragraph of two_parms) as integer

set default_rate to (second paragraph of two_parms) as real

if my parleAnglais() then

set conversionRate to my ask_a_number("Enter the current conversion rate ($ to €)", default_rate, "f")

else

set conversionRate to my ask_a_number("Donner le taux de conversion ($ en €)", default_rate, "f")

end if

(*

Read the source CSV file *)

set en_liste to paragraphs of (read le_csv)

if item 1 of en_liste contains "," then

set delim to ","

else

set delim to ";"

end if

(*

Init two lists useful to concatenate the PDFs *)

set {noms_factures, les_UNIXs} to {{}, {}}

(*

The main loop <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< *)

repeat with i from 2 to count of en_liste

set une_ligne to itemi of en_liste

(*

Assuming that there is no character 'delim' in the passed values, split a line of datas into values to insert in invoices. *)

if une_ligne > "" then

set nn to nn + 1 (* increment the invoice # and pass it in the format 00001 *)

set nom_facture to my cree_une_facture(my decoupe(une_ligne, delim), text -5 thru -1 of ("0000" & nn), conversionRate)

copy nom_facture to end of noms_factures

copy quoted form of POSIX path of (dossier_de_stockage & nom_facture) to end of les_UNIXs

end if

end repeat -- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

(*

Build a string with the paths to the individual invoices

"'/Users/<userAccount>/Documents/mes_factures/ma_facture_00028.pdf' '/Users/<userAccount>/Documents/mes_factures/ma_facture_00029.pdf' '/Users/<userAccount>/Documents/mes_factures/ma_facture_00030.pdf'" *)

set les_UNIXs to my recolle(les_UNIXs, space)

if my parleAnglais() then

set chemin_du_gros_PDF to "" & dossier_de_stockage & (do shell script "date +invoices_%Y%m%d-%H%M%S") & ".pdf"

else

set chemin_du_gros_PDF to "" & dossier_de_stockage & (do shell script "date +factures_%Y%m%d-%H%M%S") & ".pdf"

end if

(*

Concatenate the PDFs using a python script delivered by Apple in an Automator process. *)

set quotedPath2py to (quoted form of "/System/Library/Automator/Combine PDF Pages.action/Contents/Resources/join.py")

do shell scriptquotedPath2py & " -o " & quoted form of POSIX path of chemin_du_gros_PDF & space & les_UNIXs

(*

Open the newly created big PDF file *)

tell application "Finder" to open file chemin_du_gros_PDF

(*

Write the last used invoice # in the dedicated text file *)

write ((nn as text) & return & conversionRate) to file p2num starting at 1

if numbersLoaded then tell application "Numbers" to quit

(*

Delete the individual invoices.PDF *)

tell application "System Events"

repeat with nom_facture in noms_factures

deletedisk item (dossier_de_stockage & nom_facture)

end repeat

end tell

(*

Clean the three properties used so that their contents will not be saved in the script *)

my nettoie()

end main

--=====

on nettoie()

set |alias_du_modèle| to ""

set dossier_de_stockage to ""

set fichierNum to ""

end nettoie

--=====

on cree_une_facture(fiche_en_liste, |numéro|, conversion_rate)

local cellules_cibles, une_fiche, nouveau_nom, le_chemin, documents_ouverts, nom_du_document, i

(*

List of target cells *)

set cellules_cibles to {"D4", "D9", "D23", "D10", "D11", "D12", "D13", "A17", "A26"}

(*

Build the invoice name *)

if my parleAnglais() then

set nouveau_nom to "my_invoice_" & |numéro| & ".pdf"

else

set nouveau_nom to "ma_facture_" & |numéro| & ".pdf"

end if

(*

Create an invoice file to save in soon *)

set le_chemin to (dossier_de_stockage & nouveau_nom)

tell application "System Events"

(*

Theorically, this piece of code takin care of pre-existing file is no longer needed.

I leave it in case of some failure leaving some invoices in the folder. *)

if existsdisk itemle_chemin then

set date_de_modification to modification date of disk itemle_chemin

set stamped_name to text 1 thru -5 of nouveau_nom & "_" & my horoDateur(date_de_modification) & ".pdf"

set name of disk itemle_chemin to stamped_name

delay 0.2

end if

(*

Create the needed PDF file *)

makenewfileat end of folderdossier_de_stockagewith properties {name:nouveau_nom}

end tell -- System Events

(*

Now the file exists so we may coerce the pathname as alias *)

set le_chemin to le_chemin as alias

tell application "Numbers"

(*

Create a new document from the template *)

set documents_ouverts to name of documents

open|alias_du_modèle|

repeat while (count of documents) is (count of documents_ouverts)

delay 0.1

end repeat

set nom_du_document to name of document 1

(*

Fill the document with invoice # and passed values *)

tell document nom_du_document to tell sheet 1 to tell table 1

tell cell "D5"

set format to text(* so, leading zeroes will not be dropped *)

set value to |numéro|(* which is built as a string *)

end tell

(*

Coercion to text is required to pass the correct decimal separator *)

set value of cell "E26" to (conversion_rate as text)

repeat with i from 1 to count of cellules_cibles

try

if i = 1 then

set value of cell (item i of cellules_cibles) to my remplace((item i of fiche_en_liste), ".", "/")

else

set value of cell (item i of cellules_cibles) to (item i of fiche_en_liste) as text

end if

end try

end repeat

end tell -- document nom_du_document

(*

Save in the pre-created file *)

save document nom_du_document as "LSDocumentTypePDF" in le_chemin

(*

Enabled to close the doc automatically. CAUTION, the save command changed the document's name *)

closedocument 1 without saving

end tell -- Numbers

return nouveau_nom

end cree_une_facture

--=====

on parleAnglais()

local z

try

tell application "Numbers" to set z to localized string "Cancel"

on error

set z to "Cancel"

end try

return (z is not "Annuler")

end parleAnglais

--=====

on decoupe(t, d)

local oTIDs, l

set oTIDs to AppleScript's text item delimiters

set AppleScript's text item delimiters to d

set l to text items of t

set AppleScript's text item delimiters to oTIDs

return l

end decoupe

--=====

on recolle(l, d)

local oTIDs, t

set oTIDs to AppleScript's text item delimiters

set AppleScript's text item delimiters to d

set t to l as text

set AppleScript's text item delimiters to oTIDs

return t

end recolle

--=====

(*

replaces every occurences of d1 by d2 in the text t

*)

on remplace(t, d1, d2)

local oTIDs, l

set oTIDs to AppleScript's text item delimiters

set AppleScript's text item delimiters to d1

set l to text items of t

set AppleScript's text item delimiters to d2

set t to l as text

set AppleScript's text item delimiters to oTIDs

return t

end remplace

--=====

(*

Asks for an entry and checks that it is an floating number

set myInteger to my ask_a_number(Prompt, DefaultValue, "i")

set myFloating to my ask_a_number(Prompt, DefaultValue, "f")

*)

on ask_a_number(lPrompt, lDefault, IorF)

local lPrompt, lDefault, n

tell application (path tofrontmost applicationasstring)

if IorF is in {"F", "f"} then

set n to text returned of (display dialog (lPrompt & " (" & lDefault as text) & ")" default answer lDefault as text)

try

set n to n as number(* try to convert the value as an number *)

return n

on error

if my parleAnglais() then

display alert "The value needs to be a floating number." & return & "Please try again."

else

display alert "La valeur saisie doit être un nombre décimal." & return & "Veuillez recommencer."

end if

end try

else

set n to text returned of (display dialoglPromptdefault answerlDefault as text)

try

set n to n as integer(* try to convert the value as an integer *)

return n

on error

if my parleAnglais() then

display alert "The value needs to be an integer." & return & "Please try again."

else

display alert "La valeur saisie doit être un nombre entier." & return & "Veuillez recommencer."

end if

end try -- 1st attempt

end if -- IorF…

end tell -- application

(*

Here if the first entry was not of the wanted class

second attempt *)

tell application (path tofrontmost applicationasstring)

if IorF is in {"F", "f"} then

set n to text returned of (display dialog lPrompt & " (" & (1.2 as text) & ")" default answer lDefault as text)

try

set n to n as number(* try to convert the value as an number *)

return n

on error

--

end try

else

set n to text returned of (display dialoglPromptdefault answerlDefault as text)

try

set n to n as integer(* try to convert the value as an integer *)

return n

on error

--

end try -- 1st attempt

end if -- IorF…

end tell -- application

if my parleAnglais() then

error "The value you entered was not numerical !" & return & "Goodbye !"

else

error "La valeur saisie n’est pas numérique !" & return & "Au revoir !"

end if

end ask_a_number

--=====

on horoDateur(une_date)

tell une_date to return (((its year) * 10000 + (its month) * 100 + (its day)) as text) & "_" & text 2 thru -1 of ((1000000 + (its hours) * 10000 + (its minutes) * 100 + (its seconds)) as text)

end horoDateur

--=====

--[/SCRIPT]

--{code}

At least, the script may be interesting for every curious user.

Saving as PDF is not a complicated task. The unique oddity is that Apple Engineers use different strings to define the export mode.

It's "LSDocumentTypePDF" for Numbers but it's "SLDocumentTypePDF". It's perfectly ridiculous but we must live with that.

Concatenating the PDFs is not a trivial task.

Have fun.

Yvan KOENIG (VALLAURIS, France) mercredi 7 septembre 2011 23:02:11

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.0

My iDisk is : <http://public.me.com/koenigyvan>

Please : Search for questions similar to your own before submitting them to the community

enigma2k,

Would you mind sending a sample CSV file to my email address?

Thanks,

Jerry