Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Add 1 column based on name of files

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Add 1 column based on name of files
# 1  
Old 09-08-2014
Add 1 column based on name of files
Hi all,

I already transferred list of files from ftp server with files name end with $Y$m$d

Example my files
Code:
data_20140101.xls
data_20140102.xls
data-20140103.xls

and content of each files, for example data_20140101.xls

Code:
USA|16846481|8871374|534909|0|0|0|700|5351981|0|31605445
USA|190457229|54492314|9723236|0|0|0|3500|85095671|0|341308950
USA|41173242|12120063|1370862|0|0|0|547|13140958|0|67805672
USA|8036414|4153916|282212|0|0|0|44|2468889|0|14941475

I need to add 1 column based on format date from files name, so output should like

Code:
20140101|USA|16846481|8871374|534909|0|0|0|700|5351981|0|31605445
20140101|USA|190457229|54492314|9723236|0|0|0|3500|85095671|0|341308950
20140101|USA|41173242|12120063|1370862|0|0|0|547|13140958|0|67805672
20140101|USA|8036414|4153916|282212|0|0|0|44|2468889|0|14941475

and file from data_20140102.xls also created 1 column with 20140102 in first column

thanks
# 2  
Old 09-08-2014
Code:
awk 'FNR == 1 {nm=substr(FILENAME, length(FILENAME)-11, 8)} {print nm "|" $0}' *.xls

This User Gave Thanks to SriniShoo For This Post:
radius
# 3  
Old 09-08-2014
Hello radius,

Following may help you. I am taking an example for 1 file and all file names you can get in a for loop etc and put the same code in it.

Code:
awk '{gsub(/[[:alpha:]]/,X,FILENAME); gsub(/[[:punct:]]/,X,FILENAME);{$1=FILENAME FS $1;}} 1' FS="|" OFS="|" data_20140101.xls

Output will be as follows.

Code:
20140101|USA|16846481|8871374|534909|0|0|0|700|5351981|0|31605445
20140101|USA|190457229|54492314|9723236|0|0|0|3500|85095671|0|341308950
20140101|USA|41173242|12120063|1370862|0|0|0|547|13140958|0|67805672
20140101|USA|8036414|4153916|282212|0|0|0|44|2468889|0|14941475

Use of for loop example as follows. (Though not tested)

Code:
for filename in data_*.xls
do 
awk '{gsub(/[[:alpha:]]/,X,FILENAME); gsub(/[[:punct:]]/,X,FILENAME);{$1=FILENAME FS $1;}} 1' FS="|" OFS="|"  $filename
done

Thanks,
R. Singh
Last edited by RavinderSingh13; 09-08-2014 at 01:57 AM..
# 4  
Old 09-08-2014
Quote:
Originally Posted by RavinderSingh13
Hello radius,

Following may help you. I am taking an example for 1 file and all file names you can get in a for loop etc and put the same code in it.

Code:
awk '{gsub(/[[:alpha:]]/,X,FILENAME); gsub(/[[:punct:]]/,X,FILENAME);{$1=FILENAME FS $1;}} 1' FS="|" OFS="|" data_20140101.xls

Output will be as follows.

Code:
20140101|USA|16846481|8871374|534909|0|0|0|700|5351981|0|31605445
20140101|USA|190457229|54492314|9723236|0|0|0|3500|85095671|0|341308950
20140101|USA|41173242|12120063|1370862|0|0|0|547|13140958|0|67805672
20140101|USA|8036414|4153916|282212|0|0|0|44|2468889|0|14941475

Use of for loop example as follows. (Though not tested)

Code:
for filename in data_*.xls
do 
awk '{gsub(/[[:alpha:]]/,X,FILENAME); gsub(/[[:punct:]]/,X,FILENAME);{$1=FILENAME FS $1;}} 1' FS="|" OFS="|"  $filename
done

Thanks,
R. Singh
If you're going to do it this way and you want the output from all of the input files combined in a single output file, this can be simplified to:
Code:
awk '{gsub(/[[:alpha:][:punct:]]/,X,FILENAME); {$1=FILENAME OFS $1;}} 1' FS="|" OFS="|"  data_*.xls

But, it would still be more efficient to just extract the date from the filename once per file (like SriniShoo's example did) instead of extracting it on every input line.
Last edited by Don Cragun; 09-08-2014 at 03:10 AM.. Reason: Fix typo.
This User Gave Thanks to Don Cragun For This Post:
RavinderSingh13
# 5  
Old 09-08-2014
Mr Srini, can you explain your code in detail ?
Anyway, thanks for the response, it works

thks also for Mr Ravinder
# 6  
Old 09-08-2014
Posted by radius:

Quote:
Mr Srini, can you explain your code in detail ?
Anyway, thanks for the response, it works

thks also for Mr Ravinder
Hello radius,

Here is the explaination for the Srini's code.

Code:
awk '
FNR == 1 ### This condition will be true only 1 time so we need not to take file's date value again and again. Which we are storing in variable nm in next step. ####
 
{nm=substr(FILENAME, length(FILENAME)-11, 8)} #### We are getting the file's date value(which is present in it's name) by using substr utility of awk, (variable name, starting place of the letter from where we want to get the date, how many letters we want to get) then store this value to a variable named nm######
 
{print nm "|" $0} ##### Added the value if nm(which is the date present in each filename) before each line's starting ######
 
' *.xls   #### in each xls file ######

We can use data_*.xls in place of *.xls as *.xls will take every xls file.


Thanks,
R. Singh
# 7  
Old 09-08-2014
Code:
$ awk 'FNR==1{gsub(/.*_|\..*/,"",FILENAME)}{$0 = FILENAME OFS $0}1' OFS='|' *.xls

This User Gave Thanks to Akshay Hegde For This Post:
radius
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Add new column based in condition

At begining of column 2 the same block (2000) have 3 lines, in the next block (2336) it have 9 lines and for block (2524) 3 lines. ... (0 Replies)
Discussion started by: jiam912
0 Replies

2. UNIX for Beginners Questions & Answers

Add New Column Based on Files Name

Dear Sir, I need to add new column (last column) based on files name. my files name 20170809_target_tdc_pmx4.xls 20170809_target_tdc_pmx5.xls 20170809_target_tdc_pmx6.xls for example file : 20170809_target_tdc_pmx4.xls Item code Quantity Unit price Serial number... (1 Reply)
Discussion started by: radius
1 Replies

3. Shell Programming and Scripting

Compare two files based on column

Hi, I have two files roughly 1200 fields in length for each row, sorted on the 2nd field. I need to compare based on that 2nd column between file1 and file2 and print lines that exist in both files into separate files (I can't guarantee that every line in file1 is in file2). Example: File1: ... (1 Reply)
Discussion started by: origon
1 Replies

4. Shell Programming and Scripting

Merge files based on the column value

Hi Friends, I have a file file1.txt 1|ABC|3|jul|dhj 2|NHU|4|kil|eu 3|hjd|34|hfd|43 file2.txt 1||3|KING|dhj 2|NHU||k| 3|hjd|34|hd|43 i want to merge file1.txt file2.txt based on the column null values in file2.txif there are any nulls in column values , (5 Replies)
Discussion started by: i150371485
5 Replies

5. Shell Programming and Scripting

join two files based on one column

Hi All, I am trying to join to files based on one common column. Cat File1 ID HID Ab_1 23 Cd 45 df 22 Vv 33 Cat File2 ID pval Ab_1 0.3 Cd 10 Vv 0.0444 (3 Replies)
Discussion started by: newpro
3 Replies

6. Shell Programming and Scripting

to add special tag to a column based on column condition

Hi All, I have following html code <TR><TD>9</TD><TD>AR_TVR_TBS </TD><TD>85000</TD><TD>39938</TD><TD>54212</TD><TD>46</TD></TR> <TR><TD>10</TD><TD>ASCV_SMY_TBS </TD><TD>69880</TD><TD>33316</TD><TD>45698</TD><TD>47</TD></TR> <TR><TD>11</TD><TD>ARC_TBS ... (9 Replies)
Discussion started by: ckwan
9 Replies

7. Shell Programming and Scripting

merging two files based on first column

I had two files file1 and file2. I want a o/p file(file3) like below using first column as ref. Pls give suggestion ass join is not working as the number of lines in each file is nealry 5 C? file1 --------------------- 404000324810001 Y 404000324810004 N 404000324810008 Y 404000324810009 N... (1 Reply)
Discussion started by: p_sai_ias
1 Replies

8. Shell Programming and Scripting

Matching 2 files based on one column

Hi, On a similar subject, the following. I have two files: file1.txt dbSNP_rsID,Chromosome,Position,Gene rs10399749,chr. 01,45162,? rs4030303,chr. 01,72434,? rs4030300,chr. 01,72515,? rs940550,chr. 01,78032,? rs13328714,chr. 01,81468,? rs11490937,chr. 01,222077,? rs6683466,chr.... (5 Replies)
Discussion started by: swvanderlaan
5 Replies

9. Shell Programming and Scripting

Compare files column to column based on keys

Here is my situation. I need to compare two tab separated files (diff is not useful since there could be known difference between files). I have found similar posts , but not fully matching.I was thinking of writing a shell script using cut and grep and while loop but after going thru posts it... (2 Replies)
Discussion started by: blackjack101
2 Replies

10. Shell Programming and Scripting

Merge Two Files based on First column

Hi, I need to join two files based on first column of both files.If first column of first file matches with the first column of second file, then the lines should be merged together and go for next line to check. It is something like: File one: 110001 abc efd 110002 fgh dfg 110003 ... (10 Replies)
Discussion started by: apjneeraj
10 Replies
Login or Register to Ask a Question