External Tables
Peter Logan (IDS 11.70.XC3 on AIX) had a pipe-delimited file produced by a SQL Server DTS package that loaded fine with dbload, but failed in an external table with a "record indicator not found" error. Replies pointed out two likely causes: possible DOS CR/LF line endings (suggesting dos2unix, and checking with od rather than vi, since vi can hide CRs), and the main issue that external tables expect a trailing field delimiter at the end of each line. Art Kagel suggested pre-processing the file to append a pipe, e.g. awk '{ printf "%s|\\\\n", $0; }' infile > outfile. The poster thanked him; no confirmation of the final result is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 11.70.XC3
Aix 6.1
Here is the scene ... have a file created by a SQL Server DTS package
which is taking data from Oracle and putting it into a pipe delimited
ascii file .. The file has no pipe at the end of each line, but there is
a line feed there ...
Using dbload this file loads into IDS just fine... Trying to use an
external table, the load fails with a record indicator not found error.
Have tried several different things in the external table create statement
to get it to work ..
Question, if dbload loads this without doing anything special:
file '/usr/local/whmgr/data/prices.txt' delimiter '|' 31;
insert into prices;
then why isn't an external table working?
create ...
using ...
)
format 'delimited'
delimiter '|'
rejectfile '/usr/local/whmgr/logs/prices_ext.log'
maxerrors 1
express
);
I have tried several different recordend parameters ...
Any help would be appreciated ... oh also, if I unload this file from our
production system, then the external table works fine .. the difference in
the file is that is putting a '|' at the end of each line...
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
Hello Peter.
It seems that you´re facing an easier CR/CR+LF conversion trouble on your file.
Did you try to convert it before loading? (dos2unix) ?
Regards.
Alexandre Marini
IBM Informix Certified Professional v10 / v11.50 / v11.70
IBM Information Management Informix Technical Professional
IBM Infosphere DataStage Technical Professional
Database Administrator
> To: ids@iiug.org
> From: Peter_Logan@spartanstores.com
> Subject: External Tables [26943]
> Date: Thu, 3 May 2012 11:00:05 -0400
>
> IDS 11.70.XC3
> Aix 6.1
>
> Here is the scene ... have a file created by a SQL Server DTS package
> which is taking data from Oracle and putting it into a pipe delimited
> ascii file .. The file has no pipe at the end of each line, but there is
> a line feed there ...
>
> Using dbload this file loads into IDS just fine... Trying to use an
> external table, the load fails with a record indicator not found error.
> Have tried several different things in the external table create statement
> to get it to work ..
>
> Question, if dbload loads this without doing anything special:
>
> file '/usr/local/whmgr/data/prices.txt' delimiter '|' 31;
> insert into prices;>
> then why isn't an external table working?
>
> create ...
> using ...
> )
> format 'delimited'
> delimiter '|'
> rejectfile '/usr/local/whmgr/logs/prices_ext.log'
> maxerrors 1
> express
> );
>
> I have tried several different recordend parameters ...
>
> Any help would be appreciated ... oh also, if I unload this file from our
> production system, then the external table works fine .. the difference in
> the file is that is putting a '|' at the end of each line...
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
As far as i know External Table expects a field delimiter(pipe '|' in your case) at the end of every field in the data file. Something like col1|col2|col3| col1|col2|col3|
Peter
check that you have unix file and not a dos file
Cheers
Paul
> IDS 11.70.XC3
> Aix 6.1
>
> Here is the scene ... have a file created by a SQL Server DTS package
> which is taking data from Oracle and putting it into a pipe delimited
> ascii file .. The file has no pipe at the end of each line, but there is
> a line feed there ...
>
> Using dbload this file loads into IDS just fine... Trying to use an
> external table, the load fails with a record indicator not found error.
> Have tried several different things in the external table create statement
> to get it to work ..
>
> Question, if dbload loads this without doing anything special:
>
> file '/usr/local/whmgr/data/prices.txt' delimiter '|' 31;
> insert into prices;>
> then why isn't an external table working?
>
> create ...
> using ...
> )
> format 'delimited'
> delimiter '|'
> rejectfile '/usr/local/whmgr/logs/prices_ext.log'
> maxerrors 1
> express
> );
>
> I have tried several different recordend parameters ...
>
> Any help would be appreciated ... oh also, if I unload this file from our
> production system, then the external table works fine .. the difference in
> the file is that is putting a '|' at the end of each line...
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
--
Paul Watson
Tel: +1 913-674-0360
Mob: +1 913-387-7529
Web: www.oninit.com
www.advancedatatools.com
Failure is not as frightening as regret.
If you want to improve, be content to be thought foolish and stupid.
What this country needs are more unemployed politicians
Did not ... since it was working ok with dbload .. thought it should also=20
work with the ext. table ... I will have them try that .... however when =
looking at the file in VI, it looks like it has the proper new line on it=20
...
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From: "Alexandre Marini" <alexandre@briug.org>
To: ids@iiug.org
Date: 05/03/2012 11:29 AM
Subject: RE: External Tables [26944]
Sent by: ids-bounces@iiug.org
Hello Peter.=20
It seems that you=B4re facing an easier CR/CR+LF conversion trouble on your=
=20
file.=20
Did you try to convert it before loading? (dos2unix) ?=20
Regards.=20
Alexandre Marini=20
IBM Informix Certified Professional v10 / v11.50 / v11.70=20
IBM Information Management Informix Technical Professional=20
IBM Infosphere DataStage Technical Professional=20
Database Administrator=20
> To: ids@iiug.org=20
> From: Peter=5FLogan@spartanstores.com=20
> Subject: External Tables [26943]=20
> Date: Thu, 3 May 2012 11:00:05 -0400=20
>=20
> IDS 11.70.XC3=20
> Aix 6.1=20
>=20
> Here is the scene ... have a file created by a SQL Server DTS package=20
> which is taking data from Oracle and putting it into a pipe delimited=20
> ascii file .. The file has no pipe at the end of each line, but there is =
> a line feed there ...=20
>=20
> Using dbload this file loads into IDS just fine... Trying to use an=20
> external table, the load fails with a record indicator not found error.=20
> Have tried several different things in the external table create=20
statement=20
> to get it to work ..=20
>=20
> Question, if dbload loads this without doing anything special:=20
>=20
> file '/usr/local/whmgr/data/prices.txt' delimiter '|' 31;=20
> insert into prices;=20>=20
> then why isn't an external table working?=20
>=20
> create ...=20
> using ...=20
> )=20
> format 'delimited'=20
> delimiter '|'=20
> rejectfile '/usr/local/whmgr/logs/prices=5Fext.log'=20
> maxerrors 1=20
> express=20
> );=20
>=20
> I have tried several different recordend parameters ...=20
>=20
> Any help would be appreciated ... oh also, if I unload this file from=20
our=20
> production system, then the external table works fine .. the difference=20
in=20
> the file is that is putting a '|' at the end of each line...=20
>=20
> Peter Logan=20
> Senior Database Administrator=20
> Phone: 616/878-8309=20
>=20
>=20
>=20
***************************************************************************=
****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
Just post-process the file. A simple one line awk script or a 10 line C
app will do the trick. Here's the awk version:
awk '{ printf "%s|\\
", $0;}' infile >outfile
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, May 3, 2012 at 11:00 AM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:
> IDS 11.70.XC3
> Aix 6.1
>
> Here is the scene ... have a file created by a SQL Server DTS package
> which is taking data from Oracle and putting it into a pipe delimited
> ascii file .. The file has no pipe at the end of each line, but there is
> a line feed there ...
>
> Using dbload this file loads into IDS just fine... Trying to use an
> external table, the load fails with a record indicator not found error.
> Have tried several different things in the external table create statement
> to get it to work ..
>
> Question, if dbload loads this without doing anything special:
>
> file '/usr/local/whmgr/data/prices.txt' delimiter '|' 31;
> insert into prices;>
> then why isn't an external table working?
>
> create ...
> using ...
> )
> format 'delimited'
> delimiter '|'
> rejectfile '/usr/local/whmgr/logs/prices_ext.log'
> maxerrors 1
> express
> );
>
> I have tried several different recordend parameters ...
>
> Any help would be appreciated ... oh also, if I unload this file from our
> production system, then the external table works fine .. the difference in
> the file is that is putting a '|' at the end of each line...
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f3ba959e01de604bf23ed09
Check with od instead. Vi sometimes eats carriage returns. However, IB
the problem is the lack of a trailing delimiter.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, May 3, 2012 at 11:33 AM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:
> Did not ... since it was working ok with dbload .. thought it should
> also=20
> work with the ext. table ... I will have them try that .... however when =
>
> looking at the file in VI, it looks like it has the proper new line on
> it=20
> ....
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
> From: "Alexandre Marini" <alexandre@briug.org>
> To: ids@iiug.org
> Date: 05/03/2012 11:29 AM
> Subject: RE: External Tables [26944]
> Sent by: ids-bounces@iiug.org
>
> Hello Peter.=20
> It seems that you=B4re facing an easier CR/CR+LF conversion trouble on
> your=
> =20
> file.=20
> Did you try to convert it before loading? (dos2unix) ?=20
>
> Regards.=20
>
> Alexandre Marini=20
> IBM Informix Certified Professional v10 / v11.50 / v11.70=20
>
> IBM Information Management Informix Technical Professional=20
>
> IBM Infosphere DataStage Technical Professional=20
> Database Administrator=20
>
> > To: ids@iiug.org=20
> > From: Peter=5FLogan@spartanstores.com=20
> > Subject: External Tables [26943]=20
> > Date: Thu, 3 May 2012 11:00:05 -0400=20
> >=20
> > IDS 11.70.XC3=20
> > Aix 6.1=20
> >=20
> > Here is the scene ... have a file created by a SQL Server DTS package=20
> > which is taking data from Oracle and putting it into a pipe delimited=20
> > ascii file .. The file has no pipe at the end of each line, but there is
> =
>
> > a line feed there ...=20
> >=20
> > Using dbload this file loads into IDS just fine... Trying to use an=20
> > external table, the load fails with a record indicator not found
> error.=20
> > Have tried several different things in the external table create=20
> statement=20
> > to get it to work ..=20
> >=20
> > Question, if dbload loads this without doing anything special:=20
> >=20
> > file '/usr/local/whmgr/data/prices.txt' delimiter '|' 31;=20
> > insert into prices;=20> >=20
> > then why isn't an external table working?=20
> >=20
> > create ...=20
> > using ...=20
> > )=20
> > format 'delimited'=20
> > delimiter '|'=20
> > rejectfile '/usr/local/whmgr/logs/prices=5Fext.log'=20
> > maxerrors 1=20
> > express=20
> > );=20
> >=20
> > I have tried several different recordend parameters ...=20
> >=20
> > Any help would be appreciated ... oh also, if I unload this file from=20
> our=20
> > production system, then the external table works fine .. the
> difference=20
> in=20
> > the file is that is putting a '|' at the end of each line...=20
> >=20
> > Peter Logan=20
> > Senior Database Administrator=20
> > Phone: 616/878-8309=20
> >=20
> >=20
> >=20
>
> ***************************************************************************=
> ****=20
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.=20
> >=20
>
>
> ***************************************************************************=
> ****=20
>
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba6e8eb8dcb91b04bf23f53c
Thanks ...
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From: "Art Kagel" <art.kagel@gmail.com>
To: ids@iiug.org
Date: 05/03/2012 12:03 PM
Subject: Re: External Tables [26948]
Sent by: ids-bounces@iiug.org
Just post-process the file. A simple one line awk script or a 10 line C
app will do the trick. Here's the awk version:
awk '{ printf "%s|\\
", $0;}' infile >outfile
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated
nor
those of the entities themselves.
On Thu, May 3, 2012 at 11:00 AM, Peter_Logan@spartanstores.com <
Peter_Logan@spartanstores.com> wrote:
> IDS 11.70.XC3
> Aix 6.1
>
> Here is the scene ... have a file created by a SQL Server DTS package
> which is taking data from Oracle and putting it into a pipe delimited
> ascii file .. The file has no pipe at the end of each line, but there is
> a line feed there ...
>
> Using dbload this file loads into IDS just fine... Trying to use an
> external table, the load fails with a record indicator not found error.
> Have tried several different things in the external table create
statement
> to get it to work ..
>
> Question, if dbload loads this without doing anything special:
>
> file '/usr/local/whmgr/data/prices.txt' delimiter '|' 31;
> insert into prices;>
> then why isn't an external table working?
>
> create ...
> using ...
> )
> format 'delimited'
> delimiter '|'
> rejectfile '/usr/local/whmgr/logs/prices_ext.log'
> maxerrors 1
> express
> );
>
> I have tried several different recordend parameters ...
>
> Any help would be appreciated ... oh also, if I unload this file from
our
> production system, then the external table works fine .. the difference
in
> the file is that is putting a '|' at the end of each line...
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f3ba959e01de604bf23ed09
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.