Python Program to extract data from multiple Excel Files

in this video we will discuss about a Python program that can copy data from all Excel files in a computer folder and place that data into a single Excel worksheet and that do within a few seconds when I say all Excel files in a computer folder it can be hundreds or thousands of excel workbooks this video is divided into three parts in the first part I will show you the data we are working with second part is about installing Python and Open PI Excel those who have Python and Open PI excel in the computer can skip to the third part which is writing and executing the program for explaining the program I am using the scorecards of your engineering students but the same program with nominal changes can be used for wide range of requirements see this computer folder called scorecard there are more than 130 Excel workbooks in this folder to be precise 139 Excel workbooks with marks of that many engineering students let me open one of these workbooks ce591 this is the scorecard of a candidate code job in John whose role number is ce591 and the marks scored by jobin for his six exams are listed here we'll close this workbook let me open one more ce683 these are the marks scored by Mecca whose role number is ce683 now about our requirement we need to extract the name roll number and Mark scored by the candidate from all these 139 Excel workbooks into a single worksheet before writing the Python program for this task let me show you how much time it will take if we do this task manually first we have to open a scorecard copy the name and row number of the candidate paste the Gopi data into the master sheet back to the scorecard copy this course paste this course in the master sheet after that we have to close the current scorecard open the next one and repeat the process copying data from an Excel worksheet and pasting it into the master sheet will take at least 30 seconds that means for extracting data from all these 139 files and pasting it into a single worksheet we need more than one hour of manual labor now let's see how to create a Python program that can complete this task within a few seconds for writing the code we need Python and Open PI excel in the computer to install python open the web browser Google for download python download python from here you can download the latest version of python but if you are looking for a specific version scroll down see different versions of python are listed here download python click on the setup file to start installation install now setup was successful means installation complete in the search bar of Windows type in Python open python idle now we have python in our computer next we need the library called Open PI Excel for installing this package activate the command prompt of windows for that in the search bar of Windows type in CMD to install open Pixel type in PIP install Open PI Excel enter and now we have Open PI Excel to get out of the command prompt type in exit enter now that we have Python and Open PI excel in our computer let's write the program for reading Excel files in the windows search bar type in Python open python idle to open the file editor of python idle file new file let's save this file before writing the code for that file save will save this file in the desktop of the computer we'll name the file as read from Excel files save now the first line of the code import OS is a python module which will help us to interact with the operating system of the computer next line import Open PI Excel is a third party python Library which will help us to read and edit Excel files folder equal apostrophe here we have to specify the path of the directory containing the Excel files to read this is the folder containing the scorecards to copy the folder path click on the address bar right click copy paste the copied path here apostrophe output file equal the data copied from 139 Excel workbooks will be written into a single Excel workbook specify a name for that file and the path of the folder where you want that file to be saved I will be keeping that file in the same folder for that apostrophe right click paste backslash type in the file name apostrophe here I have used the file name candidates course but you can use whatever name you want these three lines are to create a new workbook and to rename the active worksheet of that workbook as candidate data next we have to specify the cells from which we need to extract data name of the candidate is in the cell C2 roll number is in the cell C3 scores are in the cells from D7 to detol foreign data from these cells cells equal open square bracket apostrophe address of the first cell containing data C2 apostrophe comma space apostrophe and to solve the second cell containing data C3 apostrophe comma space now the address of the cells containing scores apostrophe D7 apostrophe comma space closing square bracket let me close this workbook now the instructions to open all these Excel files one by one and copy data from those workbooks thank you for Loop to execute these instructions in each Excel workbook in the folder specified here and if statement that will make sure that data is extracted only from Excel files this statement will create a file path using the folder path and file name specified here that file is then saved into a variable code workbook data from the specified cells of this workbook are then saved into variable code values these values are then written into the output sheet that is the sheet called candidate data and these statements under this for Loop will be executed for each and every file in the specified folder so after 139 iterations the output workbook will be having data from all these workbooks now to save this workbook in the name candidates course now we have the program for reading Excel files to execute the program run module okay it's a new Excel workbook called candidates course is created let me open this workbook the name roll number and scores of all those 139 candidates are copied into this Excel workbook and that too within a few seconds now let's cross check this data ce587 candidate name is Amal PT roll number ce587 and this course 98 79 87 66 40 45. candidate's name is Shilpa roll number is ce645 and the scores are 50 to 58 53 82 55 67. like I mentioned in the beginning of this video this program can be easily modified for your requirements replace this folder path with that of the directory containing your Excel files and specify the address of the cells from which data needs to be extracted so we have seen how to read data from multiple Excel files the next word is about a Python program for editing multiplexer files until then take care thank you for watching and wish you a great day thank you

Channel: Ajay Anand

Views: 18,608

Keywords: XL n CAD, XLnCAD, Excel, MS Excel, Excel Tricks, Excel Tips, Learn Excel, Excel for Beginners, Excel Tutorial, Programming in Excel, Excel Programming, Excel VBA, Learn VBA, Macro, Excel Macro, VBA Macro, Excel Shortcuts

Id: M7YkQpcB4fg

Length: 12min 0sec (720 seconds)

Published: Fri Mar 31 2023