Thread: Import File

On Tue, Aug 23, 2005 at 11:47:57AM -0300, Rodrigo Africani wrote:
> I'm trying to import a sequence txt file.
> The file has fix columns ... i.e the first 10 positions is the primary key ...
> The comand copy need a delimitier but the file is seq without a delimitier.
> I look in the manual but i don't won't if it is possible.
> My problem is the file is very large ... > 1G and I don't have a lot of memory
> to change the file and put delimitiers.

If you have enough disk space then you don't need a lot of memory
to change the file: simply run it through a filter that reads one
line at a time, changes it, and writes it to a new file.  Such
filters are trivial to write in Perl and other scripting languages.
If you don't have enough disk space then you could pipe the filter's
output into psql and use "COPY FROM stdin" -- this might be faster
anyway because it involves less disk I/O.

--
Michael Fuhr

am  23.08.2005, um 11:47:57 -0300 mailte Rodrigo Africani folgendes:
> Hi,
>
> I'm trying to import a sequence txt file.
> The file has fix columns ... i.e the first 10 positions is the primary key ...
> The comand copy need a delimitier but the file is seq without a delimitier.
> I look in the manual but i don't won't if it is possible.
> My problem is the file is very large ... > 1G and I don't have a lot of memory to change the file and put
delimitiers. 

There are lot of tools like sed, awk, perl, ... to manipulate a file.
This tools can act as a filter, big file aren't a problem.


Regards, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

On Tue, 23 Aug 2005, Rodrigo Africani wrote:

> Hi,
>
> I'm trying to import a sequence txt file.
> The file has fix columns ... i.e the first 10 positions is the primary key ...
> The comand copy need a delimitier but the file is seq without a delimitier.
> I look in the manual but i don't won't if it is possible.
> My problem is the file is very large ...
> 1G and I don't have a lot of memory to change the file and put delimitiers.
>

I'm not sure if you mean your input file is fixed field (eg, col1 = chars
1-10, col2=chars 11-15, etc) instead of having a specified character (eg,
"," or space or tab) between the columns.

With a delimiter, you can (very roughly):

cat <file> | psql <db> -c "copy from stdin;"

with fixed columns you can use awk:

cat <file> | awk '{print "substr(1,10), ..." }' | psql <db> -c "copy from
stdin;"


The only other inerpretation I can see from your description is that the
data is one column pet line, rather than one record per line.

In this case a simple script or program to assemble the lines into the
appropriate number of columns might be necessary, but it should be able to
read stdin and write to stdout so that you can still pipe (|) your file tp
psql.


Hope this helps,

  Brent