Sequential file

[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.

[chulett]

First suggestion - take your actual "exporting nulls" error and search the forums for that exact string.

[kittu.raja]

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

[Nagaraj]

Try to load it into Dataset it would be much faster and Null handling will be taken care much easily here.

[chulett]

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.

[kittu.raja]

Ya true but we want in sequential file only.

[kittu.raja]

Ya true but we want in sequential file only.

[kittu.raja]

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

[ArndW]

Odd, I found this thread.

[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.

[kittu.raja]

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.

[kittu.raja]

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?

[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!

[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.