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.
> -----Original Message-----
> From: webb sprague [SMTP:wsprague@o1.com]
> Sent: Monday, May 07, 2001 2:45 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Fixed width COPY
>
> Does anybody know of a good way to COPY a file into a table if the data is
>
> based on fixed width format? Do I just have to write some code with
> scanf(Ick)? For example (Sorry about the poor ASCII art formatting):
>
> | FIELD DESCRIPTION LENGTH POSITION|
> |_______________________________________________________|
> | 1. Action (E=eff, D=del, P=pend eff) 1 1
> | 2. Action Date (mmddyy) 6 2-7
> | 3. Country Code 3
> 8-10
> | 4. filler 1 11
> 5. Paired Code 3 12-14
> | 6. filler 1
> 15
> | 7. Country Name 65 16-80
> | 8. Footnote code (LERGINFO, Sec 1.1) 1 81
> | 9. filler 9
> 82-90
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
