Python Forum

Full Version: Append from csv to xlsx with values only
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I'm trying to find in Activities[1:30000] the 1st cell that matches Report[1, 0] and remember that cell address. Then copy Report[1:30000, 0:37] and paste, values only, starting in the matched cell. A friend gave me this code, but it doesn't work. Column A in both workbooks is named the same and sorted ascending. Activities.xlsx is a table, extending past [37] from [38:52]. The 'Paste values only' needs to be contained in [0:37] so as not to erase formulas in [38:52].
Any assistance would be greatly appreciated.
Sick

Here is the code I'm working from:

import pandas as pd

report_df = pd.read_csv('Report.csv')

activities_df = pd.read_excel('Activities.xlsx')

value_to_match = report_df.iloc[1, 0]

match_index = activities_df.iloc[1:30000].eq(value_to_match).stack().idxmax()

match_row, match_col = match_index

copied_data = report_df.iloc[1:30000, 0:37]

rows_available = activities_df.shape[0] - match_row
cols_available = activities_df.shape[1] - match_col

activities_df.to_excel('Activities.xlsx', index=False)
Maybe post a sample of Report.csv and Activities.xlsx and then take it 1 step at a time. If they are very big, just post a few rows.

What does this look like: value_to_match = report_df.iloc[1, 0]??

If you know what it is, you can easily get all rows which contain whatever that is.
Activites.xlsx contains all job rows from 1/1/2022 on and is a formatted Table.
Column [A] is numeric ascending from 101000 to 357820.

Report.csv is all job rows within the last 3 months
Column [A] is also numeric ascending from 303150 to 360000

Both columns [A] have the same header "MW Job ID,and an invisible character"

Some rows from Report 303150 to 357820 may have revisions in column[B:AK] plus new rows from 357851 to 360000.

The easiest way to keep all data current is to copy Report[A2:AK20000]
and paste all, values only(as not to disrupt the Table or columns:[AL+]),
into the first cell in the Activities column [A] to match Report [A2].

The files are huge but if necessary, I'll reduce and post.

New rows are added daily, and existing open job rows can change. Going back 3 months covers any changes.

Thank you!
Sick!