dbTalk Databases Forums  

Looking for CSV Import routine

comp.databases.pick comp.databases.pick


Discuss Looking for CSV Import routine in the comp.databases.pick forum.



Reply
 
Thread Tools Display Modes
  #11  
Old   
Tony Gravagno
 
Posts: n/a

Default 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

Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.