I have a database that imports data from a csv file, which is downloaded from a governmental website. The government, in its infinite wisdom, has decided to offer the data only in Excel format. I can, of course, open the Excel file and save the data as a csv file, then import the csv file, and that works fine, but requires extra user steps to open and save the Excel file. I like to simplify the user steps, so my question is, has anyone written a procedure, which I assume would include an AppleScript, to open an Excel file and save is a csv file?
I have a lifelong aversion to AppleScript, which has always seemed obtuse and difficult to use, so I am practically inept at using it. Or can Automator help with process? So any help would be appreciated.
I don’t know the answer to this. But I have some ideas of other places you could ask this question and possibly get help.
Everyone does. There is one tool that can really help reveal the magic behind the curtain – Script Debugger by Late Night software. It’s $99 but it might pay for itself just in solving this one problem for you (of course I can’t guarantee that, but it is pretty amazing).
Disclosure – I am a friend of Mark Aldritt, the author of Script Debugger. But I was a very satisfied user of the software before I met him. Apple should buy this program and include it in the system.
I did this years ago without AppleScript, using macros in an old version of Excel. I’ll send Tom my solutions off-thread because it involves a lot of code.
This can be achieved with Hazel and a Python script. The following script could be used as a starting point (requires openpyxl that you can install via Macports, Homebrew, or PyPi):
#!/opt/local/bin/python
import openpyxl
import csv
import sys
inputfile = sys.argv[1]
outputfile = inputfile.split(".")[0] + ".csv"
wb = openpyxl.load_workbook(inputfile, data_only=True)
sh = wb.active
with open(outputfile, 'w', newline="") as f:
col = csv.writer(f, delimiter=';')
for row in sh.rows:
col.writerow([cell.value for cell in row])
Yes, it can, even when it has no readymade workflow for this. It has a recording function that can reproduce menu clicks etc. But script solutions depend on having an identical window position on your screen, identical folder hierarchy, and so on. Many causes for generating errors.
So I think you should have a litte trust in your clients. Everybody can open an Excel document — if not in Excel, then in Numbers, GoogleSheets, OpenOffice, LibreOffice, or FreeOffice — and save the contents as a CSV file.
Here’s an Applescript I found online (Apple discussion forum). I tried it, and it seems to work, using Numbers as an intermediary.
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
--begin script
--check for an output folder and create it if necessary:
set docs_folder to (path to downloads folder)
set conversion_folder to (docs_folder as text) & "Converted Excel files"
tell application "Finder" to if not (exists folder conversion_folder) then ¬
make new folder at docs_folder with properties {name:"Converted Excel files"}
--ask user to choose Excel files (.xlsx or xls):
set file_list to (choose file of type {"org.openxmlformats.spreadsheetml.sheet", ¬
"com.microsoft.Excel.xls"} with multiple selections allowed)
--process each file in turn by opening in Numbers, exporting as csv to the ouput folder, then closing the original
repeat with each_file in the file_list
tell application "Numbers"
activate
open each_file
repeat while not (exists document 1) -- wait for Excel file to open in Numbers
end repeat
set file_name to name of document 1
export document 1 to file (conversion_folder & ":" & file_name & ".csv") as CSV
close document 1 without saving
end tell
end repeat
It converts a selected group of Excel files to .csv files.
My solution to Tom’s problem is to use the macro facility in Excel. I wrote macros in Visual Basic to do this back in the 1990s and my most recent version used Microsoft Office 2011, importing files into Panorama 6. Current versions of Excel have replaced VBA with a new programming language which I am not at all familiar with. But, if it still supports the recording of macros and the existence of auto-open macros, there should be no problem.
In its simplest form, my method is as follows:
In Panorama:
Load the name of the Excel file to be converted onto the clipboard.
Use the filelist statement to create a dummy Excel file.
Use the openanything statement to open an Excel file which contains an auto-open macro (the equivalent of a .Initialize procedure).
Run a while forever loop until the dummy file no longer exists. This ensures that Panorama doesn’t out-run the very slow Excel macro.
The macro:
Pastes the contents of the clipboard into its column A.
Reads the file name, opens it and saves it as a text file.
Deletes the dummy file.
Quits Excel.
The process doesn’t have to be stand-alone. I had it embedded in a set of large procedures which did a whole lot of other things. The process can easily be expanded to cater for multiple folder paths and multiple files for conversion.
You can use a similar technique to export Panorama data into a formatted Excel file like the sample output below which comprises six sheets.
Michael, I remember that Microsoft has added VBA and (kind of) dropped its proprietary Excel 4 Macro language some long, long time ago, but I can confirm, that I am still running Excel documents that contain such old Macros for some functionality, and on the other hand Visual Basic continues to be the main programming environment in the recent versions of MS Office.
Thanks to everyone for the suggestions. I did find an AppleScript that works:
let lvfile="/Users/tgcooper/Desktop/[FILE NAME].xlsx" //use complete file path, not ~/...
let lvcsvfile=replace(lvfile,".xlsx",".csv")
applescript |||
tell application "Microsoft Excel"
activate
set my_file to POSIX file $«lvfile»$
open my_file
set my_file to POSIX file "/Users/tgcooper/Desktop/NLTWNH26.csv"
save workbook as active workbook filename $«lvcsvfile»$ file format CSV Mac file format with overwrite
close active workbook
end tell
|||Tom, as I recall, Panorama 6 could pass parameters to AppleScript. If Panorama X can still do this you can bypass the need to enter a file name into your script - always have your incoming file(s) in a dedicated folder and use the listfiles( function to build an array of file names which can be passed as parameters.
Hi Michael, you are right; you can pass variables to an AppleScript. In my case, files are being imported one at a time, and the file path will be used in the AppleScript that it gets from a PanX variable.