Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Add New Column Based on Files Name

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Add New Column Based on Files Name
# 1  
Old 08-09-2017
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

Code:
Item code	Quantity	Unit price	Serial number
A100X054000003393603800001085362365412	1		
A100X054000003393603900001085362365413	1		
A100X054000003393604000001085362365414	1		
A100X054000003393604100001085362365415	1		
A100X054000003393604200001085362365416	1


my expected output
Code:
A100X054000003393603800001085362365412|20170809_target_tdc_pmx4
A100X054000003393603900001085362365413|20170809_target_tdc_pmx4
A100X054000003393604000001085362365414|20170809_target_tdc_pmx4
A100X054000003393604100001085362365415|20170809_target_tdc_pmx4
A100X05400000339360420000108536236541620170809_target_tdc_pmx4

I did this
Code:
awk 'FNR == 1 {nm=substr(FILENAME, length(FILENAME)-11, 8)} {print $0"|"nm}' *.xls

But the output look like this
Code:
▒▒ࡱ▒>▒▒ ▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒   ▒▒▒▒▒▒\pJava Excel API v2.6.12                                                                                          B▒a=▒▒▒=h\:▒#8X@▒"▒▒1▒▒Arial1▒▒Arial1▒▒Arial1▒▒Arial▒▒▒ ▒▒ ▒▒▒ ▒▒ ▒▒▒ ▒▒ ▒▒▒ ▒▒ ▒▒▒ ▒▒ ▒▒▒ ▒▒ ▒▒▒ ▒▒ ▒▒▒ ▒▒ ▒▒▒ ▒▒ ▒▒▒ ▒▒ ▒▒▒ ▒▒ ▒▒▒ ▒▒ ▒▒▒ ▒▒ ▒▒▒ ▒▒ ▒▒▒ ▒▒ ▒ |▒ ▒+▒▒ ▒▒ ▒)▒▒ ▒▒ ▒,▒▒ ▒▒ ▒*▒▒ ▒▒ ▒      ▒▒ ▒▒ ▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒`▒▒Arkusz1▒,▒▒
,,      Item codQuantity|sanggul3
Serial number&L009X076000001318172900001082267274096&L009X076000001318173000001082267274097&L009X076000001318173100001082267274098&L009X076000001318173200001082267274099&L009X076000001318173300001082267274100&L009X076000001318173400001082267274101&L009X076000001318173500001082267274102&L009X076000001318173600001082267274103&L009X076000001318173700001082267274104&L009X076000001318173800001082267274105&L009X076000001318171400001082267274078&L009X076000001318171500001082267274080&L009X076000001318171600001082267274081&L009X076000001318172000001082267274085&L009X076000001318171900001082267274084&L009X076000001318171800001082267274083&L009X076000001318171700001082267274082&L009X076000001318172200001082267274088&L009X076000001318172100001082267274087&L009X076000001318172300001082267274089&L009X076000001318172400001082267274090&L009X076000001318172500001082267274092&L009X076000001318172600001082267274093&L009X076000001318172700001082267274094&L009X076000001318172800001082267274095&L009X076000001318171300001082267274077&L009X076000001318171200001082267274076&L009X076000001318171100001082267274075&L009X076000001318171000001082267274074&L009X076000001318170900001082267274072&L009X076000001318170800001082267274071&L009X076000001318170700001082267274070&L009X076000001318170600001082267274068&L009X076000001318170500001082267274067&L009X076000001318170400001082267274066&L009X076000001318170300001082267274065&L009X076000001318170200001082267274063&L009X076000001318170100001082267274062&L009X076000001318170000001082267274060&L009X076000001318173900001082267274106▒b▒
▒!48h▒▒▒%Ut▒▒▒Ba▒▒▒▒/N~▒▒▒k▒▒   (       Xw      ▒▒      ▒|sanggul3
Ed|sanggul3
▒▒|sanggul3
▒

can you fixed it sir
i also upload xls example

thanks
20170809_target_tdc_pmx4.xls (22.0 KB) 
# 2  
Old 08-09-2017
Those seem to be real (binary) EXCEL spread sheets; you can't edit those with *nix text tools. Either save them as text (.csv) files, modify and read back in as a text files again, or search for a perl "EXCEL" module in these fora and try to deploy it.
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 Dummies Questions & Answers

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 data_20140101.xls data_20140102.xls data-20140103.xls and content of each files, for example data_20140101.xls USA|16846481|8871374|534909|0|0|0|700|5351981|0|31605445... (6 Replies)
Discussion started by: radius
6 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