1

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.

Improve this question
3
  • 1
    hope your input and expected output is not synced... plz cross check...
    – Siva
    Aug 3, 2018 at 9:44
  • Unclear to me why folk have voted the question as unclear.
    – steve
    Aug 3, 2018 at 16:24
  • I wonder if there's confusion regarding the output from the awk attempt, given the first fields of "AT" versus the sample input & desired output having "AD" and "AR" first fields. Some of the wording in the question might be confusing, but the sample input & output make it pretty clear to me.
    – Jeff Schaller ♦
    Aug 3, 2018 at 19:34

2 Answers 2

Reset to default
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!

Improve this answer
3
  • I did not know I was able to link exterior urls yet. I will in the future. I am confused as to I run this command against my input file. This is what I have done and I get different results. awk 'your command' "$inFile" >> "$outFile"
    – Curious Sam
    Aug 4, 2018 at 1:19
  • can you have a look at what I came up with? https://pastebin.com/cWGU67Nn
    – Curious Sam
    Aug 5, 2018 at 11:54
  • Is your file by any chance in DOS format (\r\n) ? That would break the awk script. You'd need to pipe through dos2unix first.
    – steve
    Aug 5, 2018 at 18:41
0

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
Improve this answer
5
  • In your script, what does text.XXXXX mean?
    – Curious Sam
    Aug 4, 2018 at 1:24
  • 1
    It will be the name of the tmp directory. xxxxx will be a randomly generated string.
    – jesse_b
    Aug 4, 2018 at 1:51
  • https://pastebin.com/b8mgCNUx When I run it on my machine, the first 3 columns are fine. The last 3 are so far to the right, that the columns are off of my screen.
    – Curious Sam
    Aug 4, 2018 at 16:27
  • @needtoknow: It seems like there are two reasons for that: (1) The content in the third column of your input is (sometimes) very long. (2) Your terminal width cannot support a string that long. You can't have your cake and eat it too ;)
    – jesse_b
    Aug 4, 2018 at 16:38
  • See OP. I added the organization that releases the file. I think you are right. There are some long sections
    – Curious Sam
    Aug 4, 2018 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.