Using pandas pd.Excel File with user input for folder path and filename

Question

I'm using pd.ExcelFile as below to open and parse a file, but currently only with the actual folder path and filename in one string.

wb = pd.ExcelFile(folder_path+filename)

I want to put this into a function, that asks the user to give a path and filename and deals with invalid input. I started something like the below, but it doesn't seem like the error is being generated inside the function anyway, and i'm not sure how to say 'while wb isn't a valid thing' to continue to prompt for a filepath until we get a valid one?

def Load_Parse():
    folder_path = input('\nEnter the path to the qry_T spreadsheet here (include slashes at the start and at the end): ')
    filename = input('\nEnter the name of the spreadsheet to be used here: ')
    sheetname = input('\nEnter the sheet containing the data here, including the extension (e.g. "qry_Trajectory 2019.xlsx": ')
    try:
        wb = pd.ExcelFile(folder_path+filename)
    except FileNotFoundError:

Any ideas?

I'll then parse the file using a similar method i hope:

df = wb.parse('filename')

Umar.H · Accepted Answer · 2020-03-20 10:58:21Z

using Pathlib, os and pandas and a few functions.

one of the key functions you'll need is the while True which keeps executing a block of code until it's true and you initiate a break

feel free to edit to your own spec.

Modules

from pathlib import Path
import os
import pandas as pd
from xlrd import XLRDError

In Action

df = load_parser()

out:
#Hello Umar.Hussain please enter a valid target directory
#C:\Users\UmarH\Files
#1 excels_0
#2 excels_1
#Choose a number between 1 and 2
1
#Your Choice is excels_0.xlsx
#Choose a Sheet - Lists all sheets
'Sheet1'
# returns dataframe

Main Function

def load_parser():

    user = os.getlogin()

    print(f"Hello {user} please enter a valid target directory") 
    cmd = input('')

    p = file_tester(cmd,file_type='path')
    print("Please select a number from the following file")
    target_file = create_excel_dict(p)
    target_df = enumerate_sheets(target_file)



    return target_df

Helper Functions

def file_tester(string_path, file_type="path"):
    path = Path(string_path)

    while True:
        if path.is_dir():
            break
        else:
            cmd = input(f"Please Enter a Valid {file_type}")
            path = Path(cmd)

    return path


def create_excel_dict(target_path):
    xlsx_dict = {i: x for i, x in enumerate(target_path.glob('*.xlsx'), 1)}

    for k,v in xlsx_dict.items():
        print(k,v.stem)

    rng = [i for i in xlsx_dict.keys()]

    file_choice = input(f'Choose a number between {rng[0]} and {rng[-1]}')

    while True:
        try:
            file_choice = int(file_choice)
            print(f"Your Choice is {xlsx_dict[file_choice]}")
            break
        except KeyError:
            file_choice = input(f'Choose a number between {rng[0]} and {rng[-1]}')

    return xlsx_dict[file_choice]

def enumerate_sheets(target_file):

    xl = pd.ExcelFile(target_file)

    for sheet in xl.sheet_names:
        print(sheet)
    target_sheet = input("Please Type Your sheet name")

    while True:
        try:
            df = pd.read_excel(xl,sheet_name=target_sheet)
            break
        except XLRDError:
            target_sheet = input("Please enter a sheet from above.")

    return df

Took me an embarrassing amount of time for me to get this (in the end copied and pasted until i did) but was exactly what i needed and really clearly set out. Thanks :)

Collectives™ on Stack Overflow

Using pandas pd.Excel File with user input for folder path and filename

1 Answer 1

Modules

In Action

Main Function

Helper Functions

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modules

In Action

Main Function

Helper Functions

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related