CODEWITHSUNDEEP
pythonexcelcsv
Rutvik Pohankar
Rutvik Pohankar

Reputation: 1

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

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

Upvotes: 0

Views: 316

Answers (2)

Mig B
Mig B

Reputation: 647

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

Upvotes: 0

Rutvik Pohankar
Rutvik Pohankar

Reputation: 1

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)

Upvotes: 0

Related Questions