How to upload text file in postgresql which have multiple space between columns?

Question

Here file1 & file2 5 column but file2 have 4 columns data available. I am not able to upload/import file in table because they have no delimiter. Table total column 5 & every file should be upload/import in same table.

Every column's value has fixed length like column1 has 1,10; column2 has 14,12; column3 has 30,2; column4 has 41,8; column5 has 54,4.

> I use PostgreSQL Database Server. What should I do? Please help me.

file1

88056846     465             L          4.009        AB3C
88056846     465             L                       AB3C
88056846     465             L          4.009        AB3C

file2

88056846     465                        4.009        AB3C
88056846     465                        4.009        AB3C
88056846     465                        4.009        AB3C

enter image description here is real view file1 & file2

I think pgloader has support for such formats.

Laurenz Albe
– Laurenz Albe

2022-02-22 07:17:35 +00:00
Commented Feb 22, 2022 at 7:17 — Laurenz Albe
– Laurenz Albe, Commented Feb 22, 2022 at 7:17

James Brown · Accepted Answer · 2022-02-23 04:42:26Z

1

You could, for example, use GNU awk to convert the fixed width file to better form before loading:

$ gawk '
BEGIN {
    FIELDWIDTHS="8 8 14 15 12"  # estimated from your second sample data
    OFS=","
}
{
    for(i=1;i<=NF;i++)          # loop all fields
        gsub(/^ *| *$/,"",$i)   # trim space
    print                       # output
}' file

Output of the first file:

88056846,465,,4.009,AB3C
88056846,465,,4.009,AB3C
88056846,465,,4.009,AB3C

If your data has some sort of header, you don't need to estimate the field widths but use this: Fixed width to CSV.

Edited per comment:

awk '
BEGIN {
    OFS="|"
}
{
    a[++c]=substr($0,1,10)
    a[++c]=substr($0,14,12)
    a[++c]=substr($0,30,2)
    a[++c]=substr($0,41,8)
    a[++c]=substr($0,54,4)

    for(i=1;i<=c;i++) {
        gsub(/^ *| *$/,"",a[i])
        printf "%s%s",a[i],(i<c?OFS:ORS)
    }
    c=0
}' file

edited Feb 23, 2022 at 4:42

answered Feb 22, 2022 at 7:12

James Brown

37.7k8 gold badges52 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tanzir Over a year ago

Thank you @James Brown for your attention. I am not able to get accurate result. Is it possible to set {postion:length} like this ${line:1:10} ${line:14:12} ${line:30:2} ${line:41:8} ${line:54:4}? Also I wist to set "|" instead of "," and script will run automatically in all file. Need your kind response. Thank you once again.

Collectives™ on Stack Overflow

How to upload text file in postgresql which have multiple space between columns?

file1

file2

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

file1

file2

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related