CsvToListOfText
CsvToListOfText
- Subject: CsvToListOfText
- From: T&B <email@hidden>
- Date: Wed, 13 Dec 2006 18:02:18 +1100
Hi Kai and all,
I found a situation with the CsvToList solution that you graciously
posted, which my previous bug fix didn't catch. If a double quote
immediately precedes or follows a new line, it still outputs as a
single quote (but should be an empty string).
For example, with
quote = '
comma = ,
this:
item1,item2,NULL,item4
item5,item6,NULL,'item''7'''
item9,item10,''
item11,'',item13
'','item15',item16,'',item18
Should parse as:
{{"item1", "item2", "NULL", "item4"},
{"item5", "item6", "NULL", "item'7'"},
{"item9", "item10", ""},
{"item11", "", "item13"},
{"", "item15", "item16", "", "item18"}}
But instead parses as:
{{"item1", "item2", "NULL", "item4"},
{"item5", "item6", "NULL", "item'7'"},
{"item9", "item10", "'"},
{"item11", "", "item13"},
{"'", "item15", "item16", "", "item18"}}
Note that items 11 and 14 are incorrect.
That's the bad news.
The good news is that I think I have a solution, which actually
avoids the previous bug fix method of using another repeat loop. It
instead uses yet another text delimiter substitution, and is slightly
faster (1.21s down from 1.29s, on my test file)
I'm using the function name CsvToListOfText to distinguish this from
the CsvToListClassed routine, which is working fine (but is slower).
I've also moved several variables into the arguments, for flexibility
similar to my CsvToListClassed routine.
Any bug reports or improvements welcome,
Tom
-- example:
set csvText to "item1,item2,NULL,item4
item5,item6,NULL,'item''7'''
item9,item10,''
item11,'',item13
'','item15',item16,'',item18"
set separatorString to ","
set quoteString to "'"
set newLineString to linefeed
CsvToListOfText(csvText, separatorString, quoteString, newLineString)
(* gives:
{{"item1", "item2", "NULL", "item4"},
{"item5", "item6", "NULL", "item'7'"},
{"item9", "item10", ""},
{"item11", "", "item13"},
{"", "item15", "item16", "", "item18"}}
*)
property quoteSubstitute : ASCII character 1
property newLineSubstitute : ASCII character 2
property delimiterSubstitute : ASCII character 3
property linefeed : ASCII character 10
on CsvToListOfText(t, delimiterString, quoteString, newLineString)
set oldDelimiters to text item delimiters
set text item delimiters to quoteString & quoteString
set t to t's text items
set text item delimiters to quoteSubstitute
set t to t as string
set text item delimiters to quoteString
script o
property l : t's text items
end script
repeat with i from 1 to count o's l by 2
-- new substitution to allow for "" meaning empty string
set text item delimiters to quoteSubstitute
set t to text items of o's l's item i
set text item delimiters to ""
set t to t as string
set text item delimiters to delimiterString
set t to text items of t
set text item delimiters to delimiterSubstitute
set t to t as string
set text item delimiters to newLineString
set t to t's text items
set text item delimiters to newLineSubstitute
set o's l's item i to t 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 quoteString
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 delimiterSubstitute
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 CsvToListOfText
_______________________________________________
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