Copying values from multiple excel files to a single one with python

0 votes

I want to replicate the values from a folder of numerous excel files that has values in columns a through g into a single excel file.

The following piece of code has been tested by me:

`  import os
    import pandas as pd

   # Specify the input folder containing Excel files
    input_folder = 'C:/Users/User/Desktop/FilesToProcess'

   # Create an empty DataFrame to store the selected data
    selected_data = pd.DataFrame()

   # Loop through all Excel files in the input folder
    for file_name in os.listdir(input_folder):
    if file_name.endswith('.xlsx'):
   # Read in the Excel file and select the desired columns
    df = pd.read_excel(os.path.join(input_folder, file_name), usecols='A:G')
   # Append the selected data to the main DataFrame
    selected_data = selected_data.append(df)[df.columns.tolist()]

   # Specify the output file name and sheet name
    output_file = 'C:/Users/User/Desktop/output1.xlsx'
    output_sheet_name = 'Sheet1'

   # Write the selected columns to a new Excel file
    with pd.ExcelWriter(output_file) as writer:
    selected_data.to_excel(writer, sheet_name=output_sheet_name, index=False)`

Although it initially functions as intended, after a few files, the columns become messed up. It starts copying values in columns > G specifically. Due to the nature of the data, I am unable to provide a result example.

Apr 11, 2023 in Others by Kithuzzz
• 38,010 points
685 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Others

0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,420 points
1,586 views
0 votes
1 answer

How do I merge multiple excel files to a single excel file

You copy a worksheet from before each ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 63,420 points
601 views
0 votes
1 answer

Copying a sheet with specific formatting from an excel file to a new output

Using Python and the openpyxl package, you ...READ MORE

answered Mar 31, 2023 in Others by Kithuzzz
• 38,010 points
7,570 views
0 votes
1 answer

How to convert data from txt files to Excel files using python

Hi , there are few steps to ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
13,356 views
0 votes
0 answers

How to drop all tables from a database with one SQL query?

What if we don't want to type ...READ MORE

May 28, 2022 in Others by Sohail
• 3,040 points
184 views
0 votes
0 answers

How to drop all tables from a database with one SQL query?

drop all tables without typing name. Is ...READ MORE

Jun 7, 2022 in Others by polo
• 1,480 points
179 views
0 votes
1 answer

Convert column in excel date format (DDDDD.tttt) to datetime using pandas

Given # s = df['date'] s 0 ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,420 points
2,782 views
0 votes
1 answer

How to freeze the top row and the first column using XlsxWriter?

You can use worksheet.freeze_panes() to achieve this . There ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 63,420 points
1,790 views
0 votes
1 answer

Compare 2 columns in same excel sheet in pandas

Try this: import pandas as pd import numpy as ...READ MORE

answered Dec 16, 2022 in Others by narikkadan
• 63,420 points
2,142 views
0 votes
1 answer

Export DataFrame timedelta column to timestamp Excel column

The reason that the column format isn't ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,420 points
1,095 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP