Thanks Dan, Exactly what I needed.
It worked perfectly On May 29, 2:09 am, Daniel Klein <[email protected]> wrote: > 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
