Re: Looking for CSV Import routine -
01-09-2007
, 12:19 AM
Scott, I've made some changes to the base you provided.
1) Three issues with valid data corrected. Test cases below.
2) upper-cased code for cross-platform
================================
TEST.ASB.CSV
* verify original behavior, break lines for readability
CSV = \"123,456.78",abcd,123.45,\
CSV := \"this, is the ""real"" thing","abc",123""\
RESULT = ""
CALL ASB.CSV1(CSV,RESULT)
CRT RESULT
RESULT = ""
CALL ASB.CSV2(CSV,RESULT)
CRT RESULT
CRT
* show fix for issue 1
CSV = \"test for ""this"", and for ""that""",123,\
CSV := \"""foo"" and ""bar""",456\
RESULT = ""
CALL ASB.CSV1(CSV,RESULT)
CRT RESULT
* show results of enhanced version
RESULT = ""
CALL ASB.CSV2(CSV,RESULT)
CRT RESULT
CRT
* show fix for issue 2
CSV = \another test "this" and "that",123,\
CSV := \"foo" and "bar",456\
RESULT = ""
CALL ASB.CSV1(CSV,RESULT)
CRT RESULT
* show results of enhanced version
RESULT = ""
CALL ASB.CSV2(CSV,RESULT)
CRT RESULT
CRT
* show fix for issue 3
CSV = \another "this" and "that" test,123,\
CSV := \"foo" and bar,456\
RESULT = ""
CALL ASB.CSV1(CSV,RESULT)
CRT RESULT
* show results of enhanced version
RESULT = ""
CALL ASB.CSV2(CSV,RESULT)
CRT RESULT
================================
Output follows. First line is original, second line is new version.
First example just shows your primary example still works.
123,456.78^abcd^123.45^this, is the "real" thing^abc^123"
123,456.78^abcd^123.45^this, is the "real" thing^abc^123"
test for "this"^and for "that""^123^"foo" and "bar"^456
test for "this", and for "that"^123^"foo" and "bar"^456
another test "this" and "that"^123^foo" and "bar^456
another test "this" and "that"^123^"foo" and "bar"^456
another "this" and "that" test^123^foo" and bar,45
another "this" and "that" test^123^"foo" and bar^456
====================
SUBROUTINE CSV.PARSER(STRING0,REC)
* 10-19-06 asb
* 01-08-07 tg tweaks
* parse comma separated value record watching out for quotes ("")
* attribute delimited rec is returned
$OPTIONS EXT
STRING = STRING0
REC = ""
AN = 1
* Form basic assumption as to whether line is quoted or not
IF INDEX(STRING0,'""',1) THEN QUOTED = 1 ELSE QUOTED = 0
LOOP
IF STRING[1,1] NE '"' OR NOT(QUOTED) THEN
RESULT = FIELD(STRING,",",1)
STRING = STRING[COL2()+1,999999]
END ELSE
READY = 0
FOR COMMAS = 1 TO 99 UNTIL READY
POS = INDEX(STRING,'",',COMMAS)
IF POS EQ 0 THEN POS = LEN(STRING)
RESULT = STRING[2,POS-2]
IF MOD(COUNT(RESULT,\"\),2) = 0 THEN READY = 1
NEXT COMMAS
STRING = STRING[POS+2,999999]
END
RESULT = CHANGE(RESULT,'""','"')
REC<AN> = TRIM(RESULT)
UNTIL STRING EQ "" DO
AN +=1
REPEAT
RETURN
====================
It's still not perfect but seems OK with well-behaved data.
HTH
T |