Converts a CSV (tab delimited) file to an Excel xlsx file

csv2xlsx.py

#!/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()

$ 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

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

me too

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

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=",")):

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

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?

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

I've been using this code a lot the past few years — thanks @konrad 🙂 — so I converted it into a proper python project:

• https://github.com/harkabeeparolus/text2excel

Or pipx install text2excel, or even python3 -m pip install --user text2excel if you prefer.

Hey, @harkabeeparolus. I am super happy that this gist was so useful for you that you extended if further! Thanks for the ping!