Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to Populate field in File with it's manipulated Filename?
# 1  
Old 12-19-2012
Hammer & Screwdriver How to Populate field in File with it's manipulated Filename?
Hi All,

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

HTML Code:
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)

HTML Code:
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.Smilie
# 2  
Old 12-19-2012
Try

Code:
$ 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"

Code:
$ 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  
Old 12-19-2012
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  
Old 12-19-2012
try:
Code:
awk '/[.]TXT/{sub("[.]TXT","");sub(".*_","");f=$0}/,..*,/{gsub("\",\"","|");sub("^\"","");sub("\"$","");$1=$1 ; print $0,f}' OFS="|" in

# 5  
Old 12-19-2012
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.

Code:
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  
Old 12-19-2012
Genius Stuff Pamu. Smilie really solves my issue..thanks all.. for your effort.Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Linux

How do I format a Date field of a .CSV file with multiple commas in a string field?

I have a .CSV file (file.csv) whose data are all enclosed in double quotes. Sample format of the file is as below: column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10 "12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in... (3 Replies)
Discussion started by: dhruuv369
3 Replies

2. Shell Programming and Scripting

Shell script to populate an array from a csv file

Hi Guys, I've got a script that runs and collects statistics from one of our machines and outputs the data into a csv file. Now, that script runs fine and I managed to create another one (with a lot of help from this forum!!) to trim the csv file down to all the data that I need, rather than all... (9 Replies)
Discussion started by: jimbob01
9 Replies

3. Shell Programming and Scripting

how to get filename along with its first field

hi, i have some 37 files having a file name pattern 'stg*' which are tilde delimited files. the requirement is i need to fetch two columns field1:filename field2:first field of each file.... for example if i have a file like stg_2345.txt having a content 1~2~3 4~5~6 another file... (3 Replies)
Discussion started by: angel12345
3 Replies

4. Shell Programming and Scripting

Append 1st field from a file into 2nd field of another file

Hi, I've internally searched through forums for about 2+ hours. Unfortunately, with no luck. Although I've found some cases close to mine below, but didn't help so much. Actually, I'm in short with time. So I had to post my case. Hoping that you can help. I have 2 files, FILE1 ... (1 Reply)
Discussion started by: amurib
1 Replies

5. Shell Programming and Scripting

Appending 1st field in a file into 2nd field in another file

Hi, I've internally searched through forums for about 2+ hours. Unfortunately, with no luck. Although I've found some cases close to mine below, but didn't help so much. Actually, I'm in short with time. So I had to post my case. Hoping that you can help. I have 2 files, FILE1 ... (0 Replies)
Discussion started by: amurib
0 Replies

6. Shell Programming and Scripting

Unable to populate subject field of the email while using sendmail

Hi, Can anyone kindly provide some information about how to populate the subject field of the email while using the sendmail utility ? Itried the following command line argument : echo -e "Body of the email" | /usr/lib/sendmail -f from@from.com -t to@to.com -s " Subject of the email" ... (4 Replies)
Discussion started by: sdiptanil
4 Replies

7. Shell Programming and Scripting

Filename from splitting files to have the same filename of the original file with counter value

Hi all, I have a list of xml file. I need to split the files to a different files when see the <ko> tag. The list of filename are B20090908.1100-20090908.1200_CDMA=1,NO=2,SITE=3.xml B20090908.1200-20090908.1300_CDMA=1,NO=2,SITE=3.xml B20090908.1300-20090908.1400_CDMA=1,NO=2,SITE=3.xml ... (3 Replies)
Discussion started by: natalie23
3 Replies

8. Shell Programming and Scripting

shellscript.query Oracle table..populate in a text file

Hi Guys, I'm new to this forum as well as to UNIX shell scripting. I'm looking for a shellscript to query an Oracle database table and populate the result set of the query in a text file. Could you someone help me out with a sample code? Thanks, Bhagat (7 Replies)
Discussion started by: bhagat.singh-j
7 Replies

9. Shell Programming and Scripting

how to include field in the output filename of awk

Im using awk and I want the output filename to contain the first field of the input file. Ex. 1 dddd wwwww 1 eeeee wwww 1 wwww eerrrr 2 eeee eeeeee I want the output files to be xxx1 and xxx2 Thank you (4 Replies)
Discussion started by: yahyaaa
4 Replies

10. Shell Programming and Scripting

using the getline to populate an array from a file

Hello, I am trying to use the awk getline command to populate an array from a flat file. Has anyone done this before? So we have file1: a b c d I want to create an array like index that contains these four elements (8 Replies)
Discussion started by: penfold
8 Replies
Login or Register to Ask a Question