How can I convert all .csv files in a folder into a single .xlsx file with multiple worksheet tabs (1 for each .csv)?

Question

I have a folder with 20 or so .csv files. I want to create a single .xlsx file (excel file) with multiple worksheet tabs (1 for each .csv).

Can somebody recommend a simple script to do this where the user only needs to specify 2 things: the folder with the .csv files & the path to the new .xlsx file?

I found the same question at this thread but without an answer I could understand:

https://superuser.com/questions/742454/how-to-convert-many-csv-files-into-1-xlsx-file-with-multiple-tabs?rq=1

Thanks All,

Shivamshaz · Accepted Answer · 2014-09-05T19:19:34.403

7

The following code takes folder name (with multiple csv files) as input and creates output as a single xls file with multiple sheets

import xlwt, csv, os

csv_folder = "Output/"

book = xlwt.Workbook()
for fil in os.listdir(csv_folder):
    sheet = book.add_sheet(fil[:-4])
    with open(csv_folder + fil) as filname:
        reader = csv.reader(filname)
        i = 0
        for row in reader:
            for j, each in enumerate(row):
                sheet.write(i, j, each)
            i += 1

book.save("Output.xls")

edited Sep 05 '14 at 19:19

answered Sep 05 '14 at 09:15

Shivamshaz

262
2
3
10

Thanks so much! I edited your post, adding os as an imported module and changed the variable 'output_folder' to 'csv_folder' which I believe was the intention. Thanks, works great! – bigCow Sep 05 '14 at 18:29
Cool, I missed those as this snippet was actually a section of a huge code. – Shivamshaz Sep 05 '14 at 18:32
Shivamshaz, I just noticed that each worksheet begins on the second row. Can you recommend a way to avoid this or to delete the first row? If you look at the output xls, the title row is in the second row of each worksheet tab. Also do you know why this is put 'on hold'? – bigCow Sep 05 '14 at 19:01
Shivamshaz, before I send you on the hunt, this thread does a good job too, I recommend people check this out. Thanks so much for helping an ignorant stranger. http://stackoverflow.com/questions/5705588/python-creating-excel-workbook-and-dumping-csv-files-as-worksheets?rq=1 – bigCow Sep 05 '14 at 19:14
@bigCow, I have changed the code so that output starts from first row itself – Shivamshaz Sep 05 '14 at 19:20
1

Thanks dude, I don't think the question is too broad, you provided a very narrow solution – bigCow Sep 05 '14 at 19:30
1

Hi there, may I ask that in this line, code `sheet = book.add_sheet(fil[:-4])` , what does **-4** mean here? – MlolM Aug 24 '15 at 13:55
@MlolM, It means 'the last four characters'. – hata Jan 02 '20 at 09:09

score 1 · Answer 2 · answered Sep 05 '14 at 09:34

1

I see you've tagged the question with python, but an option is using the Power Query ( free Excel add-in from Microsoft ) that has a "Get External Data" -> "From Folder" option.

answered Sep 05 '14 at 09:34

Alex

909
2
11
25

How can I convert all .csv files in a folder into a single .xlsx file with multiple worksheet tabs (1 for each .csv)?

2 Answers2

Linked