CODEWITHSUNDEEP
pythonexcelfilecsvxlsx
user670186
user670186

Reputation: 2840

Python convert csv to xlsx

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?

Upvotes: 71

Views: 251939

Answers (8)

Bhanu Sinha
Bhanu Sinha

Reputation: 1776

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)

Upvotes: 36

alecxe
alecxe

Reputation: 473753

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.

Upvotes: 110

Larry W
Larry W

Reputation: 121

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.

Upvotes: 7

zhuhuren
zhuhuren

Reputation: 347

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')

Upvotes: 27

Rubycon
Rubycon

Reputation: 18346

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")

Upvotes: 4

chfw
chfw

Reputation: 4592

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.

Upvotes: 48

patrickjlong1
patrickjlong1

Reputation: 3823

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()

Upvotes: 12

David Ding
David Ding

Reputation: 1749

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')

Upvotes: 1

Related Questions