newline character in text field

IBM Informix Dynamic Server Version 11.50.FC6X3 I am attempting to load data from a pipe '|' delimited file. The data has a text field that has newline or crlf (I'm not sure how to tell the difference). Fortunately the file is also fixed length. I have tried dbload using both delimited and fixed length formats but can't get either to work. The fixed length error is: 'The position of the fixed-length field is out of range in the load file.' Apparently because it starts a new record after each newline. Anyone have any ideas? The file was dumped from a db2 database and if I need to I can have the dba create the file again.

Em 08/07/2010 13:26, BEVIS KENNEDY escreveu: > IBM Informix Dynamic Server Version 11.50.FC6X3 > > I am attempting to load data from a pipe '|' delimited file. The data has a > text field that has newline or crlf (I'm not sure how to tell the difference). > Fortunately the file is also fixed length. > > I have tried dbload using both delimited and fixed length formats but can't > get either to work. > > The fixed length error is: > 'The position of the fixed-length field is out of range in the load file.' > > Apparently because it starts a new record after each newline. Anyone have any > ideas? > > The file was dumped from a db2 database and if I need to I can have the dba > create the file again. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Hello. Just a tip: are you changing from different machine architectures??? If so, you can convert the file before importing it (ex: dos2unix, unix2dos) Regards. -- Alexandre Marini Tecnologia da Informação - DBA SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg> IBM Informix Dynamic Server Certified Professional V10 / V11

Thanks for the response. The problem is not that the newline / crlf's are not getting converted properly. The problem is when they exist within the body of the record dbload and hpl treat them as the end-of-record. I had the db2 dba create the file again with '~' on the end of each record. I'm trying to get HPL loader to work with '~' as record end but the output of the execution looks like: Error accessing sysmaster, SQL error -206, ISAM error -111 Error occured at HPL failpoint: 11430 Error occured at HPL failpoint: 11008 I just started troubleshooting this problem.

Hi, Take a look at the ALLOW_NEWLINE configuration parameter. I'm not sure that will do the trick in this case since the parameter is for quoted strings, but give it a shot. Cheers, Dick Snoke Executive IT Specialist IBM Software Group - ChannelWorks Tel: (404) 487-1595 Email: dsnoke@us.ibm.com From: "BEVIS KENNEDY" <bkennedy@utah.gov> To: ids@iiug.org Date: 07/08/10 04:18 PM Subject: Re: newline character in text field [20548] Sent by: ids-bounces@iiug.org Thanks for the response. The problem is not that the newline / crlf's are not getting converted properly. The problem is when they exist within the body of the record dbload and hpl treat them as the end-of-record. I had the db2 dba create the file again with '~' on the end of each record. I'm trying to get HPL loader to work with '~' as record end but the output of the execution looks like: Error accessing sysmaster, SQL error -206, ISAM error -111 Error occured at HPL failpoint: 11430 Error occured at HPL failpoint: 11008 I just started troubleshooting this problem. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi Dbload can handle newline character ('0x0D') in a text field. However, if your data contains multibyte characters and delimiter "|" happen to be a valid part of valid multibyte character for that locale. Then dbload maybe confused. I need more information to reproduce it. A set of sample data which is not working should be good start. (couple rows will be enough). Regards, -Ping ----- Original Message ---- From: Alexandre Marini <amarini@fazenda.ms.gov.br> To: ids@iiug.org Sent: Thu, July 8, 2010 1:38:54 PM Subject: Re: newline character in text field [20546] Em 08/07/2010 13:26, BEVIS KENNEDY escreveu: > IBM Informix Dynamic Server Version 11.50.FC6X3 > > I am attempting to load data from a pipe '|' delimited file. The data has a > text field that has newline or crlf (I'm not sure how to tell the difference). > Fortunately the file is also fixed length. > > I have tried dbload using both delimited and fixed length formats but can't > get either to work. > > The fixed length error is: > 'The position of the fixed-length field is out of range in the load file.' > > Apparently because it starts a new record after each newline. Anyone have any > ideas? > > The file was dumped from a db2 database and if I need to I can have the dba > create the file again. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Hello. Just a tip: are you changing from different machine architectures??? If so, you can convert the file before importing it (ex: dos2unix, unix2dos) Regards. -- Alexandre Marini Tecnologia da Informação - DBA SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg> IBM Informix Dynamic Server Certified Professional V10 / V11 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.