If your 'sample data' is really representative of the CSV file then this code should do the trick...
0001 line = 'Name1, "458, MyCity", MyCountry' 0002 EQU DQ TO CHAR(34) 0003 EQU COMMA TO CHAR(44) 0004 newarray = '' 0005 LOOP UNTIL LEN(line) = 0 0006 comma1.pos = INDEX(line,DQ,1) 0007 IF comma1.pos = 1 THEN 0008 comma2.pos = INDEX(line,DQ,2) 0009 newarray<-1> = TRIM(line[comma1.pos+1,comma2.pos-2]) 0010 line = TRIM(line[comma2.pos+2,-1]) 0011 END ELSE 0012 newarray<-1> = line[COMMA,1,1] 0013 line = TRIM(line[COMMA,2,999]) 0014 END 0015 REPEAT 0016 CRT newarray If the actual CSV file does not have spaces between the fields then you can remove the TRIM()'s. Dan On Fri, May 28, 2010 at 11:44 AM, master <[email protected]> wrote: > @D. Klein: > > I could use lots of delimiters, my fav being pipe. But in this case I > dont control the format the file comes in or the delimiter to be used. > I DONT read it directly from Excel; the data is converted to the flat > file format I have described before it comes to jbase for loading. > > Different applications have a way of interpreting the enclosed > delimiters - for instance in Oracle I just have to declare the > "enclosed by" character when defining an external table. > > That said, here is a portion of the code minus the inserts. > > DIRPATH='\path\to\dir' ;* Path to extracts directory > FILENAME ='cust.file' ;* Flat File name > OPENSEQ DIRPATH,FILENAME TO MYINPUTFILE ELSE > CRT 'CANT open file' > END > > LOOP > READSEQ D.LINE FROM MYINPUTFILE ELSE > BREAK > END > CONVERT ',' TO @FM IN D.LINE ;* Here is my field delimiter > > **Then I go on to process the data > > while D.LINE > > FIELD.COUNT = DCOUNT(D.LINE,@FM) ;* This value should be the same > for > all records > ;* However, if there is an > enclosed comma, the number of fields will be more > > RECORD.ID = d.li...@fm,1,1] ;* Get ID from first field in the > flat file > > R.RECORD<0>= RECORD.ID > > R.CUST<FIELD1> =d.li...@fm,1,1] ;* first field, etc > R.CUST<FIELD2>=d.li...@fm,2,1] > R.CUST<FIELD3>=d.li...@fm,3,1] > > WRITE R.CUST ON FN.CUST,RECORD.ID ON ERROR ;*Write to jbase > file > *DO STUF > END > REPEAT > > Sample Data: > > The sample data I sent should be enough > > Name: Name1 > Address: 458, MyCity > Country: MyCountry > > In Excel it will appear like this: > > Name Address Country > Name1 458, MyCity MyCountry > > When converted to CSV it will be: > > Name1, "458, MyCity", MyCountry > > The routine should read 3 fields: > Field 1: "Name 1" > Field 2: "458, MyCity" - including the comma > Field 3: MyCountry. > > Right now this is what is happening: > > Field 1: "Name 1" > Field 2: "458 > Field 3: MyCity" > Field 4: MyCountry. > > > On May 28, 4:40 pm, Daniel Klein <[email protected]> wrote: > > READSEQ cannot be doing this. > > > > How is the CSV file being generated. A better method would be to use a > > tab-delimited file. > > > > Post your code, a 'jdiag.out' file (as requested in the Posting > Guidelines. > > For Zarquon's sake, doesn't ANYONE read it?) and an actual sample of the > > file so that a correct solution can be provided. > > > > Dan > > > > On Thu, May 27, 2010 at 7:16 PM, master <[email protected]> wrote: > > > How do i read a record whose delimiter is part of the field values? > > > > > Many programs (Excel, etc) will enclose a field in which a delimiter > > > is part of the text with double quotes: > > > Sample This: > > > > > Name: Name1 > > > Address: 458, MyCity > > > Country: MyCountry > > > > > In Excel it will appear like this: > > > > > Name Address Country > > > Name1 458, MyCity MyCountry > > > > > When converted to CSV it will be: > > > > > Name, "458, MyCity", MyCountry > > > > > Now, using READSEQ, the routine breaks the Address field at the first > > > comma , (i.e after 458) instead of treating everything enclosed in "" > > > as a single entity. > > > > > -- > > > Please read the posting guidelines at: > > >http://groups.google.com/group/jBASE/web/Posting%20Guidelines > > > > > IMPORTANT: Type T24: at the start of the subject line for questions > > > specific to Globus/T24 > > > > > To post, send email to [email protected] > > > To unsubscribe, send email to [email protected] > > > For more options, visit this group at > > >http://groups.google.com/group/jBASE?hl=en > > > > > > -- > Please read the posting guidelines at: > http://groups.google.com/group/jBASE/web/Posting%20Guidelines > > IMPORTANT: Type T24: at the start of the subject line for questions > specific to Globus/T24 > > To post, send email to [email protected] > To unsubscribe, send email to [email protected] > For more options, visit this group at > http://groups.google.com/group/jBASE?hl=en > -- Please read the posting guidelines at: http://groups.google.com/group/jBASE/web/Posting%20Guidelines IMPORTANT: Type T24: at the start of the subject line for questions specific to Globus/T24 To post, send email to [email protected] To unsubscribe, send email to [email protected] For more options, visit this group at http://groups.google.com/group/jBASE?hl=en
