10

I need to convert all sheets of a single .xls/.xlsx file to a .csv. This will be done on all .xls files in all directories and sub-directories (recursively).

Step 1: Get the sheetnames of all .xls into a .csv using:

for file in $(find . -name '*.xls' -o -name '*.xlsx');do in2csv -n "$file" > ${file%.xls}-sheetnames-list.csv; done

filename-sheetnames-list.csv can act as a list:

sheetname1
sheetname2
sheetname3

Step 2 : The code for converting a specific sheet into a .csv using in2csv is:

in2csv --sheet "SHEETNAME" filename.xls > filename-SHEETNAME.csv

How can I get every sheetname in a .xls/x and write every sheet separately for all directories containing a .xls/x ?

in2csv --write-sheets "-" filename.xls > filename-sheet1.csv filename-sheet2.csv .... gives output only on sheet1.csv, not sure how to get all sheets from this.

2
  • 2
    Why not just find every .xls{,x} and loop over every sheet using -exec?
    – dessert
    Nov 6, 2017 at 14:23
  • 1
    @glennjackman this is perfectly on topic here, just as it would be on Unix & Linux.
    – terdon
    Nov 6, 2017 at 16:07

4 Answers 4

10

You can just put a loop inside another loop.

To avoid errors, don't use for with find results.

while IFS= read -r file; do
    while IFS= read -r sheet; do
        in2csv --sheet "$sheet" "$file" > "${file%.*}-${sheet}.csv"
    done < <(in2csv -n "$file")
done < <(find . -name '*.xls' -o -name '*.xlsx')
5
  • @muru ah crap. You're absolutely right. I'd tested in an environment where the IFS had already been changed so of course it propagated downwards. Idiot. Thanks, edit reverted.
    – terdon
    Nov 7, 2017 at 9:01
  • @RoVo the first option works fine. The second one however gives me no output or error. I am unsure why; for a single .xls in2csv --write-sheets "-" filename.xls > sheetname.csv gives only the first sheet. I don't know what additional info to add to write all sheets. That shall give us clues to correct your code.
    – csheth
    Nov 8, 2017 at 7:37
  • 1
    did you update to that version 1.0.2 ? pip install csvkit -U. I think the way it works is not what you like, with the simple skript from 1st option you have more ways to control the output and the filenames etc.
    – pLumo
    Nov 8, 2017 at 8:02
  • still doesn't work with the update, and yes I'd prefer using a list than --write-sheets Maybe you can set this alternative option as another answer... I will accept the first option as the answer then. Thanks @RoVo
    – csheth
    Nov 8, 2017 at 8:24
  • 1
    Maybe generally a good idea to have alternative options in another answer. Thanks, glad that I could help.
    – pLumo
    Nov 8, 2017 at 8:55
7

Skipping find and using bash:

shopt -s globstar  # enable recursive globbing
for f in **/*.xls{,x}  # for files ending in .xls or .xlsx
do
    in2csv -n "$f" |   # get the sheetnames
      xargs -I {} bash -c 'in2csv --sheet "$2" "$1" > "${1%.*}"-"$2".csv' _ "$f" {} # {} will be replaced with the sheetname
done
5
  • this script looks elegant but its output is filename-{}.csv containing no data. I'm a novice and can't seem to find the error by editing the script and reading up. Some help?
    – csheth
    Nov 8, 2017 at 7:23
  • @ChintanSheth my bad, I'd forgotten the redirection would be outside xargs. Corrected, not as elegant now.
    – muru
    Nov 8, 2017 at 8:01
  • xargs and > is evil :-P. That's why I prefer another loop, it's less error prone.
    – pLumo
    Nov 8, 2017 at 8:01
  • @RoVo I'd have usually gone for another loop too, just wanted to show another method here.
    – muru
    Nov 8, 2017 at 8:03
  • This works now, however slightly slower than @RoVo answer.
    – csheth
    Nov 8, 2017 at 8:25
3

csvkit version > 1.0.2 has a builtin function to write all sheets:

--write-sheets: WRITE_SHEETS
                      The names of the Excel sheets to write to files, or
                      "-" to write all sheets.

So you could try the following:

find . -name '*.xls' -o -name '*.xlsx' -exec in2csv --write-sheets "-" {} \;

Note:

This seems not to work 100% as expected. But worth a try and as this is the first version with that option maybe in future versions the implementation is better/easier.

0

Use Gnumeric:

ssconvert -S filename.xlsx filename.csv

to get one csv file per sheet.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .