10

I have a *.xlsm file which has 20 sheets in it. I want to save few sheets as *.csv (formatting loss is fine) individually. Already tried xlrd-xlwt and win32com libraries but could not get through. Can anybody please provide a code snippet which does the above processing in Python? I have other python dependencies so no other language would work. Thanks

iPirate
  • 113
  • 1
  • 1
  • 6
  • As far as I remember, all recent Microsoft Office formats were XML packed into Zip container. So probably you could take a look at the .xlsm file sources, clarify its XML structure and then extract required fields. – user3159253 May 09 '14 at 00:43

3 Answers3

6

xlrd should work fine on xlsm files as well. I tested the code with a random xlsm file, and it worked perfectly.

import csv
import xlrd

workbook = xlrd.open_workbook('test.xlsx')
for sheet in workbook.sheets():
    with open('{}.csv'.format(sheet.name), 'wb') as f:
        writer = csv.writer(f)
        writer.writerows(sheet.row_values(row) for row in range(sheet.nrows))

If you've encoding issues, try the code below:

import csv
import xlrd

workbook = xlrd.open_workbook('test.xlsm')
for sheet in workbook.sheets():
    if sheet.name == "Sheet_name_from_xlsm_file":
        with open('{}.csv'.format(sheet.name), 'wb') as f:
            writer = csv.writer(f)
            for row in range(sheet.nrows):
                out = []
                for cell in sheet.row_values(row):
                    try:
                        out.append(cell.encode('utf8'))
                    except:
                        out.append(cell)
                writer.writerow(out)
Ali Sayani
  • 33
  • 7
Steinar Lima
  • 7,644
  • 2
  • 39
  • 40
  • 1
    If this doesn't work, you should upload your data file if possible. – Steinar Lima May 09 '14 at 01:16
  • Thanks for the snippet! I am getting this error: writer.writerows(sheet.row_values(row) for row in range(sheet.nrows)) UnicodeEncodeError: 'ascii' codec can't encode character u'\xb5' in position 0: ordinal not in range(128) Is there a way to specify encoding here? Sorry, can't upload the file here. – iPirate May 09 '14 at 01:32
  • @iPirate I think you need to encode the strings with utf8 or something similar. Try cp1252 if it doesn't work. I've updated my answer. – Steinar Lima May 09 '14 at 01:42
  • 1
    I tried to use this code to convert an .xlsm file to csv and nothing happens - no errors and it doesn't write anything out that I can see.. what could be happening. I also dont' understand where the csv is getting written out to in the above code – kflaw Jan 06 '15 at 21:18
  • @kflaw: The output is saved in _.csv_ (without the brackets obviously). This file will be located the place you run your script from. If you have any further questions, I advise you to open a new question here at SO, with as much context provided as possible. You can link to your new question here if you want to. – Steinar Lima Jan 06 '15 at 23:07
  • 1
    Please keep in mind, that from the current xlrd-version onwards (2.0+) explicitly only "xls" is allowed as a file format. So for opening "xlsm"-files you have to switch to the much better openpyxl module. – CheradenineZK Feb 25 '21 at 09:47
4

You can do this easily with pandas

  1. Install pandas and xlrd dependencies by following

    • pip3 install pandas
    • pip3 install xlrd (required by pandas)

  2. Now simply read xlsm file using read_excel. Here is a demo:-

import pandas as pd

# YOU MUST PUT sheet_name=None TO READ ALL CSV FILES IN YOUR XLSM FILE
df = pd.read_excel('YourFile.xlsm', sheet_name=None)

# prints all sheets
print(df)

# prints all sheets name in an ordered dictionary
print(df.keys())

# prints first sheet name or any sheet if you know it's index
first_sheet_name = list(df.keys())[0]
print(first_sheet_name)

# prints first sheet or any sheet if know it's name
print(df[first_sheet_name])

# export first sheet to file
df[first_sheet_name].to_csv('FirstSheet.csv')

# export all sheets 
for sheet_name in list(df.keys()):
   df[sheet_name].to_csv(sheet_name + 'Sheet.csv')


# USE IT IN MULTIPLE WAYS #
bikram
  • 7,127
  • 2
  • 51
  • 63
3
import pandas as pd

import xlrd

import openpyxl #required for xlrd 2.0.1 and higher

df = pd.read_excel('your_excel_file_name.xlsm', sheet_name='your_sheet_name')
df.to_csv('your_new_name.csv')
buddemat
  • 4,552
  • 14
  • 29
  • 49
Marceloi
  • 31
  • 1