71

In this post there is a Python example to convert from csv to xls.

However, my file has more than 65536 rows so xls does not work. If I name the file xlsx it doesnt make a difference. Is there a Python package to convert to xlsx?

Improve this question
0

8 Answers 8

Reset to default
111

Here's an example using xlsxwriter:

import os
import glob
import csv
from xlsxwriter.workbook import Workbook


for csvfile in glob.glob(os.path.join('.', '*.csv')):
    workbook = Workbook(csvfile[:-4] + '.xlsx')
    worksheet = workbook.add_worksheet()
    with open(csvfile, 'rt', encoding='utf8') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader):
            for c, col in enumerate(row):
                worksheet.write(r, c, col)
    workbook.close()

FYI, there is also a package called openpyxl, that can read/write Excel 2007 xlsx/xlsm files.

Improve this answer
Sign up to request clarification or add additional context in comments.

14 Comments

Thanks for this very helpful code snippet. While using large files, it's better to use 'constant_memory' for controlled memory usage like: workbook = Workbook(csvfile + '.xlsx', {'constant_memory': True}). Ref: xlsxwriter.readthedocs.org/en/latest/working_with_memory.html
Nice... However, the xlsx files created are full of all number fields having errors that the fields are stored as text instead of numbers...
Found a fix to the numbers as text issue here: stackoverflow.com/questions/24971556/…
I had to add these lines to make it work with Western European languages import sys reload(sys) sys.setdefaultencoding('latin-1')
@MrMobileMan It is better to use the xlsxwriter constuctor option strings_to_numbers. For example, workbook = Workbook('output.xlsx',{'strings_to_numbers':True})
|
49

With my library pyexcel, 

 $ pip install pyexcel pyexcel-xlsx

you can do it in one command line:

from pyexcel.cookbook import merge_all_to_a_book
# import pyexcel.ext.xlsx # no longer required if you use pyexcel >= 0.2.2 
import glob


merge_all_to_a_book(glob.glob("your_csv_directory/*.csv"), "output.xlsx")

Each csv will have its own sheet and the name will be their file name.

Improve this answer

6 Comments

Very nice... Thanks! I up-voted this one. One issue I'm having, however, is that both this and xlswriter create xlsx's full of errors that the text fields are formatted as text instead of numbers...
Found the fix to the numbers as text issue here... stackoverflow.com/questions/24971556/…
If additional formatting is needed, you may not use merge_all_to_a_book but use pyexcel.Sheet, with which you can use format() function to convert float into int first, then use sheet operations to merge them and save as csv.
with pyexcel-cli package and pyexcel, pyexcel-xlsx, you can do that in command line: $ pyexcel merge your_csv_directory/*.csv out.xlsx
How to specify the sheet name if I have the only 1 file to be written to xlsx file?
|
37

Simple two line code solution using pandas

import pandas as pd

read_file = pd.read_csv('File name.csv')
read_file.to_excel('File name.xlsx', index=None, header=True)
Improve this answer

4 Comments

This probably the more OP way of doing it.
thanks ! how can i dump the content to the UI vs to file ?
gave me an eerror. pandas.errors.ParserError: Error tokenizing data. C error: Expected 1 fields in line 33, saw 2
Maybe you have semi-colon or something else than comma as delimiter. In that case you can tell read_csv. Example: read_file = pd.read_csv('File name.csv', delimiter=';')
27

First install openpyxl:

pip install openpyxl

Then:

from openpyxl import Workbook
import csv


wb = Workbook()
ws = wb.active
with open('test.csv', 'r') as f:
    for row in csv.reader(f):
        ws.append(row)
wb.save('name.xlsx')
Improve this answer

1 Comment

Easy And Support Unicode characters for it also!! It's best and simple for me.
12

Adding an answer that exclusively uses the pandas library to read in a .csv file and save as a .xlsx file. This example makes use of pandas.read_csv (Link to docs) and pandas.dataframe.to_excel (Link to docs).

The fully reproducible example uses numpy to generate random numbers only, and this can be removed if you would like to use your own .csv file. 

import pandas as pd
import numpy as np

# Creating a dataframe and saving as test.csv in current directory
df = pd.DataFrame(np.random.randn(100000, 3), columns=list('ABC'))
df.to_csv('test.csv', index = False)

# Reading in test.csv and saving as test.xlsx

df_new = pd.read_csv('test.csv')
writer = pd.ExcelWriter('test.xlsx')
df_new.to_excel(writer, index = False)
writer.save()
Improve this answer

2 Comments

depends on openpyxl inside pandas
Note: This depends on your CSV file being in flat-file format.
7

Simple 1-to-1 CSV to XLSX file conversion without enumerating/looping through the rows: 

import pyexcel

sheet = pyexcel.get_sheet(file_name="myFile.csv", delimiter=",")
sheet.save_as("myFile.xlsx")

Notes:

  1. I have found that if the file_name is really long (>30 characters excluding path) then the resultant XLSX file will throw an error when Excel tries to load it. Excel will offer to fix the error which it does, but it is frustrating.
  2. There is a great answer previously provided that combines all of the CSV files in a directory into one XLSX workbook, which fits a different use case than just trying to do a 1-to-1 CSV file to XLSX file conversion.
Improve this answer

2 Comments

simple way of doing it
Just a note, this solution requires pyexcel's plugin called pyexcel-xlsx.
4

How I do it with openpyxl lib:

import csv
from openpyxl import Workbook

def convert_csv_to_xlsx(self):
    wb = Workbook()
    sheet = wb.active

    CSV_SEPARATOR = "#"

    with open("my_file.csv") as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader):
            for c, col in enumerate(row):
                for idx, val in enumerate(col.split(CSV_SEPARATOR)):
                    cell = sheet.cell(row=r+1, column=idx+1)
                    cell.value = val

    wb.save("my_file.xlsx")
Improve this answer

Comments

1

There is a simple way

import os
import csv
import sys

from openpyxl import Workbook

reload(sys)
sys.setdefaultencoding('utf8')

if __name__ == '__main__':
    workbook = Workbook()
    worksheet = workbook.active
    with open('input.csv', 'r') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader):
            for c, col in enumerate(row):
                for idx, val in enumerate(col.split(',')):
                    cell = worksheet.cell(row=r+1, column=c+1)
                    cell.value = val
    workbook.save('output.xlsx')
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.