How to Merge Multiples tabs in excel into One Excel sheet
import pandas as pd
import numpy as np
import os, collections, csv
from os.path import basename
df = []
f = "D:/Test/Any_Data.xlsx"
numberOfSheets = 5 #Modify this.
for i in range(1,numberOfSheets+1):
data = pd.read_excel(f, sheet_name = 'Sheet'+str(i), header=None)
df.append(data)
final = "D:/Test/mergedfile3.xlsx"
df = pd.concat(df)
df.to_excel(final)
How to Merge Multiples ExcelFiles into One excel sheet
import os
import pandas as pd
os.chdir('D:\\Test\\')
cwd = os.path.abspath('D:\\Test\\')
files = os.listdir(cwd)
## Method 1 gets the first sheet of a given file
df = pd.DataFrame()
for file in files:
if file.endswith('.xlsx'):
df = df.append(pd.read_excel(file), ignore_index=True)
df.head()
df.to_excel('total_sales.xlsx')
Information updating using Pandas Writing Queries on CSV files and put it in your XL sheet.
import pandas as pd
import xlwings as xw
from pandasql import sqldf
pysqldf = lambda q: sqldf(q, globals())
df= pd.read_csv('Test.csv')
df1=pysqldf("SELECT * FROM df where columnname not like 'None' and column_2 in('abc') order by host, case areas when 'London' then 1 when 'UK' then 2 when 'Pakistan' then 3 END ASC")
new_header = df1.iloc[0] #grab the first row for the header
df1 = df1[1:] #take the data less the header row
df1.columns = new_header #set the header row as the df header
print(pysqldf('SELECT * FROM df1'))
#load workbook that has a chart in it
wb = xw.Book('D:\\Python_Work\\data\\AnyReport.xlsx')
ws = wb.sheets['Sheet1']
ws.range('S5').options(index=False).value = df1
wb.save('D:\\Python_Work\\data\\AnyReport2.xlsx')
xw.apps[0].quit()
Loading an Excel File and then Update the sheet information on certain Row and Column
#Using openpyxl
from openpyxl import workbook #pip install openpyxl
from openpyxl import load_workbook
wb = load_workbook("D:\TEST\T.xlsx")
sheets = wb.sheetnames
Sheet1 = wb[sheets[Sheet1]]
#Then update as you want it
Sheet1 .cell(row = 2, column = 4).value = 5 #This will change the cell(2,4) to 4
wb.save("HERE PUT THE NEW EXCEL PATH")
Email Handling in Python
import datetime as dt
from datetime import timedelta
todayDaysDateTime = dt.datetime.now() - dt.timedelta(days = 0)
Outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI") #Opens Microsoft Outlook
folder = Outlook.Folders[0] #your Inbox
print(folder)
subFolder = folder.Folders[1] #your inbox
subFolderMessages = subFolder.Items #item of your inbox mean your emails
message = subFolderMessages.GetFirst()
while message:
#Here needs filter which should print only those mails having approved keyword
if(message.Subject=="sub1" ):
print("yes")
break
else:
message = subFolderMessages.GetNext()
print(message.Subject) #get the subjet of your email
myattachements=message.Attachments #attaments of your email.
attach = myattachements.item(1) # get the attahement of email.
attach.SaveAsFile('A:\\dir1\\dir2\\'+str(attach)) # save it to the location.
Other Approach Using for Loop
import win32com.client
import time
import datetime as dt
from datetime import timedelta
todayDaysDateTime = dt.datetime.now() - dt.timedelta(days = 0)
day_num = todayDaysDateTime.strftime("%Y-%m-%d")
Outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI") #Opens Microsoft Outlook
folder = Outlook.Folders[0] #your Inbox
print(folder)
subFolder = folder.Folders[1] #your inbox
subFolderMessages= subFolder.Items #item of your inbox mean your emails
for message in subFolderMessages:
if('youremailsubject' in message.Subject and str(message.ReceivedTime).split(' ')[0] = f'{day_num}'):
print(message.ReceivedTime)
print(message.Subject)
break
myattachements=message.Attachments #attaments of your email.
attach = myattachements.item(1) # get the attahement of email.
attach.SaveAsFile('A:\\path1\\path2\\'+str(attach)) # save ito the location.
##############
#import pyodbc
import pandas.io.sql as psql
import pandas as pd
import datetime
from datetime import timedelta
import win32com.client
#Variables for con, dataframe
con=pyodbc.connect("DSN=WorldISWorldFULL",autocommit=True)
cursor=con.cursor()
MY_ARRAY_TAB= ["ABC","DEF","GHI"]
D='abc123'
b='abc123'
#Generating Questions
def queries(Dates,CAB,CABDAY,CABM):
if 'InTheMYARY' in CAB:
return """
s '{}' as D,'{}' as CAB, count(**************) as CTS from {}_{}
""".format(Dates,CAB,CAB,CABDAY)
else:
return """
s '{}' as D,'{}' as CAB, count(************) as CTS from {}_{}
dare datefdateabc(abc,def+cz*circle,2020-11-11),dd/MM/yy)='{}'
""".format(Dates,CAB,CAB,CABM,Dates)
#Dates YourDaysRNumbered Convt
def date_conversion(x,CAB):
sysdate=datetime.datetime.YourDaysRNumberednow()
unixdate=datetime.datetime(2020,11,11)
YourDaysRNumbered=sysdate-timedelta(days=x)
CABDAY=str((YourDaysRNumbered - unixdate).days)
CABM=(sysdate.year - unixdate.year) * 144 + (sysdate.month - unixdate.month)
Date = datetime.datetime.now()-timedelta(days=x)
Date=str(Date.strftime("%d/%m/%y"))
return Date,queries(Date,CAB,CABDAY,CABM)
#Default value if CAB NF
def default_frame(d,t):
default_value = [[d,t,'NF']]
return pd.DataFrame(default_value,columns=['Dates','CAB','CTS'])
def send_mail(df):
o = win32com.client.Dispatch("Outlook.Application")
Msg = o.CreateItem(0)
Msg.Importance = 0
Msg.Subject = 'Hello World'
Msg.To = DESTINATARIES
Msg.SOnBehalfOfName = sby
Msg.HTMLBody='''<HTMLH>TEST ONE<HTMLH>{}'''.format(df.to_html(index=False))
Msg.Send()
# Main loop for CAB
def main():
df = pd.DataFrame()
for CAB in MY_ARRAY_TAB:
for x in range(7,11):
dates,sql = date_conversion(x,CAB)
try:
df1=psql.read_sql(sql,con)
df = pd.concat((df,df1))
except Exception as e:
if 'NF(2020)' in e.args[0]:
df=pd.concat((df,default_frame(dates,CAB)))
else:
print(e.args[0])
send_mail(df)
if __name__ == "__main__":
main()
######
Query An Excel files Using Pandas and SQLDF and Saving it as to a Comma Separated Values
import pandas as pd
from pandasql import sqldf
from datetime import date, timedelta
dt = date.today()
today = date.today()- timedelta(1)
day_num = today.strftime("%Y-%m-%d")
print(day_num)
pysqldf = lambda q: sqldf(q, globals())
df= pd.read_excel('1.xlsx', sheet_name='sheet1')
df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_').str.replace('(', '').str.replace(')', '')
df["my_date_"] = df["my_date"].dt.date # tell the data frame that take my_date as date colume type
df1=pysqldf(f"SELECT my_date, count(1) FROM df where my_date='{day_num}' group by my_date ") #
print(pysqldf('SELECT * FROM df1'))
df1.to_csv(f'YourFileName_{day_num}.csv',index=False)
Reading a value from the CSV file and then put that value to an Excel file on a target cell
import pandas as pd
import xlwings as xw
#read the csv file in data frame
df= pd.read_csv('1.csv')
df["columnNameof_CSVFile"]
#read a particular value from the column of the dataframe
print(df['columnNameof_CSVFile'].values[indexNo])#7 or 0
# load workbook
wb = xw.Book('2.xlsx')
ws = wb.sheets['SheetName in 2.xlsx']
x=ws.range('C' + str(ws.cells.last_cell.row)).end('up').row + 1
cell = 'C' + str(x)
ws.range(cell).value=df['columnNameof_CSVFile'].values[7]
wb.save('2.xlsx')
Query your Database and write the result of your sql query to the CSV file
import pyodbc
import csv
def write_queryresult_to_csv_file(cursor, sql, csv_file):
cursor.execute(sql)
file_header = ''
f = open(csv_file, 'w')
columns = [column[0] for column in cursor.description]
for col in columns:
file_header = file_header + col + ','
f.write(file_header[:-1] + '\n')
for row in cursor:
file_row = ''
for element in row:
if element is None:
element = ''
file_row = file_row + element.__str__().replace(',', ' ') + ','
f.write(file_row[:-1] + '\n')
f.close()
pyodbc.autocommit = True
ch=pyodbc.connect('DSN=odbcdbname', autocommit=True)
curs = ch.cursor()
sql="yourSQLQuery"
file_name="D:\\dirname\1.csv"
write_queryresult_to_csv_file(curs,sql,file_name)
Dealing with the Weekdays in Python
x=0
day ='day/month/YY'
dt = datetime.strptime(day, '%d/%m/%y')
print("Date given by the use"+str(dt))
if dt.weekday()==6:#Weekday return 6 for Sunday and Zero for Monday in python
start = dt - timedelta(days=x)
end = start + timedelta(days=6)
print(start)
print(end)
if dt.weekday()==0:
x=1
start = dt - timedelta(days=x)
end = start + timedelta(days=6)
print(start)
print(end)
else:
start = dt - timedelta(days=dt.weekday()+1)
end = start + timedelta(days=6)
print(start)
print(end)
print("Week day="+str(dt.weekday()))
