import pandas as pd
import numpy as np
import os as os



# Get the user
username = os.getlogin()


# search for directory
directory = r'C:/Users/' + username + '/Digital Wholesale - Documents/2. Amazon/Customer Operations_OTC/02. Amazon weekly Report/Amazon weekly automation/'


# reading SAP extract  files from Run_report.xlsm and creating csv from it

df_1 = pd.read_excel(os.path.join(directory,'Run_report.xlsm'), sheet_name= "weekly",header= None)
df_1 = df_1.drop(df_1.columns[[0,1]], axis=1)
df_1.columns = df_1.loc[3].rename(None)
df_1 = df_1.drop(range(5))

df_1.to_csv(directory + '1.csv', index=False, header= True)


#Read CSV of SAP extract

df_weekly=pd.read_csv(os.path.join(directory,'1.csv'), low_memory=False)