Python Forum
Append from csv to xlsx with values only
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Append from csv to xlsx with values only
#1
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)
Reply
#2
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.
Reply
#3
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!
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Cant Append a word in a line to a list err "str obj has no attribute append Sutsro 2 3,128 Apr-22-2020, 01:01 PM
Last Post: deanhystad

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020