Need to process all files in folder with Applescript and Excel
Need to process all files in folder with Applescript and Excel
- Subject: Need to process all files in folder with Applescript and Excel
- From: R M <email@hidden>
- Date: Thu, 21 Sep 2006 12:41:08 -0400
- Thread-topic: Need to process all files in folder with Applescript and Excel
Title: Need to process all files in folder with Applescript and Excel
I have 1800+ CSV individual contact files that I must convert to HTML files in Excel.
By using Excel's conversion engine (surprisingly) I can get the superior consistency and error-tolerance I need to automatically process what are poorly-formed and inconsistent CSV files into well-formed HTML output. With it, I can then run each file through a really nifty program called Anthracite by Metafy in order to export the tables into arrays. These arrays will then import into Filemaker or some other database for final normalization. A better technical person than I could find a better way to do this, but I have proven the basic interfaces and know this will work. There may be other ways to do this, but I am focused on doing it this way, and have invested huge hours to get it 95% done. It's the final 5% that's killing me.
But I'm an analyst, not a programmer, and there’s a snag. I have cobbled together a little newbie Applescript that will automate the process of opening the CSV file in Excel 2004, then converting and saving it as an HTML file. It works beautifully, as simple as it is.
However, the AppleScript as written will only convert one file at a time. I need a way to have this script loop through the specified directory, choose each successive file, convert it, save it, and then go back to the next file in line in the same folder. I'm sure it is a simple fix, but I can't find any reference or example to help me chunk through this directory one file at a time. I've been scouring for reference for this but just can't fnd example syntax.
My core processing script is below. I hope those knowledgeable folks out there will understand just getting to this point is harder than it looks for a non-scripter like me. And in fact, I got this far only by scouring news groups and picking up tips here and code left there by extremely talented and generous people whose prior work allowed me to finally write this script for Excel 2004 using OS X 10.4.7:
set the_file to "Users:mynameesktop:TESTfolder:Uniquecontactdata 1.csv"
tell application "Microsoft Excel"
activate
open the_file
save active workbook in the_file & "_new.html" as HTML
close active workbook without saving
end tell
I need to revise the AppleScript in order to have it run through, alter, re-name and save-as all the files to HTML.
It may be important to note that I have 1800+files, sequentially numbered "1.csv" through "2200.csv". Because there are numbers missing from this sequence, my gut tells me I also need some type of "auto skip if file not present" function, auto error-handling, or a looping strategy that’s flexible enough to work with these variables.
Can anyone help me in this quest? Any advice of any sort would gratefully appreciated.
Rob @ DecisionInformatics
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Applescript-users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden