Page 1 of 1

Sequential file

Posted: Fri Oct 17, 2008 8:29 am
by kittu.raja
Hi,
When I am trying to load data from one flat file to another flat file which has nulls, I am getting a warning " exporting Nulls without Null handling properties" I tried to Null handle in the transformer then also its says the same thing. In the Output file I should have nullability as yes for some columns. Can anybody help me out.

Posted: Fri Oct 17, 2008 8:32 am
by chulett
First suggestion - take your actual "exporting nulls" error and search the forums for that exact string.

Posted: Fri Oct 17, 2008 8:44 am
by kittu.raja
chulett wrote:First suggestion - take your actual "exporting nulls" error and search the forums for that exact string. ...

Mine is the only message it has in the forum. Nobody has raised this issue before

Posted: Fri Oct 17, 2008 9:42 am
by Nagaraj
Try to load it into Dataset it would be much faster and Null handling will be taken care much easily here.

Posted: Fri Oct 17, 2008 9:59 am
by chulett
kittu.raja wrote:
chulett wrote:First suggestion - take your actual "exporting nulls" error and search the forums for that exact string. ...
Mine is the only message it has in the forum. Nobody has raised this issue before
No, you're not. Use a common portion of your actual error, not what you put in your first post.

Posted: Fri Oct 17, 2008 10:40 am
by kittu.raja
Ya true but we want in sequential file only.

Posted: Fri Oct 17, 2008 10:40 am
by kittu.raja
Ya true but we want in sequential file only.

Posted: Fri Oct 17, 2008 10:44 am
by kittu.raja
chulett wrote:
kittu.raja wrote:
chulett wrote:First suggestion - take your actual "exporting nulls" error and search the forums for that exact string. ...
Mine is the only message it has in the forum. Nobody has raised this issue before
No, you're not. Use a common portion of your actual error, not what you put in your first post.
I searched by "Exporting Nulls" but it doesnt have anything

Posted: Fri Oct 17, 2008 10:52 am
by ArndW
Odd, I found this thread.

Posted: Fri Oct 17, 2008 11:26 am
by jdmiceli
Is the source file being created by DataStage? If so, then set the Null handling settings in the stage that creates the Sequential file.

Posted: Fri Oct 17, 2008 1:12 pm
by kittu.raja
jdmiceli wrote:Is the source file being created by DataStage? If so, then set the Null handling settings in the stage that creates the Sequential file.

No, I am getting a flat file from other source. Its not created by datastage. I handled the nulls then also its says the same.

Posted: Fri Oct 17, 2008 1:14 pm
by kittu.raja
ArndW wrote:Odd, I found this thread. ...
Yup I got it. But is there any other way. my file has 53 columns and 30 columns have null in them. Now i have to append spaces to 30 columns?

Posted: Mon Oct 20, 2008 8:36 am
by jdmiceli
Are these essentially showing up as non-printing characters? Or maybe characters outside the standard ASCII range (32-127 I think)? If so, try this:

Create a routine called 'RemoveNonPrintingChars' (or whatever - it doens't really matter). There will be one argument called 'Arg1' and the code goes like this:

Code: Select all

      Text = Arg1

      If Text=OCONV(Text, "MCP") Then
         Ans = Text
      End Else
         LetterCount=LEN(Text)
         Ans = ""
         For i=1 to LetterCount
            letter=Text[i,1]
            AsciiLetter=SEQ(letter)
            Begin Case
               Case AsciiLetter < 32 Or AsciiLetter > 127
                  letter = " "
            End Case
            Ans := letter
         Next i
      End
This code is from V8 by the way. I don't know if this will do you any good, but give it a shot.

If you want to pre-process the entire flat file, then you could do it with a little Perl script at the command prompt level (assuming you are just substituting a non-printing character with a space). That code looks like this:

Code: Select all

#!/usr/local/bin/perl -w

use strict;
use warnings;

my ($filepath, $filename, $newfilename, $line, $hold);
my @array;

$hold = '';

$filepath = "$ARGV[0]";
$filename = "$ARGV[1]";
$newfilename = "$filepath" . "fix_" . "$filename";

$filename = "$filepath"."$filename";

open(O,"< $filename")||die "Could not open $filename for processing!\n";
open(F,"> $newfilename")||die "Could not open $newfilename for processing!\n";

while ($line = <O>)
{
        if ($line =~ //)
        {
                $line =~ s// /g;
                $hold = $hold . $line;
                chomp $hold;
                next;
        }

        if (($line =~ /\n/) and (length($hold)> 0))
        {
                $hold = $hold . $line;
                print F $hold;
                $hold = '';
                next;
        }

        # if none of the other option have appeared then this is a normal row
        print F $line;
}
close (O);
close (F);

# rename($newfilename,$filename);
The last line of the script above (rename) is commented out. Running this will let you test without destroying your original file. Once tested, you could remove the pound and this will then rename the working file to the same as your original, overwriting it. You may have to modify the code above to suit your needs, but I would be interested to know if it works for you. :?:

Hope that helps!

Posted: Mon Oct 20, 2008 9:12 am
by throbinson
If your files are not fixed width, then do a search on this APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL.
The solution will be to do two things.
1. Add that env variable to your Project and set it to True
2. Define the default for any field that contains a NULL to "" (empty) at the line level.