Convert XLS files into CSV preserving UTF-8 encoding

Scripting Forums AppleScript | Mac OS X
#1

Hi,

I often try to convert Excel files (xls, xlsx) into CSV files maintaining the accented and special characters.

The only way I can do it is to copy and paste the content into Numbers and Export it as CSV with UTF-8 encoding.

Is there a way to use AppleScript to convert Excel files into CSV files preserving the UTF-8 encoding?

Thanks

#2

If the characters are all exporting OK as MacRoman, you could use something like this:

set fileRef to (open for access (choose file) with write permission)
set theData to read fileRef as text
set eof fileRef to 0
write theData to fileRef as «class utf8»
close access fileRef
#3

Hello Shane

As the original file is described as xls or xlsx, your scheme will return a lot of garbage.

Yvan KOENIG (VALLAURIS, France) samedi 8 septembre 2012 09:18:29

#4

You’re right, of course. I was thinking Excel .csv files, and assuming they encoded MacRoman correctly.

#5

This seems to work but it breaks the column if it finds a comma, hmmm


tell application "Microsoft Excel"
    activate

    set outFile to (path of active workbook)
    set fileName to (name of active workbook)
    set outFile to (outFile & ":" & fileName & ".csv")
    set openFile to open for access file outFile with write permission
    set eof openFile to 0

    set lastCol to count of columns of used range of active sheet
    set lastRow to count of rows of used range of active sheet

    repeat with r from 1 to lastRow
        set rowStr to (value of cell r of column 1 of active sheet)
        repeat with c from 2 to lastCol
            set cellVal to (value of cell r of column c of active sheet)
            set rowStr to rowStr & "," & cellVal
        end repeat
        write rowStr & return to openFile as «class utf8»
    end repeat
    close access openFile    
end tell
#6

Excel has a HUGE AppleScript dictionary (which I never used). Have you looked at it?
It has a ‘save as’ command, with a ‘file format’ property. Looks like it might be what you need.
You’d avoid composing the CSV formatted text in the script.

A comment on that scriptlet: it has read/write commands within an application tell block.
It’s considered ‘good practice’ not to do that. First gather all data from Excel, then do the writing.

#7

Thanks, I tried doing the Save As and selecting UTF-8 but it will still spit out garbage
 Hmmm

How should the block above be without read/write inside the tell block?

And why is it breaking the column when it finds a comma? :slight_smile:

Thanks

#8

Hi. :slight_smile:

Save Clean CSV file from Excel using Applescript - Stack Overflow

#9

Darn


This method should work, did not test

set theText to {} -- gather output

tell application "Microsoft Excel"
	activate
	set outFile to (path of active workbook)
	set fileName to (name of active workbook)
	set outFile to (outFile & ":" & fileName & ".csv")
	
	set lastCol to count of columns of used range of active sheet
	set lastRow to count of rows of used range of active sheet
	
	repeat with r from 1 to lastRow
		set rowStr to (value of cell r of column 1 of active sheet)
		repeat with c from 2 to lastCol
			set cellVal to (value of cell r of column c of active sheet)
			set rowStr to rowStr & "," & cellVal
		end repeat
		-- what have we?
		log rowStr
		-- add this line to the list
		set end of theText to rowStr & return
		
	end repeat
end tell

-- list to text
set {TID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, return}
set theText to theText as text
set AppleScript's text item delimiters to TID

-- write text
set openFile to open for access file outFile with write permission
set eof openFile to 0
write theText & return to openFile as «class utf8»
close access openFile

I added a log statement; check the events pane to see output lines as they are built. An empty cell should show up as an empty string.

Edit:

Ah, McUsr found a scriplet for Excel. Yes, garbage might be the result of ‘misspelled’ code


#10

That works but does not keep UTF-8 encoding, all the extra characters are garbled :frowning:

#11

This gives this error:
Can’t make file {missing value, “:”, missing value, “.csv”} into type «class fsrf».

#12

Um, to which script does this apply??

It would help to know which line causes the error.
But it looks like Excel didn’t return the name & path of the workbook - which suggests there was no open workbook.
The result is a list, because not all parts of ‘outfile’ are text.

#13

Sorry, it shows this line


set openFile to open for access file outFile with write permission

and gives this error


Result:
error "Can't make file {missing value, \":\", missing value, \".csv\"} into type «class fsrf»." number -1700 from file {missing value, ":", missing value, ".csv"} to «class fsrf»
#14

Hello

Excel dictionary states :

save – Save an object.
reference – the object for the command
[as Unicode text] – The file type in which to save the data.
[in alias] – The file in which to save the object.

So I guess that an instruction like :


tell application "Microsoft Excel"
	activate
	tell active workbook
		set dossier to path & ":"
		set fileName to name
	end tell
end tell
tell application "System Events"
	name extension of disk item (dossier & fileName)
	set csvName to (text 1 thru -(1 + (length of result)) of fileName) & "csv"
	set csvPath to (dossier & csvName)
	if not (exists disk item csvPath) then
		make new file at end of folder dossier with properties {name:csvName}
	end if
end tell
tell application "Microsoft Excel"
	save active workbook as "file type for csv" in alias csvPath
end tell

would do the trick.
Alas I don’t know which is the string to use as file type for csv

Yvan KOENIG (VALLAURIS, France) samedi 8 septembre 2012 12:29:00

#15

Yes, that was the only possibility, in this case. Can only occur when no workbook is open: Excel will return ‘missing value’ for name & path.

Well
 I have Office 2004, and that entry is quite different - no “Unicode text” anywhere. 2004 seems not to know about text encoding.
Pendolo, what version yours? Regulus’s script presumably works with 2008.

#16

Hello.

CSV Mac file format is the correct string from Excel 2008’s dictionary, try that!

Hopefully it works!

if not, we’ll use iconv on the darn file! :slight_smile:

#17

McUsr reminds me a bit of Sarah Palin, if she had taken a sudden interest in scripting.

#18

Hi. :slight_smile:

This saves an Exel worksheet as csv with utf8 encoding for me.

set fn to ((path to desktop folder as text) & "arb1.csv")
set pxn to quoted form of POSIX path of fn
do shell script "touch " & pxn
tell application "Microsoft Excel"
	save active sheet in fn as CSV Mac file format
end tell
#19

Sarah Palin
 LOL :smiley:

Nop it still breaks the special characters

I’m testing with some Czech language, in one of the columns the sentence is:

And in the CSV it spits out this:

#20

Thanks

On my side I wrote :


tell application "Microsoft Excel"
	activate
	tell active workbook
		set dossier to path & ":"
		set fileName to name
	end tell
end tell

tell application "System Events"
	name extension of disk item (dossier & fileName)
	set csvName to (text 1 thru -(1 + (length of result)) of fileName) & "csv"
	set csvPath to (dossier & csvName)
	if not (exists disk item csvPath) then
		make new file at end of folder dossier with properties {name:csvName}
	end if
end tell

tell application "Microsoft Excel"
	(*
	tell active workbook
		save workbook as filename csvPath file format CSV file format
	end tell
	*)
	save active sheet in csvPath as CSV file format
	close workbook 1 saving no
end tell

Alas mine and yours export a file which is perfectly treated when I open them with Numbers but, if I open them in Excel, I get odd results which you may see at :
https://www.box.com/s/tjsdv0gx38p8qlqckkra

Yvan KOENIG (VALLAURIS, France) samedi 8 septembre 2012 14:15:19