Sample code for reading a CSV file

[Nonsanity]

Here's a generalized case that returns an array, with or without the quotes,
and that allows for commas and returns inside the quotes to remain part of
that element. It also preserves backslash-escaped quotes in any element,
quoted or not.


function CSVtoArray csv, removeQuotes, preserveEscapedQuotes
   if preserveEscapedQuotes is empty then put true into
preserveEscapedQuotes

   set itemdel to quote
   if preserveEscapedQuotes then replace ("\"&quote) with numtochar(3) in
csv
   repeat with b = 1 to the number of items in csv
      if trunc(b/2) = (b/2) then
         replace return with numtochar(1) in item b of csv
         replace comma with numtochar(2) in item b of csv
      end if
   end repeat

   set itemdel to comma
   repeat with a = 1 to the number of lines in csv
      put line a of csv into theline
      repeat with b = 1 to the number of items in theline
         get item b of theline
         replace numtochar(1) with return in it
         replace numtochar(2) with comma in it
         if removeQuotes then replace quote with "" in it
         if preserveEscapedQuotes then replace numtochar(3) with quote in it
         put it into o[a][b]
      end repeat
   end repeat
   return o
end CSVtoArray


The 2nd and 3rd parameters are optional, with expected settings as default.
(Leave quotes on the strings, and DO preserve escaped quotes.) It doesn't
allow StartOfHeading, StartOfText, or EndOfText characters to be in the
source (ASCII 1, 2, and 3 respectively) but then those are non-printable
characters and not likely to be there to begin with. It should be fairly
speedy, though I haven't tested it with a huge input.

 ~ Chris Innanen
 ~ Nonsanity



On Thu, Feb 17, 2011 at 1:16 PM, Paul Dupuis <paul at researchware.com> wrote:

> So I am tired of reinventing the proverbial wheel over and over again.
>
> I have a new project that I want to read a CSV file for. Obviously reading
> the file is easy. And is it was tab separated instead of comma, parsing out
> the rows and columns is easy as well. However, with comma seperated data
> where some columns contain string with commas in then that are encapsulated
> in quotes, just plowing through the itemDelimiter and lineDelimter doesn't
> work.
>
> So before I bother to write code to handle encapsulated CSV data, I thought
> I'd ask if anyone on the use-list has existing code to handle CSV's that
> they'd be willing to share.
>
> --
> Paul Dupuis
> Cofounder
> Researchware, Inc.
> http://www.researchware.com/
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>