Saving worksheets as CSV---FRUSTRATING!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dg - 23 Sep 2007 01:47 GMT

Hello,
I just spent the better part of two hours trying to figure out
something that ought to be very simple, but, in Excel, isn't.

I have an Excel file with several worksheets. What I want is to save
some of the worksheets as separate CSV files in the same directory as
the source file. Each CSV file should be named as the original source
file plus an underscore plus the worksheet name.

Example: Excel file called Source1.xls contains 3 worksheets called
WS1, WS2, and WS3. What I want is to have three additional files with
names Source1_WS1.csv, Source1_WS2.csv, and Source1_WS3.csv in the
same directory as Source1.xls containing its respective worksheets (if
the name of the worksheet would be preserved it would be better, but
not essential).

Sounds simple, right? Well, no.

Here's what I came up with. To simplify, in a first step I will
attempt to save all worksheets as CSV files.

set theFile to choose file

tell application "Microsoft Excel"
    activate
    open theFile
    set sheet_count to the count of sheets
    repeat with i from 1 to sheet_count
        my save_sheet(i)
    end repeat
    close active workbook without saving
end tell

on save_sheet(i)
    tell application "Microsoft Excel"
        set theSheet to worksheet i of active workbook
        set sheet_name to name of sheet i
        set fname to (full name of active workbook)
        set fname to text 1 thru -5 of fname --remove file extension
        set tname to fname & "_" & sheet_name & ".csv"
        activate object sheet i
        save as (sheet i) filename tname file format CSV
    end tell
end save_sheet

Well, what happens is that Excel CHANGES THE NAME OF THE ENTIRE
WORKBOOK upon saving the first worksheet (the first worksheet also
gets RENAMED). WHY OH WHY????

Therefore, the name of the second worksheet also contains the first,
etc.

What I get is:
- files Source1_WS1.csv, Source1_WS1_WS2.csv, and
Source1_WS1_WS2_WS3.csv are created
- the first (and only the first) worksheet in the original file is
renamed to Source1_WD1 (with the name truncated after a certain number
of characters)
- the original file name is changed to Source1_WS1_WS2_WS3.csv
(the last two changes are undone by the "close without saving"
statement, so the original file is preserved)

Why does Excel rename the whole file when I ask to save a
worksheet????!? That is nonsense.
Any idea on how to achieve what I want?
Thanks.

Reply to this Message

JE McGimpsey - 23 Sep 2007 13:58 GMT

> Why does Excel rename the whole file when I ask to save a
> worksheet????!? That is nonsense.

The reason it's not nonsense is because XL is doing exactly what you
tell it to, of course <g>...

FWIW, it's somewhat confusing in VBA, too. The important thing is that
XL can only save workBOOKs, not workSHEETs. So your script (using the
workSHEET save as method) saves the workbook with the new filename and
renames the worksheet, makes the new workbook the active workbook and
closes the original workbook. Then when your loop repeats, it operates
on the new active workbook (with the new name), and appends the new
worksheet name onto the current one.

The cleanest solution is to copy the worksheet to a new workbook (which
then becomes the active workbook), save that workbook, and close it.
Perhaps something like:

on save_sheet(i)
tell application "Microsoft Excel"
set theSheet to worksheet i of active workbook
set sheet_name to name of sheet i
set fname to (full name of active workbook)
set fname to text 1 through -5 of fname
set tname to fname & "_" & sheet_name & ".csv"
copy worksheet sheet i
save workbook as active workbook filename tname file format CSV
close active workbook
end tell
end save_sheet

Reply to this Message

dg - 23 Sep 2007 23:02 GMT

JE, thank you for your prompt answer.

Meanwhile, I found a solution myself, saving the original file name
and removing the subroutine:

set theFile to choose file

tell application "Microsoft Excel"
    activate
    open theFile
    set fname to (full name of active workbook)
    set fname to text 1 thru -5 of fname --remove file extension
    set sheet_count to the count of sheets
    repeat with i from 1 to sheet_count
        set sheet_name to name of sheet i
        set tname to fname & "_" & sheet_name & ".csv"
        save as (sheet i) filename tname file format CSV
    end repeat
    close active workbook without saving
end tell

This works, except that the sheet names in the .CSV files are renamed.

Your solution also work, and the sheets retain their correct names in
the CSV files.

I still think that it's a shame that Excel does not allow a simple
save operation on a worksheet. Waiting for the port of OpenOffice with
Aqua GUI to completely ditch M$ Office from my computer.

Reply to this Message

JE McGimpsey - 24 Sep 2007 01:42 GMT

> I still think that it's a shame that Excel does not allow a simple
> save operation on a worksheet.

While I can understand your desire, it wouldn't make a lot of sense to
do so. Worksheets can only exist as part of workbooks, so saving a
worksheet necessarily requires saving its parent workbook. It's not that
difficult to copy the sheet to a new workbook and save it.

While some folks might use a shortcut that combines those functions, I
suspect there's not a whole lot of them.

> Waiting for the port of OpenOffice with Aqua GUI to completely ditch
> M$ Office from my computer.

NeoOffice has had an Aqua GUI for a long time:

http://www.neooffice.org/

OpenOffice has an alpha available

http://porting.openoffice.org/mac/download/aqua.html

However, IIRC, both mimic XL's Save As behavior, and neither have
AppleScript support, so I'm not sure what you're expecting to be
better...

Reply to this Message

dg - 24 Sep 2007 05:56 GMT

Correction: Your solution DOES NOT WORK: The individual worksheets do
NOT get saved as CSV, they still get saved as .XLS workbooks. I think
that's because of the use of "save workbook" instead of "save as
(sheet)".

So, here is my final solution, for the benefit of others. I have now
added the ability for the user to enter which sheets get saved as CSV.

set theFile to choose file

display dialog "Enter sheets to save (comma-delimited)" default answer
"1"
set theList to text returned of the result
try
    set oldDelims to AppleScript's text item delimiters -- save their
current state
    set AppleScript's text item delimiters to {","} -- declare new
delimiters
    set sheet_list to every text item of theList
    set AppleScript's text item delimiters to oldDelims -- restore them
on error
    set AppleScript's text item delimiters to oldDelims -- restore them
in case something went wrong
end try

tell application "Microsoft Excel"
    activate
    open theFile
    repeat with i in sheet_list
        my save_sheet(i as integer)
    end repeat
    close active workbook without saving
end tell

on save_sheet(i)
    tell application "Microsoft Excel"
        set sheet_name to name of sheet i
        set fname to (full name of active workbook)
        set fname to text 1 thru -5 of fname --remove file extension
        set tname to fname & "_" & sheet_name & ".csv"
        copy worksheet sheet i
        --save workbook as active workbook filename tname file format CSV
        save as (sheet 1) filename tname file format CSV
        close active workbook without saving
    end tell
end save_sheet

> In article <1190584966.461719.262...@k79g2000hse.googlegroups.com>,
>
[quoted text clipped - 5 lines]
> worksheet necessarily requires saving its parent workbook. It's not that
> difficult to copy the sheet to a new workbook and save it.

I strongly disagree. Why would exporting a worksheet "necessarily
require" saving its parent workbook? Incidentally, I am going through
all of this because, unfortunately, people keep sending me files in
proprietary Office formats. I really need Excel only to export these
files so I can use the program I WANT to process my files. I would be
much better off if people would use only ASCII text files in this
case.

Also, sometimes I get really large files. Having to copy them to a new
workbook is very inefficient and a waste of resource and processing
time. I guess it's kind of the "Microsoft Office" way of doing things.
No thanks.

> > Waiting for the port of OpenOffice with Aqua GUI to completely ditch
> > M$ Office from my computer.
>
> NeoOffice has had an Aqua GUI for a long time:
>
> http://www.neooffice.org/

I know, but that is excruciatingly slow (maybe because of its use of
Java).

> OpenOffice has an alpha available
>
> http://porting.openoffice.org/mac/download/aqua.html

Yes, but after reading their warning about not using the software on a
production machine, I prefer to hold off...

> However, IIRC, both mimic XL's Save As behavior, and neither have
> AppleScript support, so I'm not sure what you're expecting to be
> better...

Well, for one, it will be FREE.
In time, OpenOffice will completely replace Office. Or at least,
that's what I hope.

Reply to this Message

Mac Forum / Applications / Excel / September 2007

Saving worksheets as CSV---FRUSTRATING!