I’m supper new to applescript and really need your help. I’m a basic research lab manager who has been given the project combining ~200 csv files into one excel sheet sequentially. These files will eventually go into filemaker pro 11. The script I have been trying was originally from one of your members (Stefan Klieme). I’ve changed a few things but it’s not working as I would like it to. Each csv file range is identical (A1:Z312). The script works but the files copy over each other instead of coping below the previous pasted file. below is the script.
property firstRow : 1
property lastRow : 700
set sourceFiles to (choose file with prompt "Choose source files" of type {"XLS6", "XLS7", "XLS8", "XLS", "TXT"} with multiple selections allowed without invisibles)
set firstUnusedColumn to 1
tell application "Microsoft Excel"
activate
set targetBook to make new workbook
repeat with oneFile in sourceFiles
set sourceBook to open workbook workbook file name (oneFile as text)
set thisName to name of sourceBook
set copy_range to range ("a" & firstRow & ":z" & lastRow) of active sheet of sourceBook
if value of used range of copy_range is not "" then
tell sheet 1 of targetBook
set value of cell 1 of column firstUnusedColumn to thisName
set destinationRange to (get offset range "a2" column offset 1)
end tell
copy range copy_range destination destinationRange
set firstUnusedColumn to firstUnusedColumn + 1
end if
close sourceBook saving no
end repeat
end tell
any help would be much appreciated.
Thanks,
Bob
Model: power PC G5
AppleScript: 2.2.1
Browser: Firefox 3.6.3
Operating System: Mac OS X (10.5)
I’m going to go off on a different tack. I’m primarily a FileMaker person, then an AppleScript person. I use Excel occasionally. I think FileMaker is a much better tool to organize information.
FileMaker is one of the few applications which can run AppleScript directly, via its Perform AppleScript step. It can do all of the importing of the files. It can also Save As Excel (which is slightly different from an Export, in that it is "layout-based). That should be adequate if you really must have an Excel file of the results.
A Perform AppleScript can get the file list of a folder, and set them into a FileMaker field (global). You can then Loop thru those, coercing the AppleScript path to a FileMaker syntax path, putting it into a script Variable, and using that as the dynamic Import path.
This is an example file, with the basic elements and script needed, and a few example csv files. Obviously you’d need to adjust the Import Order for your csv files’ structure. It will be there for a little while.
The ultimate goal is to get these data into fmp 11 so this will save me a step.
I ran the script in filemaker but unfortunately the csv files were not selectable. When I canceled the operation I received an applescript error -128.
any ideas on what might be happening?
Again thanks for your help. I’m a fish out of water with this applescript stuff; I’m used to simple macros.
It is a “choose folder” AppleScript command. So it is normal that the csv files are grayed out; it only allows you to choose folders. Just hit OK when you’re in the folder with the csv’s. You can also click OK if you’re in the folder containing the folder, and the folder is selected. Choose folder is flexible that way (since otherwise you’d have to back up a level).
The -128 just meant that you cancelled the choose dialog.
While in your fmp folder I start the script from the tool bar. Selected your csv folder (3 csv files) and then clicked o k.
If I was in the global layout it would switch to the Signals layout. There are 4 fields there. But no data or error messages.
It is the Import script itself. You must retarget its 2nd path (line), to a file which is an example of your csv data. Then (and only then) open the Order, and line up the csv to your file.
The 1st line in the path, $csv_file_FM, is dynamically set by the script, for each of your files. Because it is the 1st path FileMaker tries, it will change for each.
But you also need the 2nd path, in order to set up the Import Order. FileMaker cannot set it up using only a dynamic path, as that only exists when the script runs.
I believe I’ve clicked the box for “1st row is field names”. If your csv does not have this, then turn if off (or it will not import your 1st row).