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 '17 at 14:23
  • 1
    @glennjackman this is perfectly on topic here, just as it would be on Unix & Linux.
    – terdon
    Nov 6 '17 at 16:07
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 '17 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 '17 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 '17 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 '17 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 '17 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 '17 at 7:23
  • @ChintanSheth my bad, I'd forgotten the redirection would be outside xargs. Corrected, not as elegant now.
    – muru
    Nov 8 '17 at 8:01
  • xargs and > is evil :-P. That's why I prefer another loop, it's less error prone.
    – pLumo
    Nov 8 '17 at 8:01
  • @RoVo I'd have usually gone for another loop too, just wanted to show another method here.
    – muru
    Nov 8 '17 at 8:03
  • This works now, however slightly slower than @RoVo answer.
    – csheth
    Nov 8 '17 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.