Previous 1 2 Next 21 Replies Latest reply: Apr 15, 2010 7:52 AM by KOENIG Yvan
eduqate Level 1 Level 1 (0 points)
I'm trying to investigate the feasibility of automating the production of expenses claims using a claim sheet template and data from a csv file.

1) Could I drag and drop a csv file onto Numbers and have the contents inserted into a specified part of the worksheet?

2) or could I write some appple script which sets the value of a cell range to the contents of a csv file?

Any pointers gratefully received.

powerbook g4 15", Mac OS X (10.5.8)
  • Badunit Level 6 Level 6 (11,400 points)
    Yes, you can drag and drop a CSV file into a specific spot in a table.
  • eduqate Level 1 Level 1 (0 points)
    How about automatically? I want to script this if I can.

    Can I scriptably insert a csv into a specified cell range?

    The only way I can see for now is to read in the csv file into applescript, parse it's contents into arrays and then set the value of each cell.
  • Level 8 Level 8 (41,790 points)
    Badunit wrote:
    Yes, you can drag and drop a CSV file into a specific spot in a table.



    Some times ago, I tested that with a csv created by Numbers itself and it failed.

    As I know that your responses are serious, I made new attempts and got the same failure here on my French system.

    I was a bit surprised but Eureka, the light came.

    I tried with an English CSV (one using the comma as delimiter).
    Bingo, it works. So I'm glad to say to every users:

    when you want to insert CSV datas in Numbers,
    (1) if they use the comma on a system using the period as decimal separator, you may use import or drag & drop.
    (2) if they use the comma on a system using the comma as decimal separator, use drag & drop but you will have to apply search/replace to replace the decimal period by the decimal comma.
    (3) if they use the semi-colon on a system using the comma as decimal separator, use import.
    (4) if they use the semi-colon on a system using the period as decimal separator, use drag & drop but you will have to apply search/replace to replace the decimal comma by the decimal period.

    Yvan KOENIG (VALLAURIS, France) lundi 21 septembre 2009 14:09:40
  • eduqate Level 1 Level 1 (0 points)
    I have marked the question as answered because I don't think it will be. If you drag and drop a csv onto an open table then you can choose the top LH corner of the imported data. However none of this seems to be applescriptable.

    My current thoughts of solution is to have an applescript which opens a user supplied csv file, parses it and then inserts it into fixed rows of a named table in a worksheet. I could be some time

    Thanks all for your help
  • Level 8 Level 8 (41,790 points)
    I apologize but when I was writing my response, the post asking for a script was not displayed.
    I posted my response and jumped in an other thread.

    The wanted script is quite easy to write.

    May you tell me if you are using csv using the comma or using the semi-colon as delimiter ?

    If you want to include that in a more complex script, I assume that when calling the script, I may know some parameters:

    name of the spreadsheet
    name of the active sheet
    name of the active table
    row and column of the target cell
    full pathname of the csv file.

    I will be back tomorrow and given your complementary infos I will write the beast.

    Yvan KOENIG (VALLAURIS, France) lundi 21 septembre 2009 21:53:04
  • eduqate Level 1 Level 1 (0 points)
    You are awesomely helpful.

    I am in the UK so using comma delimited fields. yes in general those 5 parameters are sufficient.

    The only external processing required is to ensure the csv file does not contain too many lines (otherwise it will overflow the space allocated for it in the template). This is OK because the ledger software I use generates one csv record per expense item, these need to be merged so all entries for a date appear on one csv record.

    But a generalised "import this csv file into a given spreadsheet/sheet/table/position" would be of use to lots of people.
  • Level 8 Level 8 (41,790 points)
    May you send a sample csv file to my mailbox.
    It will be useful to take care of every features.

    Click my blue name to get my address.

    Yvan KOENIG (VALLAURIS, France) lundi 21 septembre 2009 22:51:25
  • Level 8 Level 8 (41,790 points)
    I repeat:

    May you send a sample csv file to my mailbox ?
    It will be useful to take care of every features

    Click my blue name to get my address.

    Yvan KOENIG (VALLAURIS, France) mardi 22 septembre 2009 22:53:17
  • eduqate Level 1 Level 1 (0 points)
    Apologies for not getting back to you, email has been sent.
  • hohabadu Level 1 Level 1 (0 points)
    Do you have a solution for this problem. I have tried the same without success.
    I'll be very happy, if you could post a sample code to insert the content of a csv. It is no problem for me to change ";" to "," or any other character with the help of the text item delimiters of applescript, but i don't know how to insert the content to a well formatted cell/range of a table...
  • Level 8 Level 8 (41,790 points)
    Maybe this script may help.

    --

    --[SCRIPT insertCSV_inNumbers]
    (*
    Enregistrer le script en tant que Script : insertCSV_inNumbers.scpt
    déplacer le fichier ainsi créé dans le dossier
    <VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:

    copiez vos données depuis la page source
    menu Scripts > Numbers > insertCSV_inNumbers
    Après conversion, les données seront insérées dans la table active de Numbers..

    --=====

    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.
    (* à vérifier avec Snow Leopard *)
    Cochez la case "Afficher le menu des scripts dans la barre de menus".

    --=====

    Save the script as Script : insertCSV_inNumbers.xxx

    Move the newly created file into the folder:
    <startup Volume>:Users:<yourAccount>:Library:Scripts:

    copy datas from the source page
    menu Scripts > Numbers > insertCSV_inNumbers
    After conversion, the datas will be inserted in the active table in Numbers.

    --=====

    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)
    2009/12/17

    *)
    --=====

    property liste : {}
    property liste2 : {}

    on run
    my nettoie()
    set enTexte to the clipboard as text
    if enTexte contains ";" then
    set delim to ";"
    else
    set delim to ","
    end if
    set my liste to paragraphs of enTexte

    set {dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()

    tell application "Numbers" to tell document dName to tell sheet sName to tell table tName
    set rowMax to count rows
    set colMax to count columns
    set nbRows to count of liste
    if rowNum1 - 1 + nbRows > rowMax then
    repeat rowNum1 - 1 + nbRows - rowMax times
    add row below last row
    end repeat
    set rowMax to count rows
    end if -- nbRows + …
    repeat with r from 1 to nbRows
    set my liste2 to my decoupe(item r of my liste, delim)
    set nbCols to count of my liste2
    if colNum1 - 1 + nbCols > colMax then
    repeat colNum1 - 1 + nbCols - colMax times
    add column after last column
    end repeat
    set colMax to count columns
    end if -- nbCols + …
    tell row (rowNum1 + r - 1)
    repeat with c from 1 to nbCols
    set value of cell (colNum1 + c - 1) to (item c of my liste2) as text
    end repeat -- c
    end tell -- row …
    end repeat -- r
    end tell -- Numbers …
    my nettoie()
    end run

    --=====

    on nettoie()
    set my liste to {}
    set my liste2 to {}
    end nettoie

    --=====

    on getSelParams()
    local r_Name, t_Name, s_Name, d_Name, col_Num1, row_Num1, col_Num2, row_Num2
    set {d_Name, s_Name, t_Name, r_Name} to my getSelection()

    if r_Name is missing value then
    if my parleAnglais() then
    error "No selected cells"
    else
    error "Il n'y a pas de cellule sélectionnée !"
    end if
    end if

    set two_Names to my decoupe(r_Name, ":")
    set {row_Num1, col_Num1} to my decipher(item 1 of two_Names, d_Name, s_Name, t_Name)
    if item 2 of two_Names = item 1 of two_Names then
    set {row_Num2, col_Num2} to {row_Num1, col_Num1}
    else
    set {row_Num2, col_Num2} to my decipher(item 2 of two_Names, d_Name, s_Name, t_Name)
    end if
    return {d_Name, s_Name, t_Name, r_Name, row_Num1, col_Num1, row_Num2, col_Num2}
    end getSelParams

    --=====
    (*
    set {rowNumber, columnNumber} to my decipher(cellRef,docName,sheetName,tableName)
    apply to named row or named column !
    *)
    on decipher(n, d, s, t)
    tell application "Numbers" to tell document d to tell sheet s to tell table t to return {address of row of cell n, address of column of cell n}
    end decipher

    --=====
    (*
    set { d_Name, s_Name, t_Name, r_Name} to my getSelection()
    *)
    on getSelection()
    local _, theRange, theTable, theSheet, theDoc, errMsg, errNum

    tell application "Numbers" to tell document 1
    repeat with i from 1 to the count of sheets
    tell sheet i
    set x to the count of tables
    if x > 0 then
    repeat with y from 1 to x
    try
    (selection range of table y) as text
    on error errMsg number errNum
    set {_, theRange, _, theTable, _, theSheet, _, theDoc} to my decoupe(errMsg, quote)
    return {theDoc, theSheet, theTable, theRange}
    end try
    end repeat -- y
    end if -- x>0
    end tell -- sheet
    end repeat -- i
    end tell -- document
    return {missing value, missing value, missing value, missing value}
    end getSelection

    --=====

    on decoupe(t, d)
    local l
    set AppleScript's text item delimiters to d
    set l to text items of t
    set AppleScript's text item delimiters to ""
    return l
    end decoupe

    --=====

    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

    --=====
    --[/SCRIPT]
    --


    Yvan KOENIG (VALLAURIS, France) jeudi 17 décembre 2009 20:32:30
  • hohabadu Level 1 Level 1 (0 points)
    Hi Yvan
    Thank you very much for the example. I wrote a today a script which looks very very similiar to yours. That shows me, that the way seems to be the only one. It's a shame that there is no other way to fill the table from csv then pass every single cell with the help of a repeat. It works, but it is not the fastest way... The applescript library of Numbers is a little bit rudimentary.
    But once more, thank you for your help, Yvan
  • Level 8 Level 8 (41,790 points)
    In fact, there is an other way:

    build a block of datas using TABs between 'cells' and RETURNs between 'rows' then paste it.
    It works well but it requires the use of GUI scripting and when there is an other path I prefer let GUI scripting out of use.

    Yvan KOENIG (VALLAURIS, France) jeudi 17 décembre 2009 22:36:30
  • hohabadu Level 1 Level 1 (0 points)
    thats exactly the way i have done it before, but i have had the problem to hide some of the imported columns. If you hide the columns before the import with UI scripting, the hidden columns will not be filled with the data of the tsv. And i couldn't find a way to hide a column after the import via applescript... and there is no property to set a hidden status of a column and you could not set the width of a column to 0.0... very rudimentary applescript support...
Previous 1 2 Next