0

We have a department that has been using a copy of an Apple Numbers file to input daily data into and save them daily. The numbers file is designed with multiple tables etc. - but they have the same layout.

They have been doing this for about 2 years (10 files per day) now, so there are about 7000+ number files.

I need to extract this data into a singular Spreadsheet under columns.

My idea is to convert them into CSV/XLS files and then get data from each of these files in a singular file using something like

='[file.xlsx]sheet'!$A$1

However, I have searched everywhere I could on batch converting these files into CSV.

I have strung together a very rudimentary "Watch me do" Automator workflow.

Step 1 - It uses the "Ask for Finder Items"

Step 2 - It uses the "Open Finder Items"

Step 3 - It uses the "Watch me do" workflow - Clicks on "Files" > CSV > Next > Export

This works fine for one file. But I need it to circle back to the second file and repeat the process. Any idea on how I can get this done for multiple files?

Thank you so much.

4
  • Hi and welcome to Apple.SE! The first and foremost thing to do when you have a question is to search. Even if you find things that don't work or you don't understand, people then know what you did and did not try and will suggest answers accordingly. Have you for example found this answer on Stackoverflow dealing with the same issue? May 18 at 15:51
  • Hey Saaru, thanks for taking the time to respond and my apologies if I came across as someone who didn't search before asking. As a matter of fact, I did come across that answer. I tried the second solution - and I am faced with a problems. 1. I get a "Could not open file. Operation not permitted" error when running the script. This goes away if I have the file open. So I have to open 30-40 files at a time, run the script, then close them and move on. I have added both Finder and Terminder to Full Disk access. Have searched around for this perplexing issue as well - but haven't found anything. May 18 at 21:28
  • Excellent, please edit your question to include the things you've tried so far. By "second solution" do you mean this answer? Sorting of answers can differ per person... The permission issue is weird indeed, did you try the suggestion in the comments as well? May 18 at 22:30
  • 1
    If Python is an option then fairly simple script using numbers-parser could do it automagically May 19 at 6:12

1 Answer 1

2

Create a folder on your desktop and name it "CSV_Documents". This will be the location all of your exported .csv files will be saved to.

Paste this following AppleScript code into a new Script Editor.app document. Running this code will export all currently opened Numbers.app documents to .csv files, then close each Numbers doc (without saving) after it has been exported. If there are any errors trying to export an open document, this file won't be exported and will remain open in Numbers.app and will continue on to process the next file.

NOTE: This is a bare-bones version but it gets the job done.

set saveToFolder to (path to desktop as text) & "CSV_Documents:"

set theFiles to choose file with prompt "Select your numbers documents to open" of type ¬
    "numbers" with multiple selections allowed

tell application "Numbers"
    open theFiles
    set documentNames to name of documents
    
    repeat with thisDocument in documentNames
        try
            export document thisDocument ¬
                to file (saveToFolder & thisDocument & ".csv") as CSV
            close document thisDocument saving no
        end try
    end repeat
    
end tell

NOTE: This version has more "Bells & Whistles".

property saveToFolder : (path to desktop as text) & "CSV_Documents:"

try
    alias saveToFolder
on error
    do shell script "mkdir ~/Desktop/CSV_Documents"
end try

activate
set theFiles to choose file with prompt ¬
    "Select \".numbers\" files to open" of type ¬
    "numbers" with multiple selections allowed

if (count of theFiles) > 50 then
    activate
    display alert "TOO MANY FILES" message ¬
        "To avoid timing out errors, select only up to 50 files." as critical ¬
        buttons {"OK"} default button 1
    return
end if

set documentNames to {}

repeat with i in theFiles
    tell application "System Events" to set {theFileName, nameExtension} to ¬
        {name, name extension} of i
    set end of documentNames to theFileName
end repeat

tell application "Numbers"
    with timeout of 500 seconds
        open theFiles
    end timeout
    
    repeat with i from 1 to count of documentNames
        set thisDocument to item i of documentNames
        try
            export document thisDocument to file ¬
                (saveToFolder & (my text 1 thru -8 of thisDocument) & "csv") as CSV
            close document thisDocument saving no
        end try
    end repeat
    
end tell

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .