Pandas Read Excel when the table begins on a variable row

user_8139700 0
Viewed 419

I have an Excel workbook with multiple sheets. I am trying to iteratively use Pandas read_excel() to read in a Dataframe from each sheet to output separate csv files for each.

def getSheets(inputfile, fileformat):
'''Split the sheets in the workbook into seperate CSV files in to folder
    in the directory. CSV's are named identical to the original sheet names'''
name = getName(inputfile) # get name
try:
    os.makedirs(name)
except:
    pass
# read as df
df1 = pd.ExcelFile(inputfile)
# for each sheet create new file
for x in df1.sheet_names:
    y = x.lower().replace("-", "_").replace(" ","_")
    print(x + '.' + fileformat, 'Done!')
    df2 = pd.read_excel(inputfile, sheet_name=x) #looking for way to dynamically find where the table begins
    filename = os.path.join(name, y + '.' + fileformat)
    if fileformat == 'csv':
        df2.to_csv(filename, index=False)
    else:
        df2.to_excel(filename, index=False)

The problem I am having is that the Excel workbook has a lot of formatting. The consequence is that the actual table begins on a different line in every sheet. Here is an example of one sheet in the workbook: example sheet

Here the table begins on Line 10. In other sheets in that same workbook the table begins on Line 8, and so on. There are >50 sheets and the first line of the table begins differently throughout.

I have read about ways to read from specific lines using 'skiprows' arguments. But that value for that arguement changes for each sheet I iterate over. Is there a way to use Pandas to read in a table when each table begins on a variable line or some way to identify where within an Excel sheet the table actually begins?

pandas excel python
1 Answers

You can find where the table begins by reading the Excel file manually before calling pd.read_excel (or its close cousin ExcelFile.parse):

frames = []

xl = pd.ExcelFile('data.xlsx')
for sheet in xl.book.sheets():
    # Find where a table begins within the first 200 rows of the sheet
    found = False
    for n in range(200):
        if sheet.cell_value(n, 0) == 'ID':
            found = True
            break
    if not found:
        raise ValueError('Cannot find the table')
    
    # Read the table
    frames.append(xl.parse(sheet.name, skiprows=n))
user_25389390
Related