Converting all worksheets in an Excel workbook to csv format

Question

My Excel document my.xlsx has two Sheets named Sheet1 and Sheet2. I want to convert all worksheets to csv format using xlsx2csv. I used the following commands:

from xlsx2csv import *
xlsx2csv my.xlsx convert.csv
File "<stdin>", line 1
    xlsx2csv my.xlsx convert.csv
              ^
SyntaxError: invalid syntax

x2c -a my.xlsx my1.csv
  File "<stdin>", line 1
    x2c -a my.xlsx my1.csv
            ^
SyntaxError: invalid syntax

Any help, please.

score 2 · Accepted Answer · answered Jun 12 '19 at 01:45

2

I have not used xlsx2csv before but why don't we try pandas.

Your requirement can be solved like this:

import pandas as pd
for sheet in ['Sheet1', 'Sheet2']:
    df = pd.read_excel('my.xlsx', sheetname=sheet)
    df.to_csv(sheet + '_output.csv', index=False)

answered Jun 12 '19 at 01:45

knl

969
11
35

Thanks @Kelvin for your answer. Would highly appreciate if you give the solution which is not using the names of the sheets. B/c my xlsx is very large which is not easy to open using Excel so do not know the names of sheets at hand. Thanks – MYaseen208 Jun 12 '19 at 01:52
1

@MYaseen208 yes, absolutely. You can do something like this: `my_file = pd.ExcelFile('my.xlsx')` and after that just loop through all the sheets: `for sheet in my_file.sheet_names: df = pd.read_excel('my.xlsx', sheetname=sheet)` – knl Jun 13 '19 at 02:43
1

because pd.read_excel is extremely slow. – xjtan Jan 12 '22 at 08:25
Seems to be since 2018 _sheet_name_ in `pd.read_excel('my.xlsx', sheet_name=sheet)`. See [typeerror-with-pandas-read-excel](https://stackoverflow.com/questions/57348149/typeerror-with-pandas-read-excel). – jgran Feb 03 '22 at 16:07

caot · Answer 2 · 2019-06-12T02:28:49.900

1

You can do something as the follows:

import pandas as pd

xls_file = pd.ExcelFile('<path_to_your_excel_file>')
sheet_names = xls_file.sheet_names

for sheet in sheet_names:
    df = xls_file.parse(sheet)

edited Jun 12 '19 at 02:28

answered Jun 12 '19 at 02:18

caot

3,066
35
37

score 0 · Answer 3 · answered Aug 22 '20 at 23:55

Xlsx2csv python implementation:
Could only execute Xlsx2csv with sheetid parameter. In order to get sheet names and ids, get_sheet_details was used.
csvfrmxlsx creates csv files for each sheet in csv folder under parent directory.

import pandas as pd
from pathlib import Path


def get_sheet_details(filename):
    import os
    import xmltodict
    import shutil
    import zipfile
    sheets = []
    # Make a temporary directory with the file name
    directory_to_extract_to = (filename.with_suffix(''))
    os.mkdir(directory_to_extract_to)
    # Extract the xlsx file as it is just a zip file
    zip_ref = zipfile.ZipFile(filename, 'r')
    zip_ref.extractall(directory_to_extract_to)
    zip_ref.close()
    # Open the workbook.xml which is very light and only has meta data, get sheets from it
    path_to_workbook = directory_to_extract_to / 'xl' / 'workbook.xml'
    with open(path_to_workbook, 'r') as f:
        xml = f.read()
        dictionary = xmltodict.parse(xml)
        for sheet in dictionary['workbook']['sheets']['sheet']:
            sheet_details = {
                'id': sheet['@sheetId'],  # can be sheetId for some versions
                'name': sheet['@name']  # can be name
            }
            sheets.append(sheet_details)
    # Delete the extracted files directory
    shutil.rmtree(directory_to_extract_to)
    return sheets


def csvfrmxlsx(xlsxfl, df):  # create csv files in csv folder on parent directory
    from xlsx2csv import Xlsx2csv
    for index, row in df.iterrows():  
        shnum = row['id']
        shnph = xlsxfl.parent / 'csv' / Path(row['name'] + '.csv')  # path for converted csv file
        Xlsx2csv(str(xlsxfl), outputencoding="utf-8").convert(str(shnph), sheetid=int(shnum))  
    return


pthfnc = 'c:/xlsx/'
wrkfl = 'my.xlsx'
xls_file = Path(pthfnc + wrkfl)
sheetsdic = get_sheet_details(xls_file)  # dictionary with sheet names and ids without opening xlsx file
df = pd.DataFrame.from_dict(sheetsdic)
csvfrmxlsx(xls_file, df)  # df with sheets to be converted

Converting all worksheets in an Excel workbook to csv format

3 Answers3