Thread: COPY command
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 _________________________________________________________________ Try Live.com: where your online world comes together - with news, sports, weather, and much more. http://www.live.com/getstarted
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
How do I figure out what the "NULL" string would be? The value from TextPad? BTW, I'm running Postgres on XP. J. Michael Nees jmnees@hotmail.com ---------------------------------------- > Date: Tue, 15 Aug 2006 11:20:22 -0400 > From: KeithW@NarrowPathInc.com > To: jmnees@hotmail.com > CC: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] COPY command > > 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 temporarytable with one CHAR column to load the file, then use a second staging table to parse the fields and set appropriatedata types. When I use the COPY command to load the temporary table I only get part of the record. When I encountera FILLER column, which appears to be a NULL value, the rest of the record starting with the Filler doesn't load. Total record size is 455, at position 61 is the first filler field, defined as PIC X(1) from the legacy system. I'mable to load all the records in the file into the temporary table, but only for positions 1 through 60. When I lookedat the flat file 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org _________________________________________________________________ Try Live.com: where your online world comes together - with news, sports, weather, and much more. http://www.live.com/getstarted
>>>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 > > Mike Nees wrote: > How do I figure out what the "NULL" string would be? The value from TextPad? BTW, I'm running Postgres on XP. > > > > > J. Michael Nees > jmnees@hotmail.com Mike, The value shown in TextPad might work. If it were me on a Windows box I would be using gvim to try and determine the same thing. -- Kind Regards, Keith
Keith, I downloaded gvim and viewed the file. It shows the filler field in position 61 as "^@", two characters, position 62 startsthe next field. I've tried the following: COPY tblparcelto_tmp FROM '/pacelto.txt' WITH NULL AS '\^@' COPY tblparcelto_tmp FROM '/pacelto.txt' WITH NULL AS '\^\@' COPY tblparcelto_tmp FROM '/pacelto.txt' WITH NULL AS '^@' ...and many, many other combinations to try to get it to work. No matter what I try each record is truncated at position60. Shouldn't this work? I'm considering abandoning the WITH NULL attribute and use a line editor to take care of the problem. It just seems like this should work and there's something about the character set or NULL value that I don't understand. Any suggestions would be appreciated. Mike Nees ---------------------------------------- > Date: Wed, 16 Aug 2006 11:23:00 -0400 > From: KeithW@NarrowPathInc.com > To: jmnees@hotmail.com > CC: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] COPY command > > >>>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 temporarytable with one CHAR column to load the file, then use a second staging table to parse the fields and set appropriatedata types. When I use the COPY command to load the temporary table I only get part of the record. When I encountera FILLER column, which appears to be a NULL value, the rest of the record starting with the Filler doesn't load. Total record size is 455, at position 61 is the first filler field, defined as PIC X(1) from the legacy system. I'mable to load all the records in the file into the temporary table, but only for positions 1 through 60. When I lookedat the flat file 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 > > > > Mike Nees wrote: > > How do I figure out what the "NULL" string would be? The value from TextPad? BTW, I'm running Postgres on XP. > > > > > > > > > > J. Michael Nees > > jmnees@hotmail.com > > Mike, > > The value shown in TextPad might work. If it were me on a Windows box I > would be using gvim to try and determine the same thing. > > -- > > Kind Regards, > Keith > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq _________________________________________________________________ Try Live.com - your fast, personalized homepage with all the things you care about in one place. http://www.live.com/getstarted