Problem with a tab separated file

Shell Programming and Scripting
1

Hi,
I have created a tab separated file from the following input file.

ADDRESS1        CITY    STATE   POSTAL  COUNTRY LON LAT
32 PRINZREGENTENSTRASSE ROSENHEIM   BAYERN  83022 DEU   1212182 4785699
263 VIA DANTE ALIGHIERI BARI    PUGLIA  70122   ITA 1686233     4112154
30 VIA MILANO   TREVIGLIO       LOMBARDIA   24047 ITA   956961  4552289
2 KREUZWEG      ERSIGEN BERN    3423    CHE 759377  4709277
28 VICOLO DELLA FONTANA ROMA    LAZIO   00198   ITA 1250887     4191608
3 MUNCHPLATZ    10. BEZIRK-FAVORITEN    WIEN    1100    AUT     1636588 4815423
7 BAHNHOFSTRASSE  LANGNAU IM EMMENTAL   BERN    3550    CHE     778447  4693928
1 HASENBERG     ALTSTADT        SACHSEN 01067   DEU 1374604     5105173
3 PLACE DU 8 MAI 1945   SAULNES LORRAINE    54650 FRA   582300  4953234

As I was only interested in columns 1,2,4 and 5 and i wanted to reorganize it, I used the following awk command.

awk -F"\t" '{print $5"\t"$4"\t"$2"\t"$1}' infile.dat > outfile.dat

The above code has created the following output file.

COUNTRY	POSTAL	CITY	ADDRESS1
DEU	83022	ROSENHEIM       32 PRINZREGENTENSTRASSE
ITA	70122	BARI    263 VIA DANTE ALIGHIERI
ITA	24047   TREVIGLIO       30 VIA MILANO
CHE	3423    ERSIGEN 2 KREUZWEG
ITA	00198   ROMA    28 VICOLO DELLA FONTANA
AUT	1100    10. BEZIRK-FAVORITEN    3 MUNCHPLATZ
CHE	3550    LANGNAU IM EMMENTAL     7 BAHNHOFSTRASSE
DEU	01067   ALTSTADT        1 HASENBERG
FRA	54650   SAULNES 3 PLACE DU 8 MAI 1945

However, when i open this file with excel on Windows and i specify it is a tab delimited file, The complete row is shown as a single column. Whereas in UNIX when i check this file with the following command It shows that there are 4 columns.

cat WEUM.dat | head -5 | awk -F"\t" '{print $3}'

Am I missing something, how to fix this. I use the output file as an input to some java program, I tried debugging the java program and found out that the total row is considered as a single column, instead of 4 columns

2

Your "\t"'s are causing the output to *display* with the tabs expanded and replaced with space characters. You need an actual tab character instead.

awk -F"\t" '{print $5"\t"$4"\t"$2"\t"$1}' infile.dat > outfile.dat

Delete the "\t"'s in the print command and instead, inside the double-quotes type <CTRL-V><CTRL-I> to insert an actual tab character. In vi use the ":set list" command to show you the tab characters as a "^I" and end-of-line as a "$".

awk '{print $5"^I"$4"^I"$2"^I"$1}' xx.dat$

Output redirected to a file, viewed in vi with :set list looks like this:

COUNTRY^IPOSTAL^ICITY^IADDRESS1$

Now any program expecting a tab-delimited file will see the actual tab characters and parse correctly.