From: ahoward <ahoward@...>
Date: 2003-02-21T23:01:57+09:00
Subject: Re: Regexp help: Parsing a CSV file

On Fri, 21 Feb 2003, Tim Bates wrote:

> I've dumped a CSV (comma separated values) file from Excel, and I want to
> parse it into cels within my Ruby script. Easy enough, you say:
>   line.split(',')
> But it's a little more complicated than that - see, if the cel has a comma in
> it, it gets surrounded in quotes. If a cel has a quote in it, it's surrounded
> by quotes and doubled, eg:
>   Test	Te,st		Te"st
> becomes
>   Test,"Te,st","Te""st"
> Now to use String#split, I would have to write a regexp that will match a
> comma, provided that comma is preceeded by an even number of quotes. BUT, I
> don't want the regexp to match the quotes themselves, just the comma. I can't
> figure this one out...

i would not use regexps to parse this... they can be very slow,not to mention
difficult.  how about something like :

#!/usr/bin/env ruby

# input data and expected output
line     = %q(Test,"Te,st","Te""st","""Test","Test""",",Test","Test,")
expected = %w(Test Te,st Te"st "Test Test" ,Test Test,)

# states
INITIAL = 0
QUOTED  = 1

# stacks
cells  = [ %q()    ]
states = [ INITIAL ]

# handy byte values
q     = %q(')[0]
qq    = %q(")[0]
comma = %q(,)[0]


# process string
top   = nil
state = nil
idx   = 0

while (b = line[idx])

  idx += 1

  top   = cells.last
  state = states.last

  if state == QUOTED
    if b == qq
      lookahead = line[idx]
      if lookahead == qq
	top << (b)
	idx += 1
	next
      else
	states.pop and state = states.last and next
      end
    end

    top << (b) and next
  end

  # else state is NOT QUOTED!

  states.push (QUOTED) and next if (b == qq)

  cells.push (%q()) and next if (b == comma)

  top << (b) and next
end


raise 'Parse error!' unless
  cells == expected and
  state == INITIAL

p cells
# >> ["Test", "Te,st", "Te\"st", "\"Test", "Test\"", ",Test", "Test,"]


-a

-- 
 ====================================
 | Ara Howard
 | NOAA Forecast Systems Laboratory
 | Information and Technology Services
 | Data Systems Group
 | R/FST 325 Broadway
 | Boulder, CO 80305-3328
 | Email: ahoward@fsl.noaa.gov
 | Phone:  303-497-7238
 | Fax:    303-497-7259
 ====================================