Merge Multiple Excel Files when there is missing ones

Question

I'm trying to merge three different workbooks (one sheet in each one) into a single one, transforming each in a new sheet. Code works like a charm when there is 'a', 'b, and 'c', but sometimes there won't be one or two of the three files, and I still need to create a workbook with just 'A' and 'C', 'B' and 'C', just 'A', etc.

import pandas as pd
import xlsxwriter

car_list = pd.read_csv(f'C:/Users/Downloads/cars.csv)
car = car_list['Car Name']
     for i in range(len(car)):
         cars = car[i]
         if pd.notna(cars):
             a = f'C:/Users/Downloads/A/{car[i]}.xlsx'
             b = f'C:/Users/Downloads/B/{car[i]}.xlsx'
             c = f'C:/Users/Downloads/C/{car[i]}.xlsx'
        
             writer = pd.ExcelWriter(f'C:/Users/Downloads/Output/{car[i]}.xlsx',engine='xlsxwriter')
        
             reada = pd.read_excel(a)
             readb = pd.read_excel(b)
             readc = pd.read_excel(c)
        
             reada.to_excel(writer, sheet_name='A')
             readb.to_excel(writer, sheet_name='B')
             readc.to_excel(writer, sheet_name='C')
        
             writer.save()

I already tried the addition below, but, for example: if there isn't 'B' for car 2, it just uses 'B' of car 1. How can I make it work?

    try:
        reada = pd.read_excel(a)
        readb = pd.read_excel(b)
        readc = pd.read_excel(c)
    except FileNotFoundError:
        pass

Edit: I don't know if it is the best way, but the following code worked just fine for that:

    if os.path.exists(a) is True:
        pd.read_excel(a).to_excel(writer, sheet_name='A')
    else:
        print(car[i]+' without A')

    if os.path.exists(b) is True:
        pd.read_excel(b).to_excel(writer, sheet_name='B')
    else:
        print(car[i] + ' without B')

    if os.path.exists(c) is True:
        pd.read_excel(c).to_excel(writer, sheet_name='C')
    else:
        print(car[i] + ' without C')

    writer.save()

There are 3 folders A, B, C and in each multiples xlsx files correponding to cars? — ArrowRise, Jul 28 '22 at 13:11
Exactly, but sometimes there won't be a file to car 2 in folder B, for example. — Lucas Steffens, Jul 28 '22 at 13:14

score 0 · Answer 1 · answered Jul 28 '22 at 13:11

0

You can use os.path.exists() to check if your file exists before using it

https://docs.python.org/3/library/os.path.html#os.path.exists

answered Jul 28 '22 at 13:11

Santiago Peron

51
5

score 0 · Answer 2 · answered Jul 28 '22 at 14:31

I reformed you code in more cleaner way :

import pandas as pd
import xlsxwriter
import os

#master path
master_path = 'C:/Users/Downloads/' 
#search every file or folder in the master path and store it in list only if it's not a file (thus a folder)
my_dirs = [x for x in os.listdir() if not os.path.isfile(os.path.join(os.getcwd(),x))]
#read your csv file, use path join with master path for reuseability with another path in another computer
car_list = pd.read_csv(os.path.join(master_path, 'cars.csv'))

for car in car_list['Car Name']:
    if not pd.notna(car):
        writer = pd.ExcelWriter(os.path.join(master_path, 'Output', f'{car}.xlsx'), engine='xlsxwriter')
        for my_dir in my_dirs:
            file_path = os.path.join(master_path, my_dir, f'{car}.xlsx')
            if os.path.exists(file_path):
                pd.read_excel(file_path).to_excel(writer, sheet_name=my_dir)
        writer.save()

There might be some errors but I can't debug then without the input

Merge Multiple Excel Files when there is missing ones

2 Answers2