1

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.

Improve this question
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? Commented May 18, 2022 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. Commented May 18, 2022 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? Commented May 18, 2022 at 22:30
  • 2
    If Python is an option then fairly simple script using numbers-parser could do it automagically Commented May 19, 2022 at 6:12

1 Answer 1

Reset to default
3

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
Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.