Re: A "Load from" question.

On Mon, 28 Sep 1998, Suhas Tembe wrote: > I have a temp table "temp_table" with say 3 columns as ; > > col1 char(2); > col2 char(2); > col3 char(2); > > I also have a delimited (|) flat file with 3 columns & I have to load > data from the flat file into this "temp_table". > The flat file looks like this : > > ab||cd --- line 1 > |ab|cd --- line 2 > ab|cd|| --- line 3 LOAD formats are more flexible than they used to be. Once upon not so very long ago, the first two lines would have cause load errors with no delimiter at the end of the third field. > I load data into the "temp_table" as follows : > > load from "flat_file.txt" > insert into temp_table;> > Now, when I do this, row # 1 in the table would like : > > col1 = ab > col2 = null > col3 = cd > >[...] > > As you can see, a "null" is inserted into the column(s) if there is no > data. (Note : There is no space between the pipes in the flat file). My > question is, what would I have to do if I wanted "spaces" & not "nulls" > to be inserted into the table. [...] Put spaces in the load file instead of leaving the fields empty: sed -e 's/^|/ |' \\ -e 's/||/| |/g' \\ -e 's/||/| |/g' \\ -e 's/|$/| /' flat_file.txt > newfile mv newfile flat_file.txt The only surprise in there is the repeated substitution, which *is* necessary in general. If you have the sequence 'a|||b' in a longer file, then the first substitution produces 'a| ||b' and the second is necessary to produce 'a| | |b'. This is necessary for you only if both the second and third fields are empty. Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn