Excel and Delimited Text Files

Scripting Forums AppleScript | Mac OS X
,
#1

I am getting some strange behavior from Excel with a script to open and manipulate delimited text files. Here is the gist …

I have some data files that look like:

AAA|BBB|CCC|DDD|EEE|FFF
AAA|BBB|CCC|DDD|EEE|FFF
AAA|BBB|CCC|DDD|EEE|FFF

the fields are seperated by vertical bars ( ‘|’ ). All of the files are output by the same tool (just different queries).

I open them in Excel with the following Snip
tell application “Microsoft Excel”
activate
…
open text file filename theFullFile other char “|” with use other
…
end tell

Then I save it out to a workbook. The problem is that sometimes Excel seems to ignore the delimiters and split on whitespace or something else. I get no errors from Excel when the script is run.

I have opened the files that cause problems manually in Excel and it is able to split them on the ‘|’ delimiter.

Has anyone else out there done this kind of thing before? It seems like a pretty basic thing.

Thanks,
Matt

#2

I used to get EBCIDIC to ASCII data downloads from a mid-range IBM and there were sometimes non-printable control characters in the data that were not ASCII. These would mess up my macros and Applescripts. Could it be that there is a variation in the data file you are opening? This drove me crazy until I figured it out. After I was able to identify these, I’d filter the file to either remove or replace them.

MS has an extensive Excel Applescript reference here:

http://download.microsoft.com/download/3/A/7/3A70FB4B-0C6A-43E3-AAB7-AC9166B25632/Excel2004AppleScriptRef.pdf.

There is some additional info in that PDF beyond what the dictionary provides.

HTH
Craig