How should i open multiple csv files simultaneously in python in order to create a xlsx file

Question

I am working on a project to convert a .csv file into .xlsx, i successfully implemented the code but my requirement is to convert all the .csv files in a directory at once which i'm a bit confused that how should i do that pls help

I tried it by giving different address for the files but i want it to be in loop

filename = "C:\Python36\Form project\CI File\CCS DATA\ADD01.csv" filename2 ="C:\Python36\Form project\CI File\CCS DATA\ADD02.csv"

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

now = datetime.now().strftime('%d-%m-%y-%H;%M;%S')

workbook = xlsxwriter.Workbook(' '+now+'.xlsx')

worksheet = workbook.add_worksheet()

with open(csvfile, 'rt') as f:
    reader = csv.reader(f)

    for r,row in enumerate(reader):

        print(row)

        r=r+11
        if r>=12:
            break


        #sheet.cell_value(0,r)
        for c, col in enumerate(row):

            c=c+1

            worksheet.write(r, c, col)

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

#now = datetime.now().strftime('%d-%m-%y-%H;%M;%S')

#workbook = xlsxwriter.Workbook(' '+now+'.xlsx')

#worksheet = workbook.add_worksheet()

with open(csvfile, 'rt') as f:
    reader = csv.reader(f)

    for r,row in enumerate(reader):

        print(row)

        r=r+12
        if r>=13:
            break

I tried it by giving different address for the files but i want it to be in loop

I believe what you are looking for is to loop over all files that end with csv extension. This post might help you: https://stackoverflow.com/a/3964691/11400091 — Zwepp, May 23 '19 at 11:31
Do you want the csv to be on seperate sheets or in one sheet, and are the csv the same format? — Philip, May 23 '19 at 20:44

Mig B · Answer 1 · 2019-05-24T07:39:51.460

this could help you, as you can iterate over every file in your folder and check if it has a .csv-ending.

import os

INPUT_PATH = "C:\Python36\Form project\CI File\CCS DATA\"

for file in os.listdir(INPUT_PATH):
    if file.endswith('.csv') or file.endswith('.CSV'):
        INPUT_FILE = INPUT_PATH + file
        with open(INPUT_FILE, 'rt') as f:
             #your code here

I guess you want to write all .csv-data into one .xslx-file? Otherwise add something like this:


import os

INPUT_PATH ="C:\Python36\Form project\CI File\CCS DATA\"

for file in os.listdir(INPUT_PATH):
    if file.endswith('.csv') or file.endswith('.CSV'):
        INPUT_FILE = INPUT_PATH + file
        workbook = INPUT_PATH + file[:-4] + 'xlsx'


        with open(INPUT_FILE, 'rt') as f:
             #your code here

EDIT: with your additional information here is another try to help: (could you share your .csv-Data to see what you are processing?)

##create .xlsx-file##
now = datetime.now().strftime('%d-%m-%y-%H-%M-%S')
workbook = xlsxwriter.Workbook(directory+now+'.xlsx')

##iterate over .csv-files##
for filename in os.listdir(directory):
    if filename.endswith(".csv"):
        csvfile = os.path.join(directory, filename)
        ##add worksheet named as .csv-file##
        worksheet = workbook.add_worksheet('{csvfile}'.format(csvfile=filename))
        ##open and process .csv-file##
        with open(csvfile, 'rt') as f:
            reader = csv.reader(f)
            for r,row in enumerate(reader):
                for i in range(59):
                    r=r+i
                    if r>=(i+1):
                        break
                    for c, col in enumerate(row):
                        c=c+1
                        worksheet.write(r, c, col)
##don't forget to close your workbook afterwards##
workbook.close()

Yes,I want in 1 .xlsx file, i tried this code but it is just accessing the csv files but not extracting the data i need into csv! — Rutvik Pohankar, May 24 '19 at 06:04
Then you can use the first approach up here. You just have to add your data extracting code inside the `with open(..) as f:` — Mig B, May 24 '19 at 06:53
Now, my code is running successfully but it is not entering my if loop and directly ending up without saving my file — Rutvik Pohankar, May 24 '19 at 07:17

score 0 · Answer 2 · answered May 24 '19 at 07:19

Now, my code is running successfully but it is not entering my if loop and directly ending up without saving my file, Pl. ignore the indentations

for filename in os.listdir(directory):

if filename.endswith(".csv"):
    open(os.path.join(directory, filename))
    for csvfile in glob.glob(os.path.join('.', filename)):

        now = datetime.now().strftime('%d-%m-%y-%H;%M;%S')

        workbook = xlsxwriter.Workbook(' '+now+'.xlsx')

        worksheet = workbook.add_worksheet()

        with open(csvfile, 'rt') as f:
            reader = csv.reader(f)

else:
    for r,row in enumerate(reader):
        for i in range(59):
            r=r+i
            if r>=(i+1):
                #print(r)
                break


            #sheet.cell_value(0,r)
            for c, col in enumerate(row):

                c=c+1

                worksheet.write(r, c, col)

How should i open multiple csv files simultaneously in python in order to create a xlsx file

2 Answers2