Populating File data with custom manipulation on file names

Shell Programming and Scripting
1

Hi,

I am confused how to proceed firther please find the problem below:
Input Files:

DCIA_GEOG_DATA_OCEAN.TXT
DCIA_GEOG_DATA_MCRO.TXT
DCIA_GEOG_DATA_CVAS.TXT
DCIA_GEOG_DATA_MCR.TXT

Output File Name: MMA_RFC_GEOG_NAM_DIM_LOD.txt

Sample Record(DCIA_GEOG_DATA_OCEAN.TXT):(Layout same for all input files

"Terr","TerrName","Dist","DistName","REGION","RgnName","BCName"
"A0019473","Abington PA 1","A0013142","Langhorne PA","A0013261","Philadelphia PA","PROGRESSIVE"
"A0019476","Albany GA 1","A0013068","Albany GA","A0013251","Jacksonville FL","TRADITIONAL"

Requirement:
Concatenate 4 files by removing the headers from all four files.Convert comma to pipeline and remove qoutes.
I got the Solution:

awk 'FNR != 1' ${Inpt_File1} ${Inpt_File2} ${Inpt_File3} ${Inpt_File4} > temp_file.txt
awk -F '"' 'NF>1{ for(i=1;i<=NF;i+=2){gsub(",","|",$i)}} NF==1{gsub(",","|")}1' OFS="" temp_file.txt > ${Outpt_File}

Now I need to do the folowing:
Add a blank field before the last field and add a field after the last field and populate the last field with the last part of the 4 file names for their respective records - between the last "_" and the ".txt".

eg: Records for DCIA_GEOG_DATA_OCEAN.TXT file will have data like below in output:

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

Records for DCIA_GEOG_DATA_MCRO.TXT file will have data like below in output:

A0010000|Abilene TX A 1|A0010957|Dallas TX|A0010998|West||US HEADQUARTERS|MCRO
A0010001|Akron OH A 1|A0010954|Cleveland OH|A0010997|Central||US HEADQUARTERS|MCRO

Records for DCIA_GEOG_DATA_CVAS.TXT file will have data like below in output:

A0010000|Abilene TX A 1|A0010957|Dallas TX|A0010998|West||US HEADQUARTERS|CVAS
A0010001|Akron OH A 1|A0010954|Cleveland OH|A0010997|Central||US HEADQUARTERS|CVAS

Records for DCIA_GEOG_DATA_MCR.TXT file will have data like below in output:

A0010000|Abilene TX A 1|A0010957|Dallas TX|A0010998|West||US HEADQUARTERS|MCR
A0010001|Akron OH A 1|A0010954|Cleveland OH|A0010997|Central||US HEADQUARTERS|MCR

Any Help Appreciated !!!

2

try:

awk 'BEGIN {f=FILENAME;sub("[.]TXT","",f);sub(".*_","",f)}
/,.*,/ {gsub("\",\"","|");sub("^\"","");sub("\"$","");$1=$1 ; print $0,f}' OFS="|" in_file1 in_file2 in_file3 in_file4
3

Hi, Thanks very much for your patience to read the thread and answring that.
but your code didnt gave appropriate output. See the output below:

Terr|TerrName|Dist|DistName|REGION|RgnName|BCName|OFS=|
A0019473|Abington|PA|1|A0013142|Langhorne|PA|A0013261|Philadelphia|PA|PROGRESSIVE|OFS=|
A0019476|Albany|GA|1|A0013068|Albany|GA|A0013251|Jacksonville|FL|TRADITIONAL|OFS=|
A0019482|Albuquerque|S|NM|1|A0013070|Albuquerque|NM|A0013262|Phoenix|AZ|COST|CONTAINMENT|OFS=|
A0019485|Alexandria|VA|1|A0013229|Washington|DC|A0013273|Washington|DC|EMERGING|QUALITY|OFS=|
4

corrected, try:

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

great... man thanks fr ur patience.... :b:. All the files mrged successfully with correct data..:slight_smile: will look forward for help in future from you