This subroutine converts a single CSV string into a field-delimited dynamic array.
2009-12-24: Was converting escaped quote marks (i.e. two in a row) when not contained within a quoted field; now fixed.
SUBROUTINE [[CSV2FIELDS]](TEXT, RECORD) ****** * [[CSV2FIELDS]] $Revision: 1.2 $ * CONVERT COMMA SEPARATED VALUES INTO A PICK RECORD * Copyright (C) 2007 Rex Gozar * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * * This library is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA * http://www.gnu.org/licenses/lgpl.html * * Rex Gozar * firstname.lastname@example.org ****** EQU COMMA TO ',' EQU DQ TO '"' BUFFER = TEXT BUFPTR = 0 CPTR = 0 QUOTESW = @FALSE LOOP CPTR += 1 C = BUFFER[CPTR,1] WHILE (C NE "") DO IF (DQ EQ C) THEN IF (QUOTESW) AND (DQ:DQ EQ BUFFER[CPTR,2]) THEN CPTR += 1 END ELSE QUOTESW = NOT(QUOTESW) CONTINUE END END IF (COMMA EQ C) AND NOT(QUOTESW) THEN C = @FM END BUFPTR += 1 BUFFER[BUFPTR,1] = C REPEAT RECORD = BUFFER[1,BUFPTR] RETURN END
Web page devoted to CSV processing: CSV
Tags: excel google docs openoffice calc csv comma-delimited tab-delimited spreadsheet worksheet