CODEWITHSUNDEEP
pythonexcelcsv
cmg42
cmg42

Reputation: 1

Python: Inserting copied rows into Excel without erasing existing data

I am trying to automate a process that copies all rows (except row 1) of a csv file into an existing Excel worksheet. These copied rows need to be inserted above the existing data below the Header (which is row 1) in the Excel sheet, similar to "Insert Copied Cells" action in Excel.

I tried the following code snippet, but it is erasing the existing data.

import pandas as pd

# Import the CSV file into a Pandas DataFrame
df_csv = pd.read_csv("data.csv", skiprows=1)

# Get the path to the Excel file
excel_file_path = "output.xlsx"

# Open the Excel file
with pd.ExcelWriter(excel_file_path) as writer:

    # Add the CSV rows to the top of the Excel file
    df_csv.to_excel(writer, sheet_name="Sheet1", startrow=1, index=False)

I tried to add mode ='a' to ExcelWriter, but I am getting the following error:

with pd.ExcelWriter(excel_file_path, mode='a',if_sheet_exists='overlay') as writer:

Traceback (most recent call last): File "c:\Users\gouri\Python_Code\csv_to_excel.py", line 10, in with pd.ExcelWriter(excel_file_path, mode='a',if_sheet_exists='overlay') as writer: ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\gouri\Python_Code.venv\Lib\site-packages\pandas\io\excel_xlsxwriter.py", line 197, in init raise ValueError("Append mode is not supported with xlsxwriter!") ValueError: Append mode is not supported with xlsxwriter!

So, I changed the engine to openpyxl, this is an improvement in the sense that it is not erasing all data but overwriting only the cells it is copying data to.

with pd.ExcelWriter(excel_file_path, engine='openpyxl', if_sheet_exists='overlay', mode='a') as writer:

I want to insert new rows on the top of the existing data rows. Can this be done using pandas or some other module? Thanks for your help!

Upvotes: 0

Views: 413

Answers (1)

Xukrao
Xukrao

Reputation: 8634

You can concatenate the CSV data and the Excel data, and then simply replace an entire worksheet:

import pandas as pd

csv_file = "data.csv"
excel_file = "output.xlsx"
excel_sheet = "Sheet1"

# Parse data files into dataframes
df_csv = pd.read_csv(csv_file)
df_excel = pd.read_excel(excel_file, sheet_name=excel_sheet)

# Concatenate dataframes
df = pd.concat([df_csv, df_excel])

# Write dataframe to Excel sheet
with pd.ExcelWriter(excel_file, engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
    df.to_excel(writer, sheet_name=excel_sheet, index=False)

Any formatting that was already present in the worksheet will be lost. But if your formatting needs are relatively modest, then you could use pandas' built-in styling methods to add the desired formatting.

Upvotes: 1

Related Questions