Re: Importing/parsing CSV files
Re: Importing/parsing CSV files
- Subject: Re: Importing/parsing CSV files
- From: T&B <email@hidden>
- Date: Tue, 24 Oct 2006 12:57:20 +1000
Hi Kai, and all,
Last month, you posted an excellent, fast CSVToList solution, using
AppleScript Text item delimiters, temporarily ASCII character
substitutions, and a list within a script object property. The
original post is appended below.
Unfortunately, it has a bug. When it replaces "" with the temporary
quoteSubstitute (q), which are later replaced with " (one quote), it
also distorts empty items. For instance the third item in this example:
item1,"quoted item","","that was an empty item","but processes
""this"" correctly"
which becomes a list:
{"item1","quoted item","\"","that was an empty item","but processes
\"this\" correctly"}
but should be this (third item changed):
{"item1","quoted item","","that was an empty item","but processes
\"this\" correctly"}
I fixed it by inserting a repeat loop, as per:
on CsvToList(t)
set quot to "\""
set comma to ","
set oldDelimiters to text item delimiters
set quoteSubstitute to ASCII character 0
set newLineSubstitute to ASCII character 1
set commaSubstitute to ASCII character 2
set text item delimiters to quot & quot
set t to t's text items
set text item delimiters to quoteSubstitute
set t to t as string
set text item delimiters to quot
script o
property l : t's text items
end script
repeat with i from 1 to count o's l by 2
set text item delimiters to comma
set t to text items of o's l's item i
-- Added this to fix empty quotes:
if t contains quoteSubstitute then
repeat with columnN from 1 to length of t
if item columnN in t is quoteSubstitute then
set item columnN in t to ""
end if
end repeat
end if
set text item delimiters to commaSubstitute
set t to t as string
set text item delimiters to newLineSubstitute
set o's l's item i to t's paragraphs as string
end repeat
set text item delimiters to ""
set t to o's l as string
set text item delimiters to quoteSubstitute
set o's l to t's text items
set text item delimiters to quot
set t to o's l as string
set text item delimiters to newLineSubstitute
set o's l to t's text items
set text item delimiters to commaSubstitute
repeat with i from 1 to count o's l
set o's l's item i to text items of o's l's item i
end repeat
set text item delimiters to oldDelimiters
return o's l
end CsvToList
I ran it on my large test CSV text file, which is 19 column x 2323
row CSV file, about 90% of values in quotes:
Original (with bug) took: 0.92s
The new version takes: 1.06s
which is still 20% to over 3000% better than alternative techniques.
Thanks,
Tom
----
Begin forwarded message:
From: kai <email@hidden>
Date: 13 September 2006 9:30:09 AM
To: applescript-users <email@hidden>
Subject: Re: Importing/parsing CSV files
...
I've had a brief look at one or two tid-based ways to do this.
The following handler assumes that the csv data is plain text
(formatted as output from Excel):
-----------------------
on |csv as list| from t
set d to text item delimiters
set q to ASCII character 0
set p to ASCII character 1
set c to ASCII character 2
set text item delimiters to "\"\""
set t to t's text items
set text item delimiters to q
set t to t as string
set text item delimiters to "\""
script o
property l : t's text items
end script
repeat with i from 1 to count o's l by 2
set text item delimiters to ","
set t to text items of o's l's item i
set text item delimiters to c
set t to t as string
set text item delimiters to p
set o's l's item i to t's paragraphs as string
end repeat
set text item delimiters to ""
set t to o's l as string
set text item delimiters to q
set o's l to t's text items
set text item delimiters to "\""
set t to o's l as string
set text item delimiters to p
set o's l to t's text items
set text item delimiters to c
repeat with i from 1 to count o's l
set o's l's item i to text items of o's l's item i
end repeat
set text item delimiters to d
o's l
end |csv as list|
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/mailman//archives/applescript-users
This email sent to email@hidden