Add New Column Based on Files Name

UNIX for Beginners Q & A
1

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
A100X054000003393603800001085362365412	1		
A100X054000003393603900001085362365413	1		
A100X054000003393604000001085362365414	1		
A100X054000003393604100001085362365415	1		
A100X054000003393604200001085362365416	1

my expected output

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

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

But the output look like this

>    \pJava Excel API v2.6.12                                                                                          Ba==h\:#8X@"1Arial1Arial1Arial1Arial                               | +  )  ,  *          `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&L009X076000001318173900001082267274106b
!48h%UtBa/N~k   (       Xw            |sanggul3
Ed|sanggul3
|sanggul3

can you fixed it sir
i also upload xls example

thanks

2

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 forums and try to deploy it.