Hi,
I have a database that exports records to CSV format which I want to read into an Applescript.
When I export the CSV and then try to read it Applescript it reads all the records with backslashes preceeding each record.
If I save it as a CSV in Excel it becomes readable.
I’m sure I’m doing something stupid.
Can someone please help?
Thanks in anticipation
Martin.
Why do the two screenshots show different output though the code seems to be identical? And what do you want to do with the CSV?
The first screenshot shows the CSV as exported by Filemaker and then opening in Applescript.
The second screenshot shows the CSV if I open it in Excel and resave it as a CSV.
The second CSV is readable in the InDesign Applescripts. I’m wanting to process the output of the database in InDesign using an Applescript.
Thanks for your attention
Try to perform all read/write operations in your CSV file as «class utf-8».
Also, the text encoding (I recommend UTF-8) must not be changed by any of the 3 applications you are using. Otherwise, there will be confusion.
Hi Robert,
Thanks for your suggestion. I’ve tried all the options in the Filemaker export options, with and without aplying the data layout formatting. They all produce the same result.
What is interesting is that I can export the database as a Merge format document and change the extension to CSV from MER. When that is opened as imported into an Applescript is opens the column headings in a readable format without the forward slashes but as soon as it gets to records data it applies the quotes and backslashes.
Thanks
Martin
Personally, I’d use some text processing in the shell or so to fix CSV and then have it read by your AS code. awk, sed, something like that or a combination thereof should do the trick. Actually, posting the source of the CSV would be helpful (preferably not as a screenshot).
I dont have FileMaker Pro and can’t view a CSV file from that app. Can you post an example? FWIW, the following is how I would make a CSV string into a list.
set csvString to "a,b,c,d,e"
set ATID to AppleScript's text item delimiters
set AppleScript's text item delimiters to {","}
set csvList to text items of csvString
set AppleScript's text item delimiters to ATID
return csvList --> {"a", "b", "c", "d", "e"}
Thanks for your help, it’s appreciated. Here’s the file directly from Filemaker. I had to zip it to make sendable.
myFile.csv.zip (692 Bytes)
Regards
Martin
Thanks Peavine,
I adapted and tried your applescript and it returned the same.
Kindest regards
Martin
Try the following, which returns a list of strings (which I assume is what you want). Ignoring the first line, the script takes less than a millisecond to run on my 2023 Ventura Mac mini.
set csvString to (read (choose file with prompt "Please choose the CSV-File"))
set ATID to AppleScript's text item delimiters
set AppleScript's text item delimiters to {"\""}
set csvString to (text items of csvString)
set AppleScript's text item delimiters to {""}
set csvString to csvString as text
set AppleScript's text item delimiters to {","}
set csvList to text items of csvString
set AppleScript's text item delimiters to ATID
return csvList --> {"inv:29888812816522", "SN", "BRIS02", "1", "Mrs. M. DOLMAN", " Happy birthdaymyComma have a nice daymyNewLinFrom ALAN and family xxxmyNewLin", "14", "BRIS02-16522-MRS. "}
Peavine you’re a star!
It worked perfectly.
Thanks. I definitely owe you a wine or a beer or two… or several! If you’re ever in Wales you’d be most welcome to collect!
Kindest regards
Martin
Martin. I’m glad that helped and thank you for the kind email.
The script I posted earlier does the job, and there’s no reason for Martin to use anything else. However, I thought I would post an ASObjC solution just in case someone needs one in the future.
use framework "Foundation"
use scripting additions
set csvFile to POSIX path of (choose file)
set csvString to current application's NSString's stringWithContentsOfFile:csvFile encoding:(current application's NSUTF8StringEncoding) |error|:(missing value)
set csvString to csvString's stringByReplacingOccurrencesOfString:"\"" withString:""
set csvList to (csvString's componentsSeparatedByString:",") as list