Excel Workbook Sheets to CSV

Hello folks,

I’ve been googling around all afternoon to see if what I would like to do is possible. I have a collection of Excel workbooks that have a random number of sheets within them. For example, file1.xls has 12 sheets, file2.xls has 34 sheets and file3.xls has 4 sheets. The number of sheets varies all the time.

I need to take each sheet in turn and export that to CSV with UTF8 encoding too (just remembered that!). I can’t see a way of doing this with Excel, does anyone know if it can be achieved?

Model: MacBook Pro
Browser: Firefox 4.0.1
Operating System: Mac OS X (10.6)

Moved to the appropriate forum

Ahh, thank you Martin

-- Paths to Excel file and output directory
set excelDirectory to "Macintosh HD:Users:scott:Downloads:Excel:"
set outputDirectory to "Macintosh HD:Users:scott:Downloads:Excel:converted"

tell application "Microsoft Excel"
	
	activate
	
	set theWorkbook to open workbook workbook file name excelDirectory & "excel.xls"
	
	set maxCount to count of worksheets of theWorkbook
	
	repeat with i from 1 to maxCount
		
		set theWorkbook to open workbook workbook file name excelDirectory & "excel.xls"
		
		set theWorksheet to worksheet i of theWorkbook
		
		set worksheetName to name of theWorksheet
		
		activate object worksheet worksheetName
		
		save as theWorksheet filename (outputDirectory & ":" & worksheetName & ".csv") file format CSV file format
		
		close active workbook
		
	end repeat
	
end tell

Is what I’ve got so far. I’d also like to export as UTF8 and automatically overwrite files to prevent the save changes dialog popping up.

try this…

set f to choose file
set outputDirectory to (path to downloads folder as text) & "Excel:converted:"

-- make sure the outputDirectory exists and if not create it
if outputDirectory ends with ":" then set outputDirectory to text 1 thru -2 of outputDirectory
do shell script "mkdir -p " & quoted form of POSIX path of outputDirectory

-- save all worksheets as csv files
set csvPaths to {}
tell application "Microsoft Excel"
	open f
	set theSheets to worksheets of active workbook
	set workbookName to name of active workbook
	if workbookName ends with ".xls" then set workbookName to text 1 thru -5 of workbookName
	repeat with aSheet in theSheets
		set thisPath to outputDirectory & ":" & workbookName & "." & name of aSheet & ".csv"
		set end of csvPaths to thisPath
		save aSheet in thisPath as CSV file format
	end repeat
	close active workbook without saving
end tell

-- convert csv files to UTF8 format
repeat with csvPath in csvPaths
	try
		set utf8Path to text 1 thru -4 of csvPath & "utf8.csv"
		set csvText to read file csvPath
		writeTo_UTF8(utf8Path, csvText, true)
	end try
	tell application "Finder" to delete file csvPath
end repeat



(*============= SUBROUTINES ================*)
on writeTo_UTF8(targetFile, theText, appendText)
	try
		set targetFile to targetFile as text
		set openFile to open for access file targetFile with write permission
		if appendText is false then
			set eof of openFile to 0
			write «data rdatEFBBBF» to openFile starting at eof -- UTF-8 BOM
		else
			tell application "Finder" to set fileExists to exists file targetFile
			if fileExists is false then
				set eof of openFile to 0
				write «data rdatEFBBBF» to openFile starting at eof -- UTF-8 BOM
			end if
		end if
		write theText as «class utf8» to openFile starting at eof
		close access openFile
		return true
	on error theError
		try
			close access file targetFile
		end try
		return theError
	end try
end writeTo_UTF8