Wednesday, May 23, 2018

#1 2017-06-23 06:25:36 am

Adam239
Member
Registered: 2016-10-14
Posts: 71

Microsoft Excel - Save CSV as XLSX

Hi,

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:

Applescript:

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


running Sierra 10.12.5

Offline

 

#2 2017-06-23 07:12:16 am

DJ Bazzie Wazzie
Member
From:: the Netherlands
Registered: 2004-10-20
Posts: 2751
Website

Re: Microsoft Excel - Save CSV as XLSX

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:

Applescript:

set theFile to "Macintosh HD:Users:itsme:Desktop:test.csv"

set pythonScript to "import os
import glob
import csv
import openpyxl # from [url]https://pythonhosted.org/openpyxl/[/url] 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"

Last edited by DJ Bazzie Wazzie (2017-06-23 07:15:36 am)

Offline

 

#3 2017-06-23 10:43:12 am

haolesurferdude
Member
Registered: 2010-02-19
Posts: 116

Re: Microsoft Excel - Save CSV as XLSX

Your script works with a minor adjustment to the output format.

Applescript:


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

Offline

 

#4 2017-06-23 11:21:27 am

Adam239
Member
Registered: 2016-10-14
Posts: 71

Re: Microsoft Excel - Save CSV as XLSX

Thanks for this solution SurferDude smile
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?


running Sierra 10.12.5

Offline

 

#5 2017-06-23 12:10:41 pm

haolesurferdude
Member
Registered: 2010-02-19
Posts: 116

Re: Microsoft Excel - Save CSV as XLSX

Try adding the .xslx suffix to the file name.

Offline

 

#6 2017-06-23 12:28:07 pm

Adam239
Member
Registered: 2016-10-14
Posts: 71

Re: Microsoft Excel - Save CSV as XLSX

Thats done it!
Thanks!!!


running Sierra 10.12.5

Offline

 

#7 2018-05-08 06:58:28 pm

Rayco
Member
Registered: 2017-05-12
Posts: 4

Re: Microsoft Excel - Save CSV as XLSX

I'm trying something similar, just can't get it to work at all...


- - - -

set INPUT_FILE_NAME to "/Users/ray/Dropbox/Public/DMX-LEADS.csv"

set OUTPUT_FILE_NAME to "/Users/ray/Dropbox/Public/converted.csv"

tell application "Microsoft Excel"
    activate
   
    open INPUT_FILE_NAME
   
    tell active workbook to save workbook as filename OUTPUT_FILE_NAME file format CSV Windows file format
   
    close active workbook
   
end tell

- - -

Any ideas and Thanks in advance.


Filed under: excel, save, windows, csv

Offline

 

#8 2018-05-09 08:08:03 am

Yvan Koenig
Member
Registered: 2006-09-14
Posts: 3225

Re: Microsoft Excel - Save CSV as XLSX

Are you sure that Excel accept POSIX pathnames ?

Yvan KOENIG running High Sierra 10.13.4 in French (VALLAURIS, France) mercredi 9 mai 2018 16:07:53

Offline

 

#9 2018-05-10 01:48:28 am

Rayco
Member
Registered: 2017-05-12
Posts: 4

Re: Microsoft Excel - Save CSV as XLSX

Sorry I don't know.

I do know that this opens the file OK and closes it, but does not save the output file.

Just trying to re-save file as Windows CSV as I am on Mac and eBay File Exchange does not accept Mac CSV.

Offline

 

#10 2018-05-10 04:12:21 am

Nigel Garvey
Moderator
From:: Warwickshire, England
Registered: 2002-11-20
Posts: 4552

Re: Microsoft Excel - Save CSV as XLSX

Hi.

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:

Applescript:

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.

Last edited by Nigel Garvey (2018-05-10 04:16:51 am)


NG

Offline

 

#11 2018-05-10 10:00:50 am

Rayco
Member
Registered: 2017-05-12
Posts: 4

Re: Microsoft Excel - Save CSV as XLSX

Thanks Nigel, That seems to have cracked it.

Just need to put in a command to save without the dialogue box and it will be perfect.

I use scripts a bit but very simple ones, must try to study up a bit.


Cheers

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)