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

Answer 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