Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to save an Excel worksheet as CSV

Tags:

python

csv

excel

I want to write a Python script that reads in an Excel spreadsheet and saves some of its worksheets as CSV files.

How can I do this?

I have found third-party modules for reading and writing Excel files from Python, but as far as I can tell, they can only save files in Excel (i.e. *.xls) format. If I'm wrong here, some example code showing how to do what I'm trying to do with these modules would be appreciated.

I also came across one solution that I can't quite understand, but seems to be Windows-specific, and therefore would not help me anyway, since I want to do this in Unix. At any rate, it's not clear to me that this solution can be extended to do what I want to do, even under Windows.

like image 663
kjo Avatar asked May 29 '12 15:05

kjo


People also ask

Can I convert an XLSX to CSV?

How to convert a XLSX to a CSV file? Choose the XLSX file that you want to convert. Select CSV as the the format you want to convert your XLSX file to. Click "Convert" to convert your XLSX file.


2 Answers

The most basic examples using the two libraries described line by line:

  1. Open the xls workbook
  2. Reference the first spreadsheet
  3. Open in binary write the target csv file
  4. Create the default csv writer object
  5. Loop over all the rows of the first spreadsheet
  6. Dump the rows into the csv

import xlrd import csv  with xlrd.open_workbook('a_file.xls') as wb:     sh = wb.sheet_by_index(0)  # or wb.sheet_by_name('name_of_the_sheet_here')     with open('a_file.csv', 'wb') as f:   # open('a_file.csv', 'w', newline="") for python 3         c = csv.writer(f)         for r in range(sh.nrows):             c.writerow(sh.row_values(r)) 

import openpyxl import csv  wb = openpyxl.load_workbook('test.xlsx') sh = wb.active with open('test.csv', 'wb') as f:  # open('test.csv', 'w', newline="") for python 3     c = csv.writer(f)     for r in sh.rows:         c.writerow([cell.value for cell in r]) 
like image 197
Zeugma Avatar answered Sep 20 '22 17:09

Zeugma


Using pandas will be a bit shorter:

import pandas as pd  df = pd.read_excel('my_file', sheetname='my_sheet_name')  # sheetname is optional df.to_csv('output_file_name', index=False)  # index=False prevents pandas to write row index  # oneliner pd.read_excel('my_file', sheetname='my_sheet_name').to_csv('output_file_name', index=False) 
like image 35
FabienP Avatar answered Sep 21 '22 17:09

FabienP