I have a CSV file open in Excel and I want to write some Applescript to save it in .xlsx file format. I have tried to save in the normal way, but Excel saves the CSV as a text file.
Can anyone help?
This is what I have so far:
set workbookName to "Testing"
set destinationPath to (path to desktop as text) & workbookName
tell application "Microsoft Excel"
set name of active sheet to "Sheet1"
tell workbook 1
tell sheet 1
save active workbook in destinationPath as Excel Workbook file format
end tell
end tell
end tell
Due to copyrights I cannot give you my CSV/XLS/XLSX osax but before that I’ve been working with Python. Python does have xlsx modules where it can easily read CSV data and write it away as an xlsx file. I used openpyxl module but there is also an xlsxwriter module for Python as well. After you’ve installed the modules openpyxl you can use the code below:
set theFile to "Macintosh HD:Users:itsme:Desktop:test.csv"
set pythonScript to "import os
import glob
import csv
import openpyxl # from https://pythonhosted.org/openpyxl/ or PyPI (e.g. via pip)
wb = openpyxl.Workbook()
ws = wb.active
csvfile = '" & POSIX path of theFile & "'
with open(csvfile, 'rU') as f:
reader = csv.reader(f, delimiter=';')
for r, row in enumerate(reader, start=1):
for c, val in enumerate(row, start=1):
ws.cell(row=r, column=c).value = val
wb.save(csvfile + '.xlsx')"
do shell script "echo " & quoted form of pythonScript & " | python"
Your script works with a minor adjustment to the output format.
set workbookName to "Testing"
set destinationPath to (path to desktop as text) & workbookName
tell application "Microsoft Excel"
set name of active sheet to "Sheet1"
tell workbook 1
tell sheet 1
save active workbook in destinationPath as Excel XML file format
end tell
end tell
end tell
Thanks for this solution SurferDude
That looks like it’s working.
The Python script Im trying to run on the .xlsx file it creates doesn’t not recognise the file format. Any ideas? If I save the same file manually, it seems to work ok.
When I inspect the files side-by side, the xlsx created by the script is a ‘Microsoft Excel workbook’ but the manually saved (working) file is a ‘Microsoft Excel Document’
Is there a way to save the file as a document, rather than as a workbook?
In haolesurferdude’s script above, and in scripts for many other applications, the save destination is the ‘save’ command’s ‘in’ parameter. The ‘as’ parameter is for the file format.
I don’t have Excel, but I guess your script would need to look something like this:
set DropboxPublicPath to (path to home folder as text) & "Dropbox:Public:"
set INPUT_FILE_NAME to DropboxPublicPath & "DMX-LEADS.csv"
set OUTPUT_FILE_NAME to DropboxPublicPath & "converted.csv"
tell application "Microsoft Excel"
activate
open INPUT_FILE_NAME
save active workbook in OUTPUT_FILE_NAME as CSV Windows file format
close active workbook
end tell
You may need to put the word ‘file’ between ‘open’ and ‘INPUT_FILENAME’ and between ‘in’ and ‘OUTPUT_FILE_NAME’. I’m assuming that ‘CSV Windows’ is the correct term for the format.
