How to Populate field in File with it's manipulated Filename?

Shell Programming and Scripting
1

Hi All,

I need to create a script to process on 10 files. Mentioned below is one of those file and the requirement.

Input File
DCIA_GEOG_DATA_OCEAN.TXT
Sample Record
"Terr","TerrName","Dist","DistName","REGION","RgnName","BCName"
"A0010000","Abilene TX A 1","A0010957","Dallas TX","A0010998","West","US HEADQUARTERS"
"A0010001","Akron OH A 1","A0010954","Cleveland OH","A0010997","Central","US HEADQUARTERS"
"A0010002","Alaska AK A 1","A0010991","Seattle WA","A0010998","West","US HEADQUARTERS"

The output removes the qoute and makes the file a pipe delimited and adds a null field before the last field and a field after the last field.
The last field should be populated with the word which comes after the last "_" and before ".txt in file name"(OCEAN in this case)

Output record needed:
A0010000|Abilene TX A 1|A0010957|Dallas TX|A0010998|West||US HEADQUARTERS|OCEAN
A0010001|Akron OH A 1|A0010954|Cleveland OH|A0010997|Central||US HEADQUARTERS|OCEAN
A0010002|Alaska AK A 1|A0010991|Seattle WA|A0010998|West||US HEADQUARTERS|OCEAN

Any help is appreciated. Thanks in advance.:slight_smile:

2

Try

$ cat file
"Terr","TerrName","Dist","DistName","REGION","RgnName","BCName"
"A0010000","Abilene TX A 1","A0010957","Dallas TX","A0010998","West","US HEADQUARTERS"
"A0010001","Akron OH A 1","A0010954","Cleveland OH","A0010997","Central","US HEADQUARTERS"
"A0010002","Alaska AK A 1","A0010991","Seattle WA","A0010998","West","US HEADQUARTERS"

$ awk 'BEGIN{FS=",";OFS="|";}
NR==1{s=FILENAME;n=split(s,P,"[_.]")}
NR>1{gsub("\"","");$NF= OFS $NF OFS P[n-1];print}'  DCIA_GEOG_DATA_OCEAN.TXT

A0010000|Abilene TX A 1|A0010957|Dallas TX|A0010998|West||US HEADQUARTERS|OCEAN
A0010001|Akron OH A 1|A0010954|Cleveland OH|A0010997|Central||US HEADQUARTERS|OCEAN
A0010002|Alaska AK A 1|A0010991|Seattle WA|A0010998|West||US HEADQUARTERS|OCEAN
3

Awesome Pamu. That really solves the issue. Just wanted to know if i have 10 files. and have to perform the above processing with all files and then i need to concatenate all files into a single file . what should be the approach?
Should i use your code 10 times on each file and create 10 temp files? and then concatenate them into a single file? Will the above code handle the commas if there are any between the double qoutes?

4

try:

awk '/[.]TXT/{sub("[.]TXT","");sub(".*_","");f=$0}/,..*,/{gsub("\",\"","|");sub("^\"","");sub("\"$","");$1=$1 ; print $0,f}' OFS="|" in
5

With some assumptions...

You can feed your all files to end of awk and just add > output_file at the end to redirect the output.

awk 'BEGIN{FS="\"";OFS="";}
FNR==1{s=FILENAME;n=split(s,P,"[_.]")}
FNR>1{for(i=1;i<=NF;i+=2){gsub(",","|",$i)};
k=split($0,T,"|");sub(T[k],"");T[k]= "|" T[k] "|" P[n-1];print $0,T[k]}'  DCIA_GEOG_DATA_OCEAN.TXT DCIA_GEOG_DATA_FIRDS.TXT
6

Genius Stuff Pamu. :b: really solves my issue..thanks all.. for your effort.:slight_smile: