how to compare text file datas with excel sheet values

Question

For Example :

in my username.txt file

ravi
peri
perl
phython

in my .xls file i have two columns named as user name and email

username                      email
ravi                          ravi@gmail.com
peri                          peri@gmail.com

I want to produce the output in list order like excel view or normal list view .Please help me to solve this issue .

what have you tried? is your excel file in .xls format or have you exported it to CSV? — cas, Aug 8 '17 at 6:18
i tried to convert .xls to .csv by following this step $ ssconvert Book1.xlsx newfile.csv but doesn't work to me — Ravi, Aug 8 '17 at 7:22
wget pypi.python.org/packages/4c/56/… --2017-08-08 06:13:35-- pypi.python.org/packages/4c/56/… Resolving pypi.python.org... 151.101.8.223, 2a04:4e42:14::223 Connecting to pypi.python.org|151.101.8.223|:443... failed: Connection timed out. Connecting to pypi.python.org|2a04:4e42:14::223|:443... failed: Network is unreachable. — Ravi, Aug 8 '17 at 7:24

αғsнιη · Accepted Answer · 2017-08-08 06:57:43Z

first export your data.xls file into a .csv file format then use either grep, awk, in bash or any other commands or scripting. I'm giving you both grep and awk approaches.

awk 'NR==FNR{usernames[$0]++;next } ($1 in usernames)' username.txt data.csv

or using grep:

grep -Fwf username.txt data.csv

later you can import the result to .xls file format giving instructions above in link.

1 Answer 1