Python : Append data from new .xlsx files into existing .xlsx files

Append() methods in python mean adds its argument as a single element to the end of a list.
The length of the list increases by one.

1. Import library

import pandas as pd
from openpyxl import load_workbook

Pandas is an open source library that provides easy-to-use, high-performance data structures and data analysis for the Python programming language.

Openpyxl is a Python library to read and write .xlsx/.xlsm/.xltx/.xltm files.

2. Load first dataset

Load the existing dataset or the first dataset you have.

wb = load_workbook(filename = 'Data1.xlsx')
sheet_ranges = wb['Sheet1']
data = pd.DataFrame(sheet_ranges.values)
data 

data.columns = ['Daftar','Isi']
data1 = data[1:]
data1

The output will look like this:

3. Convert the DataFrame into an ArrayList 

You can do this step by appending all DataFrames into ArrayList. 

dataset1a = []
for dt in data1['Daftar']:
    dataset1a.append(''.join(dt))
print(dataset1a)

dataset1b = []
for dt2 in data1['Isi']:
    dataset1b.append(''.join(dt2))
print(dataset1b)

 
The output will look like this:

4. Load the second dataset

Load the second dataset to be appended

wb2 = load_workbook(filename='Data2.xlsx')
sheet_ranges1 = wb2['Sheet1']
dataa = pd.DataFrame(sheet_ranges1.values)
dataa 

dataa.columns = ['Daftar','Isi']
data2 = dataa[1:]
data2

The output will look like this:

5. Convert the DataFrame into an ArrayList 

Same as step three, you can do this step by appending all DataFrames into ArrayList.

rows = []
for d in data2['Daftar']:
    rows.append(''.join(d))
print(rows)

rows1 = []
for d1 in data2['Isi']:
    rows1.append(''.join(d1))
print(rows1)

The output will look like this:

6. Append arraylist from an existing dataset with an arraylist from the second dataset

for row in rows:
    dataset1a.append(''.join(row))
print(dataset1a)

for row1 in rows1:
    dataset1b.append(''.join(row1))
print(dataset1b)

 
The output will look like this: