I have a job loading a text file. The first line is just a heading, which is easy to handle. My problem is, the last line is a trailer record that should not be loaded.
In the one job the trailer record has the same number of columns as the detail records, so I can test for the record and just skip it on loading. But with this job, the trailer record has less columns and the job aborts when it gets to the last record.
How do I handle a load like this ?
Dirk Moolman
Database Administrator
Reach Technologies
South Africa
www.thefuelgroup.co.za
"No pessimist ever discovered the secret of the stars, or sailed to an uncharted land, or opened a new doorway for the human spirit." -Helen Keller
==========================================================
This message contains information intended for the perusal, and/or use (if so stated), by the stated addressee(s) only. The information is confidential and privileged. If you are not an intended recipient, do not peruse, use, disseminate, distribute, copy or in any manner rely upon the information contained in this message (directly or indirectly). The sender and/or the entity represented by the sender shall not be held accountable in the event that this prohibition is disregarded. If you receive this message in error, notify the sender immediately by e-mail, fax or telephone representations contained in this message, whether express or implied, are those of the sender only, unless that sender expressly states them to be the views or representations of an entity or person, who shall be named by the sender and who the sender shall state to represent. No liability shall otherwise attach to any other entity or person. ==========================================================
Loading textfile
Dirk,
In the column definition of your input file, define the extra columns (when
comparing last row with rest of the data rows) as Nullable and in the
"Incomplete Column" section, fill them in as "Replace". This way, your DS
job would not abort.
regards,
At 10:38 AM 10/19/2001 +0200, Dirk Moolman wrote:
>I have a job loading a text file. The first line is just a heading,
>which is easy to handle. My problem is, the last line is a trailer
>record that should not be loaded.
>
>In the one job the trailer record has the same number of columns as the
>detail records, so I can test for the record and just skip it on
>loading. But with this job, the trailer record has less columns and the
>job aborts when it gets to the last record.
>
>How do I handle a load like this ?
>
>
>Dirk Moolman
>Database Administrator
>Reach Technologies
>South Africa
>
>www.thefuelgroup.co.za
>
>"No pessimist ever discovered the secret of the stars, or sailed to an
>uncharted land, or opened a new doorway for the human spirit." -Helen
>Keller
>
>
>
>
>==========================================================
>This message contains information intended for the perusal, and/or use
>(if so stated), by the stated addressee(s) only. The information is
>confidential and privileged. If you are not an intended recipient, do
>not peruse, use, disseminate, distribute, copy or in any manner rely
>upon the information contained in this message (directly or
>indirectly). The sender and/or the entity represented by the sender
>shall not be held accountable in the event that this prohibition is
>disregarded. If you receive this message in error, notify the sender
>immediately by e-mail, fax or telephone representations contained in
>this message, whether express or implied, are those of the sender only,
>unless that sender expressly states them to be the views or
>representations of an entity or person, who shall be named by the
>sender and who the sender shall state to represent. No liability shall
>otherwise attach to any other entity or person.
>==========================================================
William Lam Tel: (852)2507-7833
Senior Consultant Fax: (852)2824-3169
DataStage/XE Email: william.lam@AscentialSoftware.com
Ascential Software Hong Kong ICQ: 108363365
In the column definition of your input file, define the extra columns (when
comparing last row with rest of the data rows) as Nullable and in the
"Incomplete Column" section, fill them in as "Replace". This way, your DS
job would not abort.
regards,
At 10:38 AM 10/19/2001 +0200, Dirk Moolman wrote:
>I have a job loading a text file. The first line is just a heading,
>which is easy to handle. My problem is, the last line is a trailer
>record that should not be loaded.
>
>In the one job the trailer record has the same number of columns as the
>detail records, so I can test for the record and just skip it on
>loading. But with this job, the trailer record has less columns and the
>job aborts when it gets to the last record.
>
>How do I handle a load like this ?
>
>
>Dirk Moolman
>Database Administrator
>Reach Technologies
>South Africa
>
>www.thefuelgroup.co.za
>
>"No pessimist ever discovered the secret of the stars, or sailed to an
>uncharted land, or opened a new doorway for the human spirit." -Helen
>Keller
>
>
>
>
>==========================================================
>This message contains information intended for the perusal, and/or use
>(if so stated), by the stated addressee(s) only. The information is
>confidential and privileged. If you are not an intended recipient, do
>not peruse, use, disseminate, distribute, copy or in any manner rely
>upon the information contained in this message (directly or
>indirectly). The sender and/or the entity represented by the sender
>shall not be held accountable in the event that this prohibition is
>disregarded. If you receive this message in error, notify the sender
>immediately by e-mail, fax or telephone representations contained in
>this message, whether express or implied, are those of the sender only,
>unless that sender expressly states them to be the views or
>representations of an entity or person, who shall be named by the
>sender and who the sender shall state to represent. No liability shall
>otherwise attach to any other entity or person.
>==========================================================
William Lam Tel: (852)2507-7833
Senior Consultant Fax: (852)2824-3169
DataStage/XE Email: william.lam@AscentialSoftware.com
Ascential Software Hong Kong ICQ: 108363365
Set the "incomplete columns" rules in the Sequential File stage - scroll right in the Columns grid. Use Help for full information. Put an output constraint on the following Transformer stage so that the header and trailer records are not processed. These techniques are taught in the "Programming with DataStage BASIC" class.
Ray and William, thank you for your replies. I used the "Replace" option under the "incomplete column" section and the job works fine now.
I check the "first line is header" option to suppres the header line, and test for the last line in the constraint box of the transformer stage. The "Replace" option lets me process the trailer without any errors.
Dirk
-----Original Message-----
From: William Lam [mailto:william.lam@AscentialSoftware.com]
Sent: Friday, October 19, 2001 10:50 AM
To: datastage-users@oliver.com; Datastage List
Subject: Re: Loading textfile
Dirk,
In the column definition of your input file, define the extra columns (when comparing last row with rest of the data rows) as Nullable and in the "Incomplete Column" section, fill them in as "Replace". This way, your DS job would not abort.
regards,
At 10:38 AM 10/19/2001 +0200, Dirk Moolman wrote:
>I have a job loading a text file. The first line is just a heading,
>which
is
>easy to handle. My problem is, the last line is a trailer record that
should
>not be loaded.
>
>In the one job the trailer record has the same number of columns as the
>detail records, so I can test for the record and just skip it on
>loading. But with this job, the trailer record has less columns and the
>job aborts when it gets to the last record.
>
>How do I handle a load like this ?
>
>
>Dirk Moolman
>Database Administrator
>Reach Technologies
>South Africa
>
>www.thefuelgroup.co.za
>
>"No pessimist ever discovered the secret of the stars, or sailed to an
>uncharted land, or opened a new doorway for the human spirit." -Helen
>Keller
>
>
>
>
>==========================================================
>This message contains information intended for the perusal, and/or use
>(if so stated), by the stated addressee(s) only. The information is
>confidential and privileged. If you are not an intended recipient, do
>not peruse, use, disseminate, distribute, copy or in any manner rely
>upon the information contained in this message (directly or
>indirectly). The sender and/or the entity represented by the sender
>shall not be held accountable in the event that this prohibition is
>disregarded. If you receive this message in error, notify the sender
>immediately by e-mail, fax or telephone representations contained in
>this message, whether express or implied, are those of the sender only,
>unless that sender expressly states them to be the views or
>representations of an entity or person, who shall be named by the
>sender and who the sender shall state to represent. No liability shall
>otherwise attach to any other entity or person.
>==========================================================
William Lam Tel: (852)2507-7833
Senior Consultant Fax: (852)2824-3169
DataStage/XE Email: william.lam@AscentialSoftware.com
Ascential Software Hong Kong ICQ: 108363365
==========================================================
This message contains information intended for the perusal, and/or use (if so stated), by the stated addressee(s) only. The information is confidential and privileged. If you are not an intended recipient, do not peruse, use, disseminate, distribute, copy or in any manner rely upon the information contained in this message (directly or indirectly). The sender and/or the entity represented by the sender shall not be held accountable in the event that this prohibition is disregarded. If you receive this message in error, notify the sender immediately by e-mail, fax or telephone representations contained in this message, whether express or implied, are those of the sender only, unless that sender expressly states them to be the views or representations of an entity or person, who shall be named by the sender and who the sender shall state to represent. No liability shall otherwise attach to any other entity or person. ==========================================================
I check the "first line is header" option to suppres the header line, and test for the last line in the constraint box of the transformer stage. The "Replace" option lets me process the trailer without any errors.
Dirk
-----Original Message-----
From: William Lam [mailto:william.lam@AscentialSoftware.com]
Sent: Friday, October 19, 2001 10:50 AM
To: datastage-users@oliver.com; Datastage List
Subject: Re: Loading textfile
Dirk,
In the column definition of your input file, define the extra columns (when comparing last row with rest of the data rows) as Nullable and in the "Incomplete Column" section, fill them in as "Replace". This way, your DS job would not abort.
regards,
At 10:38 AM 10/19/2001 +0200, Dirk Moolman wrote:
>I have a job loading a text file. The first line is just a heading,
>which
is
>easy to handle. My problem is, the last line is a trailer record that
should
>not be loaded.
>
>In the one job the trailer record has the same number of columns as the
>detail records, so I can test for the record and just skip it on
>loading. But with this job, the trailer record has less columns and the
>job aborts when it gets to the last record.
>
>How do I handle a load like this ?
>
>
>Dirk Moolman
>Database Administrator
>Reach Technologies
>South Africa
>
>www.thefuelgroup.co.za
>
>"No pessimist ever discovered the secret of the stars, or sailed to an
>uncharted land, or opened a new doorway for the human spirit." -Helen
>Keller
>
>
>
>
>==========================================================
>This message contains information intended for the perusal, and/or use
>(if so stated), by the stated addressee(s) only. The information is
>confidential and privileged. If you are not an intended recipient, do
>not peruse, use, disseminate, distribute, copy or in any manner rely
>upon the information contained in this message (directly or
>indirectly). The sender and/or the entity represented by the sender
>shall not be held accountable in the event that this prohibition is
>disregarded. If you receive this message in error, notify the sender
>immediately by e-mail, fax or telephone representations contained in
>this message, whether express or implied, are those of the sender only,
>unless that sender expressly states them to be the views or
>representations of an entity or person, who shall be named by the
>sender and who the sender shall state to represent. No liability shall
>otherwise attach to any other entity or person.
>==========================================================
William Lam Tel: (852)2507-7833
Senior Consultant Fax: (852)2824-3169
DataStage/XE Email: william.lam@AscentialSoftware.com
Ascential Software Hong Kong ICQ: 108363365
==========================================================
This message contains information intended for the perusal, and/or use (if so stated), by the stated addressee(s) only. The information is confidential and privileged. If you are not an intended recipient, do not peruse, use, disseminate, distribute, copy or in any manner rely upon the information contained in this message (directly or indirectly). The sender and/or the entity represented by the sender shall not be held accountable in the event that this prohibition is disregarded. If you receive this message in error, notify the sender immediately by e-mail, fax or telephone representations contained in this message, whether express or implied, are those of the sender only, unless that sender expressly states them to be the views or representations of an entity or person, who shall be named by the sender and who the sender shall state to represent. No liability shall otherwise attach to any other entity or person. ==========================================================