Skip to content

Instantly share code, notes, and snippets.

@konrad konrad/csv2xlsx.py
Last active Sep 28, 2019

Embed
What would you like to do?
Converts a CSV (tab delimited) file to an Excel xlsx file
#!/usr/bin/env python
"""
FUNCTION: Converts a CSV (tab delimited) file to an Excel xlsx file.
Copyright (c) 2016, Konrad Foerstner <konrad@foerstner.org>
Permission to use, copy, modify, and/or distribute this software for
any purpose with or without fee is hereby granted, provided that the
above copyright notice and this permission notice appear in all
copies.
THE SOFTWARE IS PROVIDED 'AS IS' AND THE AUTHOR DISCLAIMS ALL
WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE
AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL
DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR
PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER
TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR
PERFORMANCE OF THIS SOFTWARE.
"""
import argparse
import csv
import sys
from openpyxl import Workbook
def main():
parser = argparse.ArgumentParser()
parser.add_argument("input_file")
args = parser.parse_args()
if not args.input_file.endswith(".csv"):
sys.stderr.write("Error: File does not have the ending \".csv\".\n")
sys.exit(2)
wb = Workbook()
worksheet = wb.active
for row in csv.reader(open(args.input_file), delimiter="\t"):
worksheet.append([_convert_to_number(cell) for cell in row])
wb.save(args.input_file.replace(".csv", ".xlsx"))
def _convert_to_number(cell):
if cell.isnumeric():
return int(cell)
try:
return float(cell)
except ValueError:
return cell
main()
@jnthnclrk

This comment has been minimized.

Copy link

commented Jul 2, 2014

$ python csv2xlsx.py campaign-summary.csv
Traceback (most recent call last):
  File "csv2xlsx.py", line 26, in <module>
    from openpyxl.workbook import Workbook
ImportError: No module named openpyxl.workbook
@jnthnclrk

This comment has been minimized.

Copy link

commented Jul 2, 2014

Ah, sudo easy_install openpyxl installs the packages, but now I get:

$ python csv2xlsx.py campaign-summary.csv
Traceback (most recent call last):
  File "csv2xlsx.py", line 43, in <module>
    sheet.cell(row = row_index, column = col_index).value = col
  File "/Library/Python/2.7/site-packages/openpyxl-2.0.4-py2.7.egg/openpyxl/worksheet/worksheet.py", line 284, in cell
    coordinate = '%s%s' % (get_column_letter(column), row)
  File "/Library/Python/2.7/site-packages/openpyxl-2.0.4-py2.7.egg/openpyxl/compat/functools.py", line 122, in wrapper
    result = user_function(*args, **kwds)
  File "/Library/Python/2.7/site-packages/openpyxl-2.0.4-py2.7.egg/openpyxl/cell/cell.py", line 109, in get_column_letter
    raise ValueError("Invalid column index {0}".format(col_idx))
ValueError: Invalid column index 0
@NacreData

This comment has been minimized.

Copy link

commented Aug 26, 2014

me too

@flutesa

This comment has been minimized.

Copy link

commented Dec 8, 2014

fix this problem with sudo pip install openpyxl==1.8.6
See at choderalab/host-guest#1 (comment)

@miguelmorales85

This comment has been minimized.

Copy link

commented Jun 29, 2016

I am trying to convert a .csv file with several rows into an Excell xlsx file.

I am executing
python csv2xlsx.py C:\Python27\test\data\prueba2.csv
But the following message is shown

Traceback (most recent call last):
  File "csv2xlsx.py", line 21, in <module>
    sheet.cell(row = row_index, column = col_index).value = col
  File "C:\Python27\lib\site-packages\openpyxl\worksheet\worksheet.py", line 301, in cell
    raise ValueError("Row or column values must be at least 1")
ValueError: Row or column values must be at least 1

My csv is delimited by commas. I guess I should replace in the code
csv.reader(open(args.input_file), delimiter="\t")):
for
csv.reader(open(args.input_file), delimiter=",")):

@konrad

This comment has been minimized.

Copy link
Owner Author

commented Sep 6, 2016

Updated this gist as is did not work with the current version of openpyxl.

@Jancs-E

This comment has been minimized.

Copy link

commented Sep 2, 2017

For script to work correctly, such change is necessary
def _convert_to_number(cell):
cell = unicode (cell, "utf-8")

otherwise isnumber() fails.

one more problem - despite setting numeric format, the resulting file anyway treats all values as text (opened with LoCalc).
Regional settings influence?

@Jancs-E

This comment has been minimized.

Copy link

commented Sep 2, 2017

the function dealing with "." and ","problems looks so:
def _convert_to_number(cell):
cell.replace(".",",")
cell = unicode (cell, "utf-8")
if cell.isnumeric():
return int(cell)
try:
return float(cell)
except ValueError:
return cell

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.