-
-
Save konrad/4154786 to your computer and use it in GitHub Desktop.
| #!/usr/bin/env python | |
| """ | |
| FUNCTION: Converts a CSV (tab delimited) file to an Excel xlsx file. | |
| Copyright (c) 2016, Konrad Foerstner <[email protected]> | |
| 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() |
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:
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!
Ah, sudo easy_install openpyxl installs the packages, but now I get: