extract the rows from multiple excel files and print the output as the same name of input files - Shell script

Question

[Baka]

Hello,

I have 336 excel files and each excel file has 25 columns. I have to extract the rows from column 17 which do not have “efflux pump” (for example: extract the other rows with full entries which do not have efflux pump) and print the output as the same name of input files.

Kindly help regarding this. Thank you.

EDIT:

Below is example data from 1 file:

I have to extract kdpDE, pmr phosphoethanolamine transferase and its corresponding data and excludes entries which have efflux pump from column 17 (Q in the excel sheet).

Answer 1

[shenwei356]

Try csvtk which has some subcommands that can be piped to accomplish the operations you want.

• xlsx2csv, convert XLSX to CSV format
• grep, grep data by selected fields with patterns/regular expressions
• cut, select and arrange fields
• csv2xlsx, convert CSV/TSV files to XLSX file

For batch proccessing, use GNU parallel, rush or other tools.

Answer 2

[cpad0112]

Please do not delete post once you got suggestions. Here is another solution:

Please follow the instructions:

1. Create a directory test
2. Copy 5 excel files (not move) to the test directory
3. Save following bash script in a file called 'script.sh' and place it inside test directory
4. Make the script executable ( chmod +x script.sh) and execute the script (./script.sh) in test directory.
5. A directory by name new will be created. Within new directory, 5 xls files will be present.
6. Carefully check the output and see if it is as per your requirements.
7. Run it on all excel files. Take a back up all xls files before you execute the script
8. To run the script, you would need libreoffice (calc) installed on your machine.

Script:

#! /usr/bin/env bash

mkdir -p new
for i in *.xls;
    do libreoffice --headless --convert-to csv $i --outdir new ;
    awk -F "," '$17 !~ /efflux pump/ {print $17}' new/${i%.*}.csv  > ${i%.*}.csv ;
    libreoffice --headless --convert-to xls ${i%.*}.csv  --outdir new ;
    rm ${i%.*}.csv;
    rm new/${i%.*}.csv;
done

[Baka]

Thank you very much @cpad0112