Re: A "Load from" question.

Jonathan Leffler wrote in message <6up72t$g1b$1@news.xmission.com>... >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 > ---snip--- Another technique is if the fields are defined to default to spaces instead of permitting nulls. That does require a change of the database change, but it does permit you on a field by field basis to fill in different values in different fields. S.W.