Read excel tables from bunch files in the folder and convert them to csv files with delimiter '|'

import os

import pandas as pd

from janitor import xlsx_table

from tabulate import tabulate

# folder path

dir_path = "C:/test/"

# list to store files

res = []

# Iterate directory

for path in os.listdir(dir_path):

    # check if current path is a file

    if os.path.isfile(os.path.join(dir_path, path)):

        res.append(path)

pd.set_option("display.max_columns", None)

pd.set_option("display.expand_frame_repr", False)

#pd.set_option("max_colwidth", None)

if os.path.exists(dir_path+"/archive/ERPEVAL.txt"):

    os.remove(dir_path+"/archive/ERPEVAL.txt")

    print(dir_path+"/archive/ERPEVAL.txt"+" This file has been deleted successfully")

else:

    print("The file does not exist!")

for a in res:

    #print (a)

    filename = dir_path+a

    print (filename+" -> File Processed successfully")    

    df = pd.DataFrame(xlsx_table(filename,sheetname = 'Questionnaire', table=["datatable"]))    

    # print(df.to_csv(index=False,header=None,line_terminator='\n',sep='|',encoding='utf-8',columns=["ID","Name","Dept","Category","pscore","ascore","fsiscore"]))

    with open(dir_path+"/archive/ERPEVAL.txt", 'a+') as f:

        str = df.to_csv(index=False,header=None,line_terminator='\n',sep='|',encoding='utf-8',columns=["ID","Name","Dept","Category","pscore","ascore","fsiscore"])

        f.write(str)