Member Avatar for gishi

hi!

does anyone know how to convert excel file (xls and xlsx)(or even just xls) into csv preferably using xlrd and csv modules. i need to convert a specific excel sheet to csv. i need to create a csv file instead of using comma but semicolon. i am still not familiar with python and i really need to convert files. hope someone can help me.

thanks in advance!

Edited by gishi because: n/a
  • 2 Contributors
  • 6 Replies
  • 653 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by gishi

Dani AI

Generated

Short answer for : if your files are XLS (Excel 97–2003) use xlrd to read and Python’s csv module to write a semicolon-separated file. If the files are XLSX (Excel 2007+), use openpyxl (or pandas with engine="openpyxl") — modern xlrd releases only handle the old .xls binary format. As noted, there are other tools, but the examples below are small, dependable, and work in current Python environments. See the xlrd README and the pandas/openpyxl docs for background. (github.com)

Minimal, practical example (XLS -> CSV, semicolon, Python 3):

import xlrd
import csv

def xls_to_csv(xls_path, csv_path, sheet_name=None, delimiter=';'):
    book = xlrd.open_workbook(xls_path)
    sh = book.sheet_by_name(sheet_name) if sheet_name else book.sheet_by_index(0)

    with open(csv_path, 'w', newline='', encoding='utf-8') as fout:
        writer = csv.writer(fout, delimiter=delimiter, quotechar='"', quoting=csv.QUOTE_MINIMAL)
        for rx in range(sh.nrows):
            writer.writerow(sh.row_values(rx))

When writing CSV in Python 3 always open the output with newline='' and specify encoding so newlines and Unicode are handled correctly. See the csv module docs for the recommended usage. (docs.python.org)

If you need XLSX support use openpyxl (read-only mode + values_only is handy):

from openpyxl import load_workbook
import csv

def xlsx_to_csv(xlsx_path, csv_path, sheet_name=None, delimiter=';'):
    wb = load_workbook(xlsx_path, data_only=True, read_only=True)
    ws = wb[sheet_name] if sheet_name else wb.active

    with open(csv_path, 'w', newline='', encoding='utf-8') as fout:
        writer = csv.writer(fout, delimiter=delimiter)
        for row in ws.iter_rows(values_only=True):
            writer.writerow(['' if v is None else v for v in row])

Troubleshooting notes: the ZipImportError: bad local file header you saw importing pyExcelerator usually means a corrupted .egg file — remove that egg from site-packages and reinstall with pip/easy_install (or prefer modern packages). If you’re on ancient Python (your trace shows Python 2.4), many libraries no longer support it; upgrading to a maintained Python 3 release will save a lot of friction. If a quick workaround is needed, pandas.read_excel(..., engine='openpyxl') can read a named sheet and DataFrame.to_csv(sep=';') will produce the semicolon CSV. (openpyxl.pages.heptapod.net)

Recommended Answers

All 6 Replies

Member Avatar for Beat_Slayer

You have the pyexcelerator module, with a conversion script in the examples folder.

Happy coding.

Member Avatar for gishi

hi!I already tried the codes but i didn't work though. do you have other suggestions or codes so that i can do this? thanks!

Member Avatar for Beat_Slayer

Does pyexcelerator doesn't work?

Wich system you are running, and can you post a sample xls?

Wich error message the converter from pyexcelerator gives?

Member Avatar for gishi

This is the traceback when I import pyExcelerator. I have installed it again and still does not work:

Traceback (most recent call last):
File "<interactive input>", line 1, in ?
ZipImportError: bad local file header in C:\Python24\lib\site-packages\pyexcelerator-0.6.4.1-py2.4.egg

Do you have a code that use xlrd module instead of pyexcelerator?

thanks

Edited by gishi because: incomplete post
Member Avatar for gishi

in addition, this should read or open excel files created in microsoft excel

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.