python: creating excel workbook and dumping csv files as worksheets

Question

I have few csv files which I would like to dump as new worksheets in a excel workbook(xls/xlsx). How do I achieve this?

Googled and found 'pyXLwriter' but it seems the project was stopped. While Im trying out 'pyXLwriter' would like to know are there any alternatives/suggestions/modules?

Many Thanks.

[Edit]

Here is my solution: (anyone has much leaner, much pythonic solution? do comment. thx)

import glob
import csv
import xlwt
import os

wb = xlwt.Workbook()


for filename in glob.glob("c:/xxx/*.csv"):
    (f_path, f_name) = os.path.split(filename)
    (f_short_name, f_extension) = os.path.splitext(f_name)
    ws = wb.add_sheet(str(f_short_name))
    spamReader = csv.reader(open(filename, 'rb'), delimiter=',',quotechar='"')
    row_count = 0
    for row in spamReader:
        for col in range(len(row)):
            ws.write(row_count,col,row[col])
        row_count +=1

wb.save("c:/xxx/compiled.xls")

print "Done"

watch out. Xlwt limits the number of inputs to 65,556. But if you have more number of rows, go for xlsxwriter — Baradwaj Aryasomayajula, Jul 20 '17 at 18:04
Try Sikuli. It's a Jython project, but you can use it to automate any GUI tasks. — Patrick, Apr 18 '11 at 15:58

score 28 · Accepted Answer · answered Apr 25 '11 at 10:36

Not sure what you mean by "much leaner, much pythonic" but you certainly could spruce it up a bit:

import glob, csv, xlwt, os
wb = xlwt.Workbook()
for filename in glob.glob("c:/xxx/*.csv"):
    (f_path, f_name) = os.path.split(filename)
    (f_short_name, f_extension) = os.path.splitext(f_name)
    ws = wb.add_sheet(f_short_name)
    spamReader = csv.reader(open(filename, 'rb'))
    for rowx, row in enumerate(spamReader):
        for colx, value in enumerate(row):
            ws.write(rowx, colx, value)
wb.save("c:/xxx/compiled.xls")

score 12 · Answer 2 · answered Apr 18 '11 at 16:27

12

You'll find all you need in this xlwt tutorial. This libraries (xlrd and xlwt) are the most popular choices for managing Excel interaction in Python. The downside is that, at the moment, they only support Excel binary format (.xls).

answered Apr 18 '11 at 16:27

Sergio

4,537
4
33
41

yea, looking thorugh the tutorial pdf. an idea clicked where I could create an xls files with many sheets and use the python csv reader module/method to dump line by line into the sheets. :) – siva Apr 18 '11 at 16:36
Yes, this is pretty much the idea. I would advise you to create the sheets when you need them, not beforehand. – Sergio Apr 18 '11 at 16:48
6

xlwt tutorial link is broken – nicholsonjf Mar 24 '14 at 19:07

score 4 · Answer 3 · answered Sep 09 '16 at 17:36

Use xlsxwriter to create and write in a excel file in python.

Install it by : pip install xlsxwriter

import xlsxwriter


# Create an new Excel file and add a worksheet.
workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()

# Widen the first column to make the text clearer.
worksheet.set_column('A:A', 20)

# Add a bold format to use to highlight cells.
bold = workbook.add_format({'bold': True})

# Write some simple text.
worksheet.write('A1', 'Hello')

# Text with formatting.
worksheet.write('A2', 'World', bold)

# Write some numbers, with row/column notation.
worksheet.write(2, 0, 123)
worksheet.write(3, 0, 123.456)

# Insert an image.
worksheet.insert_image('B5', 'logo.png')

workbook.close()

user809695 · Answer 4 · 2013-08-16T23:07:22.327

Also available in GitHub repo "Kampfmitexcel"...

import csv, xlwt, os

def input_from_user(prompt):
    return raw_input(prompt).strip()

def make_an_excel_file_from_all_the_txtfiles_in_the_following_directory(directory):
    wb = xlwt.Workbook()
    for filename in os.listdir(data_folder_path):
        if filename.endswith(".csv") or filename.endswith(".txt"):
            ws = wb.add_sheet(os.path.splitext(filename)[0])
            with open('{}\\{}'.format(data_folder_path,filename),'rb') as csvfile:
                reader = csv.reader(csvfile, delimiter=',')
                for rowx, row in enumerate(reader):
                    for colx, value in enumerate(row):
                        ws.write(rowx, colx, value)
    return wb

if __name__ == '__main__':
    path_to_data = input_from_user("Where is the data stored?: ")
    xls = make_an_excel_file_from_all_the_txtfiles_in_the_following_directory(path_to_data)
    xls_name = input_from_user('What do you want to name the excel file?: ')
    xls.save('{}\\{}{}'.format(data_folder_path,xls_name,'.xls'))
    print "Your file has been saved in the data folder."

score 3 · Answer 5 · answered Apr 18 '11 at 16:19

3

I always just write the Office 2003 XML format through strings. It's quite easy to do and much easier to manage than writing and zipping up what constitutes a xlsx document. It also doesn't require any external libraries. (though one could easily roll their own)

Also, Excel supports loading CSV files. Both space delimited or character delimited. You can either load it right in, or try to copy & paste it, then press the Text-To-Columns button in the options. This option has nothing to do with python, of course.

answered Apr 18 '11 at 16:19

adorablepuppy

1,077
1
7
13

thanks.. but not if I have 20 over csv which I going to open one by one and doing the cut and paste.. hehe :) – siva Apr 18 '11 at 16:23
To get a blank template file I can play with, I open up Excel, enter Test in A1, then use save as on the blank spreadsheet, select XML Spreadsheet 2003 as the filetype, then give it a name. You'll know what to do when you see the XML structure for it. – adorablepuppy Apr 18 '11 at 16:30
This sounds very interesting! – Sergio Apr 18 '11 at 23:07

score 1 · Answer 6 · answered Jul 20 '17 at 18:32

This is basing on the answer your answer itself. But the reason I am using xlsxwriter is because, it accepts more data in xlsx format. Where as the xlwt limits you to 65556 rows and xls format.

import xlsxwriter
import glob
import csv
workbook = xlsxwriter.Workbook('compiled.xlsx') 
for filename in glob.glob("*.csv"):
    ws = workbook.add_worksheet(str(filename.split('.')[0]))
    spamReader = csv.reader(open(filename, 'rb'), delimiter=',',quotechar='"')
    row_count = 0
    print filename
    for row in spamReader:
        for col in range(len(row)):
            ws.write(row_count,col,row[col])
        row_count +=1

workbook.close()

python: creating excel workbook and dumping csv files as worksheets

6 Answers6

Linked

Related