How to format a file's last 3 columns starting at the 30th tab or 88th space

Question

Ubuntu 16.04 GNU bash, version 4.4.0

I would like to format this text file filed with zip codes containing 1,264,524 lines.

#!/bin/bash

wget http://download.geonames.org/export/zip/allCountries.zip
unzip allCountries.zip
mv -f allCountries.txt .allCountries.txt
rm -f allCountries.zip

This is a section of the file unformatted.

AD AD100 Canillo                    42.5833  1.6667   6
AD AD200 Encamp                     42.5333  1.6333   6
AD AD300 Ordino                     42.6  1.55  6
AD AD400 La Massana                    42.5667  1.4833   6
AD AD500 Andorra la Vella                    42.5  1.5   6
AD AD600 Sant Julià de Lòria                    42.4667  1.5   6
AD AD700 Escaldes-Engordany                     42.5  1.5667   6
AR 3636  POZO CERCADO (EL CHORRO (F), DPTO. RIVADAVIA (S))  Salta A              -23.4933 -61.9267 3
AR 4123  LAS SALADAS Salta A              -25.7833 -64.5 4
AR 4126  BARADERO Salta A              -26.0833 -65.263  3
AR 4126  EL CUIBAL   Salta A              -26.0833 -65.263  3
AR 4126  LA ASUNCION Salta A              -26.0833 -65.263  3
AR 4126  MIRAFLORES (TALA, DPTO. CANDELARIA) Salta A              -26.0833 -65.263  3
AR 4141  TOLOMBON Salta A              -26.2 -65.9167 4
AR 4141  QUISCA GRANDE  Salta A              -26.4367 -65.97   3
AR 4141  LA CIENEGUITA  Salta A              -26.4367 -65.97   3
AR 4141  MACHO RASTROJO Salta A              -26.4367 -65.97   3
AR 4190  ROSARIO DE LA FRONTERA  Salta A              -25.8 -64.9667 4
AR 4190  OVANDO   Salta A              -25.8 -65.1 4
AR 4190  SAN ESTEBAN Salta A              -25.8 -65.0333 3
AR 4190  LA BANDA (R. DE LA FRONTERA, DPTO. ROSARIO DE LA FRONTERA)  Salta A              -25.8 -65.0333 3
AR 4190  LA MATILDE  Salta A              -25.8 -65.0333 3
AR 4190  LAS PIEDRITAS  Salta A              -25.8 -65.0333 3
AR 4190  LOS POCITOS Salta A              -25.8 -65.0333 3
AR 4190  OJO DE AGUA (ROSARIO DE LA FRONTERA, DPTO. R.DE LA FRONTERA)   Salta A              -25.8 -65.0333 3
AR 4190  POTRERILLO (R. DE LA FRONTERA, DPTO. ROSARIO DE LA FRONTERA)   Salta A              -25.8 -65.0333 3

This should be the final outcome:

AD AD100 Canillo                                                                          42.5833     1.6667      6
AD AD200 Encamp                                                                           42.5333     1.6333      6
AD AD300 Ordino                                                                           42.6        1.56        6
AD AD400 La Massana                                                                       42.5667     1.4833      6
AD AD500 Andorra la Vella                                                                 42.5        1.6         6
AD AD600 Sant Julià de Lòria                                                              42.4667     1.5         6
AD AD700 Escaldes-Engordany                                                               42.5        1.5667      6
AR 3636  POZO CERCADO (EL CHORRO (F), DPTO. RIVADAVIA (S))  Salta A                       -23.4933    -61.9267    3
AR 4123  LAS SALADAS Salta A                                                              -25.7833    -64.5       4
AR 4126  BARADERO Salta A                                                                 -26.0833    -65.263     3
AR 4126  EL CUIBAL   Salta A                                                              -26.0833    -65.263     3
AR 4126  LA ASUNCION Salta A                                                              -26.0833    -65.263     3
AR 4126  MIRAFLORES (TALA, DPTO. CANDELARIA) Salta A                                      -26.0833    -65.263     3
AR 4141  TOLOMBON Salta A                                                                 -26.2       -65.9167    4
AR 4190  OJO DE AGUA (ROSARIO DE LA FRONTERA, DPTO. R.DE LA FRONTERA)   Salta A           -25.8       -65.0333    3
AR 4190  POTRERILLO (R. DE LA FRONTERA, DPTO. ROSARIO DE LA FRONTERA)   Salta A           -25.8       -65.0333    3

So 30th tab or 88 spaces from the left the 3rd to the last column should begin. The first character on the last 3 columns is 12 spaces apart.

Every line has data so I tried cutting the last 3 columns into another file. Then I tried removing all whitespaces from the original file then formatting it into columns. The third column gives me troubles because if do

awk 'BEGIN{ OFS="\t"}{ print $1, $2, NR }' .allCountries.txt

AT      4873    Pehigen 34069
AT      4873    Hofberg 34070
AT      4873    Wiederhals      34071
AT      4873    Oberedt 34072
AT      4873    Oberegg 34073
AT      4873    Raitenberg      34074
AT      4873    Redltal 34075
AT      4873    Friedhalbing    34076
AT      4873    Unterhaselbach  34077
AT      4873    Redltal 34078
AT      4873    Erkaburgen      34079
AT      4873    Mayrhof 34080
AT      4873    Erdpries        34081
AT      4873    Grünbergsiedlung        34082
AT      4873    Brunnhölzl      34083
AT      4873    Seibrigen       34084
AT      4873    Kinast  34085
AT      4873    Stöckert        34086
AT      4873    Frankenburg     34087
AT      4873    Fischeredt      34088
AT      4873    Marigen 34089
AT      4873    Oberhaselbach   34090
AT      4873    Ottokönigen     34091
AT      4873    Fischigen       34092
AT      4873    Endriegl        34093

Any help would be grand.

Answer 1

This seems to do it. Being a simple single line of awk, it should run fairly quickly against 1.2m line file. By creating a dummy file of 1.3m lines, based on your sample, I saw it complete within 24 seconds on a basic CentOS 7 VM with 4GB memory and GNU Awk 4.0.2.

To get some accurate responses from StackExchange, you might want to post your sample input on pastebin, together with sample output.

$ awk '{printf "%s %-6s%-81s%-12s%-12s%s\n",$1,$2,gensub(/[0-9.-]* +[0-9.-]* +[0-9]$/,"","g",substr($0,10)),$(NF-2),$(NF-1),$NF}' inp | head
AD AD100 Canillo                                                                          42.5833     1.6667      6
AD AD200 Encamp                                                                           42.5333     1.6333      6
AD AD300 Ordino                                                                           42.6        1.55        6
AD AD400 La Massana                                                                       42.5667     1.4833      6
AD AD500 Andorra la Vella                                                                 42.5        1.5         6
AD AD600 Sant Julià de Lòria                                                              42.4667     1.5         6
AD AD700 Escaldes-Engordany                                                               42.5        1.5667      6
AR 3636  POZO CERCADO (EL CHORRO (F), DPTO. RIVADAVIA (S))  Salta A                       -23.4933    -61.9267    3
AR 4123  LAS SALADAS Salta A                                                              -25.7833    -64.5       4
AR 4126  BARADERO Salta A                                                                 -26.0833    -65.263     3
$

Try it online!

Answer 2

I was able to accomplish what I believe you want with the following script, although I'm not sure how well it will work on 1mil+ lines.

#!/usr/local/bin/bash

tmp_dir="$(mktemp -d -t 'text.XXXXX' || mktemp -d 2>/dev/null)"
input=./input
output=./output
tmp_input1=${tmp_dir}/temp_input1.txt
tmp_input2=${tmp_dir}/temp_input2.txt
col1="${tmp_dir}/col1.txt"
col2="${tmp_dir}/col2.txt"
col3="${tmp_dir}/col3.txt"
col4="${tmp_dir}/col4.txt"
col5="${tmp_dir}/col5.txt"
col6="${tmp_dir}/col6.txt"

tr -s ' ' <"$input" > "$tmp_input1"

awk '{print $1}' "$tmp_input1" > "$col1"

awk '!($1="")' "$tmp_input1" > "$tmp_input2"

awk '{print $1}' "$tmp_input2" > "$col2"

awk '!($1="")' "$tmp_input2" > "$tmp_input1"

awk '{print $NF}' "$tmp_input1" > "$col6"

awk '!($NF="")' "$tmp_input1" > "$tmp_input2"

awk '{print $NF}' "$tmp_input2" > "$col5"

awk '!($NF="")' "$tmp_input2" > "$tmp_input1"

awk '{print $NF}' "$tmp_input1" > "$col4"

awk '!($NF="")' "$tmp_input1" > "$tmp_input2"

cat "$tmp_input2" > "$col3"

paste -d'\t' "$col1" "$col2" "$col3" "$col4" "$col5" "$col6"  | column -s$'\t' -t > "$output"

rm -r "$tmp_dir"

This creates a bunch of tmp files (one for each column and two for modification of the original file) and processes through your original input removing one column at a time until it's left with column 3. It then pastes all the tmp files together tab separated and uses column to format that output as you desire.

With your given input:

$ ./script.sh
$ cat output
AD  AD100  Canillo                                                                42.5833   1.6667    6
AD  AD200  Encamp                                                                 42.5333   1.6333    6
AD  AD300  Ordino                                                                 42.6      1.55      6
AD  AD400  La Massana                                                             42.5667   1.4833    6
AD  AD500  Andorra la Vella                                                       42.5      1.5       6
AD  AD600  Sant Julià de Lòria                                                    42.4667   1.5       6
AD  AD700  Escaldes-Engordany                                                     42.5      1.5667    6
AR  3636   POZO CERCADO (EL CHORRO (F), DPTO. RIVADAVIA (S)) Salta A              -23.4933  -61.9267  3
AR  4123   LAS SALADAS Salta A                                                    -25.7833  -64.5     4
AR  4126   BARADERO Salta A                                                       -26.0833  -65.263   3
AR  4126   EL CUIBAL Salta A                                                      -26.0833  -65.263   3
AR  4126   LA ASUNCION Salta A                                                    -26.0833  -65.263   3
AR  4126   MIRAFLORES (TALA, DPTO. CANDELARIA) Salta A                            -26.0833  -65.263   3
AR  4141   TOLOMBON Salta A                                                       -26.2     -65.9167  4
AR  4141   QUISCA GRANDE Salta A                                                  -26.4367  -65.97    3
AR  4141   LA CIENEGUITA Salta A                                                  -26.4367  -65.97    3
AR  4141   MACHO RASTROJO Salta A                                                 -26.4367  -65.97    3
AR  4190   ROSARIO DE LA FRONTERA Salta A                                         -25.8     -64.9667  4
AR  4190   OVANDO Salta A                                                         -25.8     -65.1     4
AR  4190   SAN ESTEBAN Salta A                                                    -25.8     -65.0333  3
AR  4190   LA BANDA (R. DE LA FRONTERA, DPTO. ROSARIO DE LA FRONTERA) Salta A     -25.8     -65.0333  3
AR  4190   LA MATILDE Salta A                                                     -25.8     -65.0333  3
AR  4190   LAS PIEDRITAS Salta A                                                  -25.8     -65.0333  3
AR  4190   LOS POCITOS Salta A                                                    -25.8     -65.0333  3
AR  4190   OJO DE AGUA (ROSARIO DE LA FRONTERA, DPTO. R.DE LA FRONTERA) Salta A   -25.8     -65.0333  3
AR  4190   POTRERILLO (R. DE LA FRONTERA, DPTO. ROSARIO DE LA FRONTERA) Salta A   -25.8     -65.0333  3