Friday, 8 July 2022

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)