Question on delimiters
IDS Version is 9.4.FC9 OS is Solaris 8 We received a file we are trying to load to a temp table to process and has "||" as the field delimiter has anyone work with multiple character delimiters or any suggestions, tried tr but then if you have a blank field then we get wrong number of arguments. Bruce Simms Data Base Services TALX Corporation 2330 Ball Drive St. Louis, MO 63146 Phone (314) 214-7703 FAX (314) 983-3238 bsimms@talx.com
Hi
In .UNL file that has rows of a table, when you find twice || means that
column have NULL value.But, maybe when a person type data to insert in a table
maybe was typed a delimiter caracter "|".In this case, you should identify a
column in the table and run the statement in dbaccess:
select * from <table> where <columns> like "%|%"You'll find out the row(s) and you have to substitute this caracters.
Or, IF you want , you can chance a delimiter before "unload", using:
set delimiter "^" for exemple ...
If you choose a "select" statement above , you can generate a temp table and
use the fields of the primary key to update all these rows.
E.g
select col1, col2, col3 from TABLE where COLUMN like "%|%" into temp tTABLE
with no log;(I wanna mean that the PK has 3 columns: col1, col2 and col3).
begin work; (take care the number of rows and MAXLOCKS)
update TABLE set COLUMN = NULL where exists
(select 0 from tTABLE where tTABLE.col1 = TABLE.col1 and tTABLE.col2 =
TABLE.col2 and tTABLE.col3 = TABLE.col3)if all runned ok, COMMIT work;
Best regards
Roberto FERRONATO
> To: ids@iiug.org> From: BSimms@talx.com> Subject: Question on delimiters
[13485]> Date: Thu, 25 Sep 2008 08:57:14 -0400> > IDS Version is 9.4.FC9 > OS
is Solaris 8 > > We received a file we are trying to load to a temp table to
process and > has "||" as the field delimiter has anyone work with multiple
character > delimiters or any suggestions, tried tr but then if you have a
blank > field then we get wrong number of arguments. > > Bruce Simms > Data
Base Services > TALX Corporation > 2330 Ball Drive > St. Louis, MO 63146 > >
Phone (314) 214-7703 > FAX (314) 983-3238 > bsimms@talx.com > > >
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum. >
_________________________________________________________________
Connect to the next generation of MSN Messenger
http://imagine-msn.com/messenger/launch80/default.aspx?locale=en-us&source=wlmai
ltagline
sed 's/||/|/g' <infile >outfile Art On Thu, Sep 25, 2008 at 8:57 AM, Bruce Simms <BSimms@talx.com> wrote: > IDS Version is 9.4.FC9 > OS is Solaris 8 > > We received a file we are trying to load to a temp table to process and > has "||" as the field delimiter has anyone work with multiple character > delimiters or any suggestions, tried tr but then if you have a blank > field then we get wrong number of arguments. > > Bruce Simms > Data Base Services > TALX Corporation > 2330 Ball Drive > St. Louis, MO 63146 > > Phone (314) 214-7703 > FAX (314) 983-3238 > bsimms@talx.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Run it through a sed script first and convert double-pipes to single. (off the top of my head... don't have access to a test machine.. do pipe symbols need to be escaped?) file convert.sed: /||/s//|/g sed -f convert.sed input.file > newinput.file -- Bob -------------- Original message -------------- From: "Bruce Simms" <BSimms@talx.com> > IDS Version is 9.4.FC9 > OS is Solaris 8 > > We received a file we are trying to load to a temp table to process and > has "||" as the field delimiter has anyone work with multiple character > delimiters or any suggestions, tried tr but then if you have a blank > field then we get wrong number of arguments. > > Bruce Simms > Data Base Services > TALX Corporation > 2330 Ball Drive > St. Louis, MO 63146 > > Phone (314) 214-7703 > FAX (314) 983-3238 > bsimms@talx.com > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >