Combining .csv files into single multi-sheet Excel file

[Andrew H]

Hi.  I'm using a variant of the script found at http://superuser.com/questions/301431/how-to-batch-convert-csv-to-xls-xlsx to attempt to create an .xls file from multiple .csv files.  A copy of my current code is below.  I have three .csv files, but that may change.  They're simply called 01.csv, 02.csv, 03.csv.  I would like to create an .xls in which the contents of 01.csv go on the tab 01, and 02.csv and 03.csv similarly, giving 3 worksheets called 01, 02, 03 in a book called output.xls.  I expected the add_sheet method to do just that-- add a sheet.  But, that's not happening.  I end up with a single sheet called 03 in a file called output.xls.  How do I get these different .csv files onto their own sheets?

#!/usr/bin/env python

import os

import glob

import csv

import xlwt

for csvfile in glob.glob(os.path.join('.', '*.csv')):

    wb = xlwt.Workbook()

    fpath = csvfile.split("/", 1)

    fname = fpath[1].split(".", 1) ## fname[0] should be our worksheet name

    ws = wb.add_sheet(fname[0])

    with open(csvfile, 'rb') as f:

        reader = csv.reader(f)

        for r, row in enumerate(reader):

            for c, col in enumerate(row):

                ws.write(r, c, col)

    wb.save('output.xls')

[John Machin]

On Saturday, March 15, 2014 5:25:49 AM UTC+11, Andrew H wrote:

Hi.  I'm using a variant of the script found at http://superuser.com/questions/301431/how-to-batch-convert-csv-to-xls-xlsx to attempt to create an .xls file from multiple .csv files.  A copy of my current code is below.  I have three .csv files, but that may change.  They're simply called 01.csv, 02.csv, 03.csv.  I would like to create an .xls in which the contents of 01.csv go on the tab 01, and 02.csv and 03.csv similarly, giving 3 worksheets called 01, 02, 03 in a book called output.xls.  I expected the add_sheet method to do just that-- add a sheet.  But, that's not happening.  I end up with a single sheet called 03 in a file called output.xls.  How do I get these different .csv files onto their own sheets?

Do some elementary debugging e.g. see inserted print() calls below.

 

#!/usr/bin/env python

import os

import glob

import csv

import xlwt

for csvfile in glob.glob(os.path.join('.', '*.csv')):

           print("starting new workbook")

    wb = xlwt.Workbook()

    fpath = csvfile.split("/", 1)

    fname = fpath[1].split(".", 1) ## fname[0] should be our worksheet name

            print("adding sheet " + fname[0])

    ws = wb.add_sheet(fname[0])

    with open(csvfile, 'rb') as f:

        reader = csv.reader(f)

        for r, row in enumerate(reader):

            for c, col in enumerate(row):

                ws.write(r, c, col)

            print("saving workbook")

    wb.save('output.xls')

[Andrew H]

I just realized I made a rather foolish mistake by placing wb = xlwt.Workbook() inside the loop.  So, I'm now able to properly add worksheets as expected.

[John Machin]

On Saturday, March 15, 2014 11:02:24 AM UTC+11, Andrew H wrote:

I just realized I made a rather foolish mistake by placing wb = xlwt.Workbook() inside the loop.  So, I'm now able to properly add worksheets as expected.

Depends on what you call "as expected".
Do the debugging that I suggested and see what else is wrong.

[Andrew H]

It works now as I would like, although I neglected to say that I also placed wb.save('output.xls') outside the loop as well.  Now it creates 3 worksheets inside of a single workbook with correct data on each sheet.  I've never used this (or much of Python, as is probably obvious) before-- it's pretty awesome!  Thanks again for the help.

[Hassan EL Aissaoui]

Hi Andrew,

I see in your script:

you create (inside)  in the  first for  loop a new wb, so you add new ws and fill with a content from csv01, this happens fine.

But in the second and thirst loop you did the same thing so you create new bw and save again with the same name "output.xls"  (each loop), result;

 your delete each time last output.xls file and become at end only the result from the last loop that means an output.xls file with content of csv03

what you have to do is simply:

- create wb object before you begin you for loop 

- save your file output after (outside of) the for loop

good luck,

Hassan

--
You received this message because you are subscribed to the Google Groups "python-excel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to python-excel...@googlegroups.com.
To post to this group, send email to python...@googlegroups.com.
Visit this group at http://groups.google.com/group/python-excel.
For more options, visit https://groups.google.com/d/optout.