Changing Excel File Formats!

Hello all, i have just joined this forum and wanted some advice on the eggplant scripts. i know we have many experts in the house here, just needed some help in the scripting. I wanted to know if there is any script we could use to change the .xls format files to .txt since i have a large number of scripts more than 700, can anyone advice or share some kind of script.

thanks to all for reading :smiley:

A good starting point would be the following post on reading data from Excel files: http://www.testplant.com/phpBB2/viewtopic.php?t=151

well, thanks for the quick reply, but the thing is i dont want to convert to eggplant files. i need to convert the extension so that i can upload it into another application and was wondering if eggplant could help me here but just changing the extension of the files so that i can upload to the other application.

thanks

I assume you don’t want to just change the file extension, but want to actually change the format of the data in the file. Based on the script in the topic that Matt provided a link to earlier, here’s a script I just wrote and tested that extracts the data from an Excel spreadsheet and writes it in tab-delimited format to a text file:

do AppleScript {{ 
tell application "Microsoft Excel" 
open "Macintosh HD:Users:doug:SomeExcelFile.xls" 

set theRange to value of the usedRange of worksheet 1 
set newData to {} 
repeat with aRow from 1 to count of theRange 
? set newRow to {} 
? repeat with aCell from 1 to count of item 1 of theRange 
? ? set newRow to newRow & text of cell aCell of row aRow of worksheet 1 
? end repeat 
? copy newRow to the end of newData 
end repeat 
get newData 
end tell 
}} 
put the result into fullData 

put empty into text
repeat with each row in fullData
	put (row joined by tab) & return after text
end repeat
put text into file "~/myFile.txt"
open "~/myFile.txt" with "TextEdit"

As you can see, it’s pretty straightforward. The original code extracts the data from Excel into a list of rows, where each row is a list of the cells in that row. The repeat loop then goes through the outer list of rows, and uses the “joined by” operator to combine the cell values in each row into a single line of text with tabs between the values. Is that what you were looking for?

hey doug, thanks for the reply and advice, i shall try it once and see if that works for me or not

thanks
keith

If you want to learn Eggplant, I’d start by doing exactly what you’d do manually, then repeat 700 times :). Assuming Mac here - if PC, swap command with control. You’ll also eliminate the command-g step and just type the full pathand type the fullrewrite. This will run slower than a shell command, but who cares - easily readable and reusable.

repeat with each line in fileList

open file - assume Excel is front and center

TypeCommand “o”
TypeText it, return # it is the full path file name
TypeCommand “o” # mac only, to open

wait for the file to open

TypeText commandkey, shiftkey, “s” # now save as

on most spreadsheets, it will remember previous “save as” type,

so you don’t have to reset to txt/csv format

TypeText return
TypeCommand “w” # close window
end repeat

Questions?

v