Mike Nees wrote:
> I have several large fixed record length files that I'm trying to load into a new db. My plan is to use a temporary
tablewith one CHAR column to load the file, then use a second staging table to parse the fields and set appropriate
datatypes. When I use the COPY command to load the temporary table I only get part of the record. When I encounter a
FILLERcolumn, which appears to be a NULL value, the rest of the record starting with the Filler doesn't load. Total
recordsize is 455, at position 61 is the first filler field, defined as PIC X(1) from the legacy system. I'm able to
loadall the records in the file into the temporary table, but only for positions 1 through 60. When I looked at the
flatfile in TextPad, TextPad lists a special character at position 61 with a value as "NUL (^@ = 0 = 0x0)".
>
> My questions:
>
> Are these just null characters?
> Not knowing PERL; Is there any easy solution to removing these characters programmatically prior to using the COPY
command?
>
>
> I can manually remove the NULL characters, but I'm looking for a better solution.
>
>
> Any help would be appreciated.
>
> Thanks,
>
>
> Mike
Mike,
The COPY command does allow you to define the NULL character using the
WITH NULL AS attribute.
COPY mytable
FROM 'myfile'
WITH NULL AS 'null string';
See http://www.postgresql.org/docs/8.0/interactive/sql-copy.html
HTH
--
Kind Regards,
Keith
