Copy and Paste the source Excel file to destination Excel file using Python openpyxl module

# Copy and Paste the source Excel file to destination Excel file using Python openpyxl module

# importing openpyxl module

import openpyxl as xl

# opening the source excel file

filename = "C:/Users/gary/file1.xlsx"

wb1 = xl.load_workbook(filename)

ws1 = wb1.worksheets[0]

# opening the destination excel file

filename1 = "C:/Users/gary/file2.xlsx"

wb2 = xl.load_workbook(filename1)

ws2 = wb2.active

# calculate total number of rows and

# columns in source excel file

mr = ws1.max_row

mc = ws1.max_column

print(mr)

print(mc)

# copying the cell values from source

# excel file to destination excel file

for i in range (1, mr + 1):

    for j in range (1, mc + 1):

        # reading cell value from source excel file

        c = ws1.cell(row = i , column = j)

        # writing the read value to destination excel file

        ws2.cell(row = i , column = j).value = c.value

# saving the destination excel file

wb2.save(str(filename1))

Convert xls files to xlsx Using Python Pandas Module

 # Convert xls files to xlsx using python pandas module

 # importing pandas as module

import pandas as pd

import os

df = pd.read_excel('file1.xls')

df.to_excel('file1.xlsx')

Rename Multiple Files Using Python

Using python to rename multiple files

 import os

rootdir = r'target'

str = "08"

for filename in os.listdir(rootdir):

    if str in filename:

        filepath = os.path.join(rootdir, filename)

        newfilepath = os.path.join(rootdir, filename.replace(str, "09"))

        os.rename(filepath, newfilepath)

Rename a file using Python pathlib module

Rename a file using Python pathlib module

 from pathlib import Path

path = Path("target file")

target = path.with_name(path.name.replace("08",'09'))

path.rename(target)

Convert PDF to xlsx file Using Python pandas and openxl module

#  Convert PDF file to CSV file

 #  Reference : https://www.youtube.com/watch?v=wJwXThPSBE8  ( From 3:48 )

import tabula

tabula.io.convert_into('A_PDF_file.pdf','A_CSV_file.csv', output_format='csv', pages='all')

#  Convert CSV file to xlsx file

#  https://www.youtube.com/watch?v=JFAn-aownHE  ( From 3:45 )

import csv

import openpyxl

csv_data = []

with open('A_CSV_file.csv') as file_obj:

    reader = csv.reader(file_obj)

    for row in reader:

        csv_data.append(row)

wb = openpyxl.Workbook()

sheet = wb.active

for row in csv_data:

    sheet.append(row)

wb.save('A_.xlsx')

Convert PDF to CSV file Using Python tabula module

 Use python tabula module to convert PDF to csv file

import tabula

tabula.io.convert_into('A_PDF_file.pdf', 'A_CSV_file.csv', output_format='csv', pages='all')

Combine multiple excel files Using python pandas module

Combine multiple  Excel files using python pandas module

from operator import index

import pandas as pd

import datetime as dt

files = ['1_data.xlsx','2_data.xlsx','3_data.xlsx','4_data.xlsx','5_data.xlsx','6_data.xlsx','7_data.xlsx','8_data.xlsx']

combined = pd.DataFrame()

for file in files:

    df=pd.read_excel(file)

    combined=combined.append(df,ignore_index=True)

combined.to_excel('combine.xlsx',index=False,sheet_name='data')