Fixed width COPY

webb sprague <wsprague@o1.com> writes:

Does anybody know of a good way to COPY a file into a table if the data is
based on fixed width format?

COPY insists on having delimiters; you'll need to translate the file
format into something COPY can deal with.

Do I just have to write some code with scanf(Ick)?

If you like C, that would work, but you could doubtless accomplish the
same result with very short scripts in sed, awk, perl, tcl, or half a
dozen other text-bashing tools. Pick your poison...

regards, tom lane

COPY is unfortunately not flexible. You will need to edit the data to
insert delimiters (check first to make sure that your choice of delimiter
does not appear in your data).
You had best delete all of the leading and trailing whitespace in each field
as well, to avoid trouble with non text-type datatypes.
For an easy script, you could check out GNU Awk (gawk), which lets you split
fields based on widths: your script might look something like:

BEGIN {
FIELDWIDTHS = "width1 width2 width3.... widthn" # set field widths for
splitting
OFS = "\t" # set output field separator

}
{
$1 = $1 # Force field splitting based on
widths
for (i = 1; i <= NF; i++) { # Loop through fields,
deleting leading & trailing spaces
sub (/^ +/, "", $i)
sub (/ +$/, "", $i)
}
print # Output the result
}

"width1" etc is just width in characters for each field.
This example uses tabs as delimiter, but you can specify whatever you want.
I'm sure that Perl can easily do this too, but I don't know enough to
suggest a script.
Forgive me if you know all of this already. I get carried away sometimes.