Informix

Hi,

while trying to select data from external table, we encountered following errors message :


# echo "select count(*) from mytable_ext;" | dbaccess $DBNAME

Database selected.

26197: Reached maximum error limit during load: (1).
Error in line 1
Near character position 38

Database closed.


See uploaded external table definition, and the the log file errors below

create external table "informix".mytable_ext
(
num_int_tc integer,
num_uat varchar(17),
reference varchar(17),
cod_pro varchar(17),
cod_cou varchar(17),
cod_tai varchar(17),
dd decimal(32,15),
tva decimal(32,15),
uvc integer,
typedecl varchar(255),
soustype varchar(255),
nrdeclaration bigint,
tauxdouane decimal(32,15),
codepref integer,
datedeclare varchar(25)
)
using
(
datafiles
(
"DISK:/tmp/myfile.txt"
),
format "delimited",
DELIMITER "|",
rejectfile "/tmpp/myfile.log",
maxerrors 150,
escape off
);

revoke all on "informix".mytable_ext from "public" as "informix";


informix:/tmp/# cat myfile.log
/tmp/myfile.txt~1~TOO_MANY_FLDS~<none>~672854|46GAR22FRCH1228|GD81930|1415857|GRF|TU|.083333333333333|6.958333333333333|360.0000000|IM|C|2200011008||100|0~/01/2022|

Can someone help, please

Best regards

------------------------------
Gilles NJOH TCHAPPI
------------------------------

#Informix

Original Message:
Sent: 1/18/2022 3:52:00 AM
From: Gilles NJOH TCHAPPI
Subject: 26197: Reached maximum error limit during load: (1). when trying to select from external table

Hi,

while trying to select data from external table, we encountered following errors message :


# echo "select count(*) from mytable_ext;" | dbaccess $DBNAME

Database selected.

26197: Reached maximum error limit during load: (1).
Error in line 1
Near character position 38

Database closed.


See uploaded external table definition, and the the log file errors below

create external table "informix".mytable_ext
(
num_int_tc integer,
num_uat varchar(17),
reference varchar(17),
cod_pro varchar(17),
cod_cou varchar(17),
cod_tai varchar(17),
dd decimal(32,15),
tva decimal(32,15),
uvc integer,
typedecl varchar(255),
soustype varchar(255),
nrdeclaration bigint,
tauxdouane decimal(32,15),
codepref integer,
datedeclare varchar(25)
)
using
(
datafiles
(
"DISK:/tmp/myfile.txt"
),
format "delimited",
DELIMITER "|",
rejectfile "/tmpp/myfile.log",
maxerrors 150,
escape off
);

revoke all on "informix".mytable_ext from "public" as "informix";


informix:/tmp/# cat myfile.log
/tmp/myfile.txt~1~TOO_MANY_FLDS~<none>~672854|46GAR22FRCH1228|GD81930|1415857|GRF|TU|.083333333333333|6.958333333333333|360.0000000|IM|C|2200011008||100|0~/01/2022|

Can someone help, please

Best regards

------------------------------
Gilles NJOH TCHAPPI
------------------------------

#Informix

Original Message:
Sent: 1/18/2022 2:20:00 PM
From: Paul Watson
Subject: RE: 26197: Reached maximum error limit during load: (1). when trying to select from external table

TOO_MANY_FLDS  - look for an embedded delimiter in your data

 

Cheers

Paul

 

Original Message:
Sent: 1/18/2022 3:52:00 AM
From: Gilles NJOH TCHAPPI
Subject: 26197: Reached maximum error limit during load: (1). when trying to select from external table

Hi,

while trying to select data from external table, we encountered following errors message :


# echo "select count(*) from mytable_ext;" | dbaccess $DBNAME

Database selected.

26197: Reached maximum error limit during load: (1).
Error in line 1
Near character position 38

Database closed.


See uploaded external table definition, and the the log file errors below

create external table "informix".mytable_ext
(
num_int_tc integer,
num_uat varchar(17),
reference varchar(17),
cod_pro varchar(17),
cod_cou varchar(17),
cod_tai varchar(17),
dd decimal(32,15),
tva decimal(32,15),
uvc integer,
typedecl varchar(255),
soustype varchar(255),
nrdeclaration bigint,
tauxdouane decimal(32,15),
codepref integer,
datedeclare varchar(25)
)
using
(
datafiles
(
"DISK:/tmp/myfile.txt"
),
format "delimited",
DELIMITER "|",
rejectfile "/tmpp/myfile.log",
maxerrors 150,
escape off
);

revoke all on "informix".mytable_ext from "public" as "informix";


informix:/tmp/# cat myfile.log
/tmp/myfile.txt~1~TOO_MANY_FLDS~<none>~672854|46GAR22FRCH1228|GD81930|1415857|GRF|TU|.083333333333333|6.958333333333333|360.0000000|IM|C|2200011008||100|0~/01/2022|

Can someone help, please

Best regards

------------------------------
Gilles NJOH TCHAPPI
------------------------------

#Informix

Take a look at that last field that shows the "~" in it - is that what you expect?  There may be a funky character in the file.

Also make sure that that there are no spaces at the end of the record in the file being loaded, or an extra newline or carriage return.

Pipe the first line of the file through od -c to look for non-ascii characters.

------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
------------------------------

Original Message:
Sent: Tue January 18, 2022 03:51 AM
From: Gilles NJOH TCHAPPI
Subject: 26197: Reached maximum error limit during load: (1). when trying to select from external table

Hi,

while trying to select data from external table, we encountered following errors message :


# echo "select count(*) from mytable_ext;" | dbaccess $DBNAME

Database selected.

26197: Reached maximum error limit during load: (1).
Error in line 1
Near character position 38

Database closed.


See uploaded external table definition, and the the log file errors below

create external table "informix".mytable_ext
(
num_int_tc integer,
num_uat varchar(17),
reference varchar(17),
cod_pro varchar(17),
cod_cou varchar(17),
cod_tai varchar(17),
dd decimal(32,15),
tva decimal(32,15),
uvc integer,
typedecl varchar(255),
soustype varchar(255),
nrdeclaration bigint,
tauxdouane decimal(32,15),
codepref integer,
datedeclare varchar(25)
)
using
(
datafiles
(
"DISK:/tmp/myfile.txt"
),
format "delimited",
DELIMITER "|",
rejectfile "/tmpp/myfile.log",
maxerrors 150,
escape off
);

revoke all on "informix".mytable_ext from "public" as "informix";


informix:/tmp/# cat myfile.log
/tmp/myfile.txt~1~TOO_MANY_FLDS~<none>~672854|46GAR22FRCH1228|GD81930|1415857|GRF|TU|.083333333333333|6.958333333333333|360.0000000|IM|C|2200011008||100|0~/01/2022|

Can someone help, please

Best regards

------------------------------
Gilles NJOH TCHAPPI
------------------------------

#Informix

If you transferred the file from another server, esp. a Windows computer, then there may well be additional characters in the file, including additional carriage returns at the end of each line.  Remove with a "tr" command or "dos2unix" (if installed).

------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
------------------------------

Original Message:
Sent: Tue January 18, 2022 02:22 PM
From: Mike Walker
Subject: 26197: Reached maximum error limit during load: (1). when trying to select from external table

Take a look at that last field that shows the "~" in it - is that what you expect?  There may be a funky character in the file.

Also make sure that that there are no spaces at the end of the record in the file being loaded, or an extra newline or carriage return.

Pipe the first line of the file through od -c to look for non-ascii characters.

------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com

Original Message:
Sent: Tue January 18, 2022 03:51 AM
From: Gilles NJOH TCHAPPI
Subject: 26197: Reached maximum error limit during load: (1). when trying to select from external table

Hi,

while trying to select data from external table, we encountered following errors message :


# echo "select count(*) from mytable_ext;" | dbaccess $DBNAME

Database selected.

26197: Reached maximum error limit during load: (1).
Error in line 1
Near character position 38

Database closed.


See uploaded external table definition, and the the log file errors below

create external table "informix".mytable_ext
(
num_int_tc integer,
num_uat varchar(17),
reference varchar(17),
cod_pro varchar(17),
cod_cou varchar(17),
cod_tai varchar(17),
dd decimal(32,15),
tva decimal(32,15),
uvc integer,
typedecl varchar(255),
soustype varchar(255),
nrdeclaration bigint,
tauxdouane decimal(32,15),
codepref integer,
datedeclare varchar(25)
)
using
(
datafiles
(
"DISK:/tmp/myfile.txt"
),
format "delimited",
DELIMITER "|",
rejectfile "/tmpp/myfile.log",
maxerrors 150,
escape off
);

revoke all on "informix".mytable_ext from "public" as "informix";


informix:/tmp/# cat myfile.log
/tmp/myfile.txt~1~TOO_MANY_FLDS~<none>~672854|46GAR22FRCH1228|GD81930|1415857|GRF|TU|.083333333333333|6.958333333333333|360.0000000|IM|C|2200011008||100|0~/01/2022|

Can someone help, please

Best regards

------------------------------
Gilles NJOH TCHAPPI
------------------------------

#Informix

Original Message:
Sent: 1/18/2022 2:57:00 PM
From: Mike Walker
Subject: RE: 26197: Reached maximum error limit during load: (1). when trying to select from external table

If you transferred the file from another server, esp. a Windows computer, then there may well be additional characters in the file, including additional carriage returns at the end of each line.  Remove with a "tr" command or "dos2unix" (if installed).

------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com
------------------------------

Original Message:
Sent: Tue January 18, 2022 02:22 PM
From: Mike Walker
Subject: 26197: Reached maximum error limit during load: (1). when trying to select from external table

Take a look at that last field that shows the "~" in it - is that what you expect?  There may be a funky character in the file.

Also make sure that that there are no spaces at the end of the record in the file being loaded, or an extra newline or carriage return.

Pipe the first line of the file through od -c to look for non-ascii characters.

------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com

Original Message:
Sent: Tue January 18, 2022 03:51 AM
From: Gilles NJOH TCHAPPI
Subject: 26197: Reached maximum error limit during load: (1). when trying to select from external table

Hi,

while trying to select data from external table, we encountered following errors message :


# echo "select count(*) from mytable_ext;" | dbaccess $DBNAME

Database selected.

26197: Reached maximum error limit during load: (1).
Error in line 1
Near character position 38

Database closed.


See uploaded external table definition, and the the log file errors below

create external table "informix".mytable_ext
(
num_int_tc integer,
num_uat varchar(17),
reference varchar(17),
cod_pro varchar(17),
cod_cou varchar(17),
cod_tai varchar(17),
dd decimal(32,15),
tva decimal(32,15),
uvc integer,
typedecl varchar(255),
soustype varchar(255),
nrdeclaration bigint,
tauxdouane decimal(32,15),
codepref integer,
datedeclare varchar(25)
)
using
(
datafiles
(
"DISK:/tmp/myfile.txt"
),
format "delimited",
DELIMITER "|",
rejectfile "/tmpp/myfile.log",
maxerrors 150,
escape off
);

revoke all on "informix".mytable_ext from "public" as "informix";


informix:/tmp/# cat myfile.log
/tmp/myfile.txt~1~TOO_MANY_FLDS~<none>~672854|46GAR22FRCH1228|GD81930|1415857|GRF|TU|.083333333333333|6.958333333333333|360.0000000|IM|C|2200011008||100|0~/01/2022|

Can someone help, please

Best regards

------------------------------
Gilles NJOH TCHAPPI
------------------------------

#Informix