! ! Copyright 1998, Ray Ontko and Co, Richmond, IN, US. ! ! This file may only be used and distributed under the ! terms of the GNU General Public License. See COPYING.TXT ! included with this file for specific terms of the license. ! !++ ! ! Name ! ! csv.sqh ! ! Description ! ! A collection of routines that are very useful for reading and ! writing .csv files (i.e., comma-separated, variable length files). ! .csv files are often used for importing into spreadsheet programs. ! In csv files, text strings are enclosed in double-quotes, and to ! include a double-quote, it is stuttered (i.e., repeated). Numeric ! values are bare (i.e., no quotes). ! ! Does not yet make any special provision for date values. These ! can usually be quoted. ! !-- ! begin-procedure csv_get_field( :$line , :$field ) !++ ! ! csv_get_field( :$line , :$field ) ! ! Gets the first comma-separated field from $line and moves it ! to $field. Upon completion the field and comma separator are ! removed from $line. ! ! If the value is in quotes, the quotations are removed. ! If a value contains two double-quotes ("), these are reduced to ! a single double-quote. ! ! For example, ! ! let $line = '"You ""send"" me.",5' ! do csv_get_field( $line , $field ) ! ! results in $field having the value 'You "send" me.' ! and $line having the value '5'. ! !-- if substr($line,1,1) = '"' move '' to $field let #pos = 2 while 1 let #next = instr($line,'"',#pos) if #next > 0 if substr($line,#next+1,1) = '"' let $field = $field || substr($line,#pos,#next - #pos + 1) let $line = substr($line,#next+2,length($line)-#next - 1 ) move 1 to #pos else let $field = $field || substr($line,#pos,#next - #pos) let $line = substr($line,#next+1,length($line)-#next ) break end-if else ! the " wasn't terminated, so we'll assume that they want the rest let $field = substr($line,#pos,length($line)-#pos+1) move '' to $line break end-if end-while if substr($line,1,1) = ',' let $line = substr($line,2,length($line)-1) end-if else let #pos = instr($line,',',1) if #pos > 0 let $field = substr($line,1,#pos - 1) let $line = substr($line,#pos+1,length($line)-#pos) else move $line to $field move '' to $line end-if end-if end-procedure ! csv_get_field begin-procedure csv_set_string( $s , :$t ) !++ ! ! csv_set_string( $s , $:t ) ! ! sets $t to the quoted value of $s. If $s contains ! double-quotes, each is replaced with two double-quotes. ! This routine is used to put a string as the the first ! field of a line to be written to a csv file. ! ! For example, ! ! do csv_set_string( $name , $line ) ! do csv_add_string( $city , $line ) ! write {OUT_FILE} from $line ! !-- if isnull( $s ) move '' to $t else do csv__replace( $s , $tmp ) let $t = '"' || $tmp || '"' end-if end-procedure begin-procedure csv_add_string( $s , :$t ) !++ ! ! csv_add_string( $s , $:t ) ! ! appends a comma to $t, followed by the value of $s in double-quotes. ! If $s contains double-quotes, these are treated as two double-quotes. ! This procedure is used to add a string field to a line that will ! be subsequently written to a csv file. ! ! For example, ! ! do csv_set_string( $last_name , $line ) ! do csv_add_string( $first_name , $line ) ! write {OUT_FILE} from $line ! !-- if isnull( $s ) let $t = $t || ',' else do csv__replace( $s , $tmp ) let $t = $t || ',"' || $tmp || '"' end-if end-procedure begin-procedure csv_set_number( #n , $e , :$t ) !++ ! ! csv_set_number( #n , $e , $:t ) ! ! sets the a string variable $t to an edited numeric value #n using ! the edit string $e. This is used to set the first field of a line ! to a numeric value using an edit mask, where the intent is to ! subsequently write the line to a csv file. ! ! For example, ! ! do csv_set_number( #emp_id , '888888888' , $line ) ! do csv_add_string( $emp_name , $line ) ! write {OUT_FILE} from $line ! !-- let $t = edit( #n , $e ) end-procedure begin-procedure csv_add_number( #n , $e , :$t ) !++ ! ! csv_add_number( #n , $e , $:t ) ! ! appends a numeric field value #n to a line $t using edit mask $e. ! This is used to append a numeric field value to a line using an ! edit mask, where the intent is to subsequently write the line to ! a csv file. ! ! For example, ! ! do csv_set_string( $name , $line ) ! do csv_add_number( #salary , '8888888.88' , $line ) ! write {OUT_FILE} from $line ! !-- let $t = $t || ',' || edit( #n , $e ) end-procedure begin-procedure csv__replace( $s , :$t ) ! ! this is an internal routine which replaces a double-quote with ! two double-quote characters. ! move $s to $rest move '' to $t while $rest <> '' let #pos = instr( $rest , '"' , 1 ) if #pos = 0 let $t = $t || $rest break end-if let $t = $t || substr( $rest , 1 , #pos - 1 ) || '""' let $rest = substr( $rest , #pos + 1 , length( $rest ) - #pos ) end-while end-procedure ! csv__replace