Visit The New, Modern Unix Linux Community


Populating File data with custom manipulation on file names


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Populating File data with custom manipulation on file names
# 1  
Populating File data with custom manipulation on file names

Hi,

I am confused how to proceed firther please find the problem below:
Input Files:
Code:
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)SmilieLayout same for all input files
HTML Code:
"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:
Code:
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:
HTML Code:
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:
Code:
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:
HTML Code:
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:
Code:
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:
Code:
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:

HTML Code:
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:
Code:
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.... Smilie. All the files mrged successfully with correct data..Smilie will look forward for help in future from you

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #88
Difficulty: Easy
PowerShell makes use of pipes.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Sort file data according to a custom list of string

I have a string of pre defined ip address list which will always remain constant their order will never change like in below sample: iplist=8.8.5.19,9.7.5.14,12.9.9.23,8.8.8.14,144.1.113 In the above example i m considering only 5 ips but there could be many more. Now i have a file which... (15 Replies)
Discussion started by: mohtashims
15 Replies

2. Shell Programming and Scripting

Exclude certain file names while selectingData files coming in different names in a file name called

Data files coming in different names in a file name called process.txt. 1. shipments_yyyymmdd.gz 2 Order_yyyymmdd.gz 3. Invoice_yyyymmdd.gz 4. globalorder_yyyymmdd.gz The process needs to discard all the below files and only process two of the 4 file names available ... (1 Reply)
Discussion started by: dsravanam
1 Replies

3. Shell Programming and Scripting

Data Manipulation on a .csv file

Hallo Friends, I need you help. My file has 5000 or so lines and currently looks like below(sample). Service Type,Origin,Destination,Rate Per Minute,Minimum Charge,Time Based Rate,Time Based From Day,Time Based To Day,Time Based From Time,Time Based To Time,Destination Prefix List,, VoIS... (3 Replies)
Discussion started by: kekanap
3 Replies

4. UNIX for Dummies Questions & Answers

Split a file and give custom names

Hello, I want to split a file based on an input list file that contains the lines each split should have + a corresponding file name. #!/bin/sh # sed -n 'start_line_#,end_line_#p' my_input_file > lines_extracted_output_file while read a b c do sed -n '$a,$bp' myLarge.file > $c.split... (2 Replies)
Discussion started by: Gussifinknottle
2 Replies

5. Shell Programming and Scripting

Manipulation of file data with UNIX

Hello , How all doing today.. I have a little doubt in Unix (6 Replies)
Discussion started by: adisky123
6 Replies

6. Shell Programming and Scripting

How to split a data file into separate files with the file names depending upon a column's value?

Hi, I have a data file xyz.dat similar to the one given below, 2345|98|809||x|969|0 2345|98|809||y|0|537 2345|97|809||x|544|0 2345|97|809||y|0|651 9685|98|809||x|321|0 9685|98|809||y|0|357 9685|98|709||x|687|0 9685|98|709||y|0|234 2315|98|809||x|564|0 2315|98|809||y|0|537... (2 Replies)
Discussion started by: nithins007
2 Replies

7. UNIX for Dummies Questions & Answers

Data file manipulation

Hi, I have two, double column data files (file1 and file2). I want to add the second column of file2 to as 3rd column of file1. But, the 3rd column values corresponds to the values of the 2nd column. example: file1: X Y ========= x1 y2 x3 y4 x2 y4 x5 y3 ========= file2: Y ... (7 Replies)
Discussion started by: gaurab
7 Replies

8. Shell Programming and Scripting

Data manipulation from a file

i have a file in follwing format 0110 1020 1011 1032 1020 2005 2003 1050 i want the output in such a way that all non zero numbers will be converted into 1 like this 0110 1010 1011 1011 1010 1001 1001 1010 (3 Replies)
Discussion started by: vaibhavkorde
3 Replies

9. Shell Programming and Scripting

Data manipulation from one file

HI all i have a file consisting of following numbers 0000 0000 0000 0000 0000 1010 0000 0100 0000 0000 0000 1111 0000 1010 0000 0100 (3 Replies)
Discussion started by: vaibhavkorde
3 Replies

10. UNIX for Dummies Questions & Answers

UNIX - File/Table/Data manipulation

Hi, I have a table (e.g.): a 1 e 4 5 6 b 2 r 4 4 2 c 5 r 3 7 1 d 9 t 4 4 9 . . What I need to do is to set the values of some values in column 2 to negative values. For example, the values 2 and 9 should become -2 and -9 in the modified file. How should I go about... (2 Replies)
Discussion started by: pc2001
2 Replies

Featured Tech Videos