Re: Newbie question about importing text files...

On 10/10/06, Jonathan Greenberg <jgreenberg(at)arc(dot)nasa(dot)gov> wrote:
> So I've been looking at the documentation for COPY, and I'm curious about a
> number of features which do not appear to be included, and whether these
> functions are found someplace else:

> 1) How do I skip an arbitrary # of "header" lines (e.g. > 1 header line) to
> begin reading in data?

if in 'csv' mode, you can set the header flag. if not, standard unix
tools fit the bill:
cat import.txt | tail -n +2 > output.txt <-- from bash
copy table foo from '/home/import.txt';

on windows? get cygwin!

or, if you prefer a more sql-ish solution, load your text data into
scratch tables (all text fields) as is and do filtering there. this
works pretty well actually.

copy table foo from '/home/import.txt';
create table bar as select * from foo offset 3;

theres a million way to do this, most inolve processing before or
after the copy statement, unless you happen to be importing csv
(often, but not always works as is) or data generated from postgresql.

> 2) Is it possible to screen out lines which begin with a comment character
> (common outputs for csv/txt files from various programs)?

see notes above.
1. import all data to scratch table
2. use sql

alternative: master perl (i prefer sql approach usually, perl scares me!)

> 3) Is there a way to read in fixed width files?

here again you could load the data into postgresql, one field per row
even, and process as such
create table import(bigfield text);
copy tabe import from 'foo.txt';

create table foo as select substr(bigfield, 1, 3)::int as a,
substr(bigfield, 4, 2)::char(2) as b, [...];

voila!

merlin