:: Area: #0 (bb4w)
Message:  24543 (Read 1 time, has 0 replies, 4725 bytes)
Date   : Fri Apr 13 17:08:46 2007 +0100
From   : jgh@arcade.demon.co.uk
Subject: Re: Import data from Microsoft Excel
Reply-To: bb4w@yahoogroups.com
Message-ID: <070413175504@arcade.demon.co.uk>

>Message-ID: <070411235502@arcade.demon.co.uk>

jgh@arcade.demon.co.uk wrote:
>       A%=INSTR(A$+",",",")
>       field$(n%)=LEFT$(A$,A%-1)
>       A$=MID$(A$,A%+1)

Of course, that needs: n%=n%+1

Also, that code doesn't work with quoted strings that include
commas, eg: Jones,"Edward, Sir",watercolours

To deal with quoted CSV fields, change to:

IF LEFT$(A$,1)="""" THEN
  A%=INSTR(A$+",",""",")+1          :REM Look for ", seperator
  field$(n%)=MID$(A$,2,A%-3)        :REM Extract item
 ELSE
  A%=INSTR(A$+",",",")              :REM Look for , seperator
  field$(n%)=LEFT$(A$,A%-1)         :REM Extract item
ENDIF

When outputting CSV, fields with commas in them must be quoted:

num%=DIM field$()
FOR n%=0 TO num%
  IF INSTR(field$(n%),",") THEN
    BPUT#out%,""""+field$(n%)+"""";  :REM output quoted string
   ELSE
    BPUT#out%,field$(n%);            :REM output unquoted string
  ENDIF
  IFn%<>num% THEN BPUT#out%,",";     :REM output comma if not last item
NEXT n%
BPUT #out%,""                        :REM output end-of-line

Some implementations output CSV files with /all/ fields quoted, whether
they need to be or not. This is not noticeable as a quoted field with no
commas in it is read just as though it is not quoted.

Note also that this code does not deal with fields that have both commas
/and/ quotes within them, eg: >he said "hello, there"<.

--
J.G.Harston - jgh@arcade.demon.co.uk - mdfs.net/User/JGH
RISC OS Internationalisation - http://mdfs.net/Software/RISCOS