{"id":2319,"date":"2022-05-28T12:51:26","date_gmt":"2022-05-28T07:21:26","guid":{"rendered":"https:\/\/sheetstips.com\/?p=2319"},"modified":"2022-06-17T11:45:12","modified_gmt":"2022-06-17T06:15:12","slug":"python-program-how-to-copy-data-from-one-excel-sheet-to-another","status":"publish","type":"post","link":"https:\/\/sheetstips.com\/python-program-how-to-copy-data-from-one-excel-sheet-to-another\/","title":{"rendered":"Python Program How to Copy Data from One Excel sheet to Another"},"content":{"rendered":"<p><strong>Python Copy Excel Sheet: <\/strong>Openpyxl, a Python package used to read, write, and edit Excel files (with the extension xlsx, xlsm, xltx, xltm) is what we need in order to work with them.<\/p>\n<p>We must first open the source and target Excel files before we may copy one Excel file to another. Next, we determine how many rows and columns there are in the source Excel file, read the value from one cell, save it in a variable, and then write that value to the target Excel file at a cell location similar to the source file&#8217;s cell position. In this article, we have provided everything about\u00a0How to Copy Data from One Excel sheet to Another using Python. Read on to find more.<\/p>\n<h2>How to Copy Data from One Excel sheet to Another in Python?<\/h2>\n<p>Given two excel files (Source and Destination) the task is to copy the contents of the source file to the destination file in Python.<\/p>\n<p><strong>Prerequisites:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/sheetstips.com\/python-google-sheets-excel-prerequisites\/#What_is_Excel?\">What is Excel?<\/a><\/li>\n<li><a href=\"https:\/\/sheetstips.com\/python-google-sheets-excel-prerequisites\/#Openpyxl\">Openpyxl<\/a><\/li>\n<\/ul>\n<p>Install the Openpyxl using the below syntax:<\/p>\n<pre>pip install openpyxl<\/pre>\n<p><strong>Intuition:<\/strong><\/p>\n<p>To copy one excel file to another, we must first open both the source and destination files. Then we count the number of rows and columns in the source Excel file, read a single cell value, save it in a variable, and send that value to the destination excel file at a cell location similar to the cell in the source file. The destination file has been saved.<\/p>\n<p><strong>SourceExcel File:<\/strong><\/p>\n<table width=\"442\">\n<tbody>\n<tr>\n<td width=\"212\">Website name<\/td>\n<td width=\"230\">Name<\/td>\n<\/tr>\n<tr>\n<td>Sheets Tips<\/td>\n<td>Vikram<\/td>\n<\/tr>\n<tr>\n<td>Sheets Tips<\/td>\n<td>Akash<\/td>\n<\/tr>\n<tr>\n<td>Sheets Tips<\/td>\n<td>Vishal<\/td>\n<\/tr>\n<tr>\n<td>Python-Programs<\/td>\n<td>Pavan<\/td>\n<\/tr>\n<tr>\n<td>Python-Programs<\/td>\n<td>Dhoni<\/td>\n<\/tr>\n<tr>\n<td>Python-Programs<\/td>\n<td>Virat<\/td>\n<\/tr>\n<tr>\n<td>BTechGeeks<\/td>\n<td>Devilliers<\/td>\n<\/tr>\n<tr>\n<td>BTechGeeks<\/td>\n<td>Pant<\/td>\n<\/tr>\n<tr>\n<td>PythonArray<\/td>\n<td>Smith<\/td>\n<\/tr>\n<tr>\n<td>PythonArray<\/td>\n<td>Warner<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-2325 aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/sourceExcel-file-image.png\" alt=\"sourceExcel file image\" width=\"628\" height=\"322\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/sourceExcel-file-image.png 628w, https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/sourceExcel-file-image-300x154.png 300w\" sizes=\"(max-width: 628px) 100vw, 628px\" \/><\/p>\n<p>We will see the step by step approach for Copying Data from One Excel sheet to Another Excel Sheet<\/p>\n<p><strong>1)Importing the modules<\/strong><\/p>\n<ul>\n<li>Import the openpyxl using the import and as keyword.<\/li>\n<\/ul>\n<p><strong>Below is the Implementation:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\"># import the openpyxl using the import and as keyword \r\nimport openpyxl as xl<\/pre>\n<p><strong>2)Creating Source Worksheet<\/strong><\/p>\n<ul>\n<li>Give the source excel filename as static input and store it in a variable.<\/li>\n<li>Pass the above file name as an argument to the load_workbook() function of openpyxl module to load the above source excel file as a workbook and store it in a variable.<\/li>\n<\/ul>\n<p><strong>Below is the Implementation:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\"># give the source excel filename as static input and store it in a variable\r\nsourceFilename =\"sourceExcel.xlsx\"\r\n# pass the above file name as argument to the load_workbook() function of openpyxl module to load the \r\n# above source excelfile as workbook and store it in a variable\r\nsourceWorkbook = xl.load_workbook(sourceFilename)\r\nsourceWorksheet = sourceWorkbook.worksheets[0]<\/pre>\n<p><strong>3)Creating Destination Worksheet<\/strong><\/p>\n<ul>\n<li>Create an empty excel file for the result and pass the excel file name as static input and store it in a variable.<\/li>\n<li>Pass the above file name as the argument to the load_workbook() function of openpyxl module to load the<br \/>\nabove source excel file as a workbook and store it in a variable.<\/li>\n<\/ul>\n<p><strong>Below is the Implementation:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\"># Create a empty excel file and pass the excel file name as static input and store it in a variable\r\ndestinationFilename =\"destinationExcel.xlsx\"\r\n# pass the above file name as argument to the load_workbook() function of openpyxl module to load the \r\n# above source excelfile as workbook and store it in a variable\r\ndestinationWorkbook = xl.load_workbook(destinationFilename)\r\ndestinationWorksheet = destinationWorkbook.active<\/pre>\n<p><strong>4)Getting the Excel File rows and columns size<\/strong><\/p>\n<ul>\n<li>Calculate the total number of rows and columns of the source excel files and store them in the corresponding two variables.<\/li>\n<li>Get the total number of rows by applying max_row attribute to the source worksheet and store it in a variable.<\/li>\n<li>Get the total number of columns by applying max_row attribute to the source worksheet and store it in a variable.<\/li>\n<\/ul>\n<p><strong>Below is the Implementation:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\">#Calculate the total number of rows and columns of the source excel files and store them in corresponding two variables.\r\n#Get the total number of rows by applying max_row attribute to the source worksheet and store it in a variable\r\nnumOfRows = sourceWorksheet.max_row\r\n#Get the total number of columns by applying max_row attribute to the source worksheet and store it in a variable\r\nnumOfColumns = ws1.max_column<\/pre>\n<p><strong>5)Copying and saving data from source excel file to destination excel file<\/strong><\/p>\n<ul>\n<li>Loop till the number of rows of the source worksheet using the for loop.<\/li>\n<li>Loop till the number of columns of the source worksheet using another nested for loop.<\/li>\n<li>Read the value of the cell of the source worksheet by passing the iterator values as arguments to the cell function and applying it to the source worksheet object.<\/li>\n<li>Store the value in some variable.<\/li>\n<li>Assigning the destination worksheet cell value with the above cell value.<\/li>\n<li>Saving the destination excel file.<\/li>\n<li>Save the destination excel file by passing the destination file name to the save() function and applying it to the destination workbook.<\/li>\n<\/ul>\n<p><strong>Below is the Implementation:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\">#Loop till the number of rows of the source worksheet using the for loop\r\nfor r in range (1, numOfRows + 1):\r\n  #Loop till the number of columns of the source worksheet using another nested for loop\r\n    for c in range (1, numOfColumns + 1):\r\n        # Read the value of the cell of source worksheet by passing the iterator\r\n    # values as arguments to the cell function and apply it to the source worksheet object\r\n    # Store the value in some variable\r\n        cellValue = sourceWorksheet.cell(row = r, column = c)\r\n        # Assinging the destination worksheet cell value with the above cell value\r\n        destinationWorksheet.cell(row = r, column = c).value = cellValue.value\r\n\r\n# saving the destination excel file\r\n# Save the destination excel file by passing the destination file name to the save() function \r\n# and apply it to the destination workbook\r\ndestinationWorkbook.save(str(destinationFilename))\r\n<\/pre>\n<h3>Below is the complete approach for the above program:<\/h3>\n<p><strong>Approach:<\/strong><\/p>\n<ul>\n<li>Import the openpyxl using the import and as keyword.<\/li>\n<li>Give the source excel filename as static input and store it in a variable.<\/li>\n<li>Pass the above file name as an argument to the load_workbook() function of openpyxl module to load the above source excel file as a workbook and store it in a variable.<\/li>\n<li>Create an empty excel file for the result and pass the excel file name as static input and store it in a variable.<\/li>\n<li>Pass the above file name as the argument to the load_workbook() function of openpyxl module to load the<br \/>\nabove source excel file as a workbook and store it in a variable.<\/li>\n<li>Calculate the total number of rows and columns of the source excel files and store them in the corresponding two variables.<\/li>\n<li>Get the total number of rows by applying max_row attribute to the source worksheet and store it in a variable.<\/li>\n<li>Get the total number of columns by applying max_row attribute to the source worksheet and store it in a variable.<\/li>\n<li>Loop till the number of rows of the source worksheet using the for loop.<\/li>\n<li>Loop till the number of columns of the source worksheet using another nested for loop.<\/li>\n<li>Read the value of the cell of the source worksheet by passing the iterator values as arguments to the cell function and applying it to the source worksheet object.<\/li>\n<li>Store the value in some variable.<\/li>\n<li>Assigning the destination worksheet cell value with the above cell value.<\/li>\n<li>Saving the destination excel file.<\/li>\n<li>Save the destination excel file by passing the destination file name to the save() function and applying it to the destination workbook.<\/li>\n<li>The Exit of the Program.<\/li>\n<\/ul>\n<p><strong>Below is the Implementation:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\"># import the openpyxl using the import and as keyword \r\nimport openpyxl as xl\r\n\r\n# give the source excel filename as static input and store it in a variable\r\nsourceFilename =\"sourceExcel.xlsx\"\r\n# pass the above file name as argument to the load_workbook() function of openpyxl module to load the \r\n# above source excelfile as workbook and store it in a variable\r\nsourceWorkbook = xl.load_workbook(sourceFilename)\r\nsourceWorksheet = sourceWorkbook.worksheets[0]\r\n\r\n# Create a empty excel file and pass the excel file name as static input and store it in a variable\r\ndestinationFilename =\"destinationExcel.xlsx\"\r\n# pass the above file name as argument to the load_workbook() function of openpyxl module to load the \r\n# above source excelfile as workbook and store it in a variable\r\ndestinationWorkbook = xl.load_workbook(destinationFilename)\r\ndestinationWorksheet = destinationWorkbook.active\r\n\r\n#Calculate the total number of rows and columns of the source excel files and store them in corresponding two variables.\r\n#Get the total number of rows by applying max_row attribute to the source worksheet and store it in a variable\r\nnumOfRows = sourceWorksheet.max_row\r\n#Get the total number of columns by applying max_row attribute to the source worksheet and store it in a variable\r\nnumOfColumns = ws1.max_column\r\n\r\n#Loop till the number of rows of the source worksheet using the for loop\r\nfor r in range (1, numOfRows + 1):\r\n  #Loop till the number of columns of the source worksheet using another nested for loop\r\n    for c in range (1, numOfColumns + 1):\r\n        # Read the value of the cell of source worksheet by passing the iterator\r\n    # values as arguments to the cell function and apply it to the source worksheet object\r\n    # Store the value in some variable\r\n        cellValue = sourceWorksheet.cell(row = r, column = c)\r\n        # Assinging the destination worksheet cell value with the above cell value\r\n        destinationWorksheet.cell(row = r, column = c).value = cellValue.value\r\n\r\n# saving the destination excel file\r\n# Save the destination excel file by passing the destination file name to the save() function \r\n# and apply it to the destination workbook\r\ndestinationWorkbook.save(str(destinationFilename))\r\n<\/pre>\n<p><strong>Output:<\/strong><\/p>\n<table width=\"442\">\n<tbody>\n<tr>\n<td width=\"212\">Website name<\/td>\n<td width=\"230\">Name<\/td>\n<\/tr>\n<tr>\n<td>Sheets Tips<\/td>\n<td>Vikram<\/td>\n<\/tr>\n<tr>\n<td>Sheets Tips<\/td>\n<td>Akash<\/td>\n<\/tr>\n<tr>\n<td>Sheets Tips<\/td>\n<td>Vishal<\/td>\n<\/tr>\n<tr>\n<td>Python-Programs<\/td>\n<td>Pavan<\/td>\n<\/tr>\n<tr>\n<td>Python-Programs<\/td>\n<td>Dhoni<\/td>\n<\/tr>\n<tr>\n<td>Python-Programs<\/td>\n<td>Virat<\/td>\n<\/tr>\n<tr>\n<td>BTechGeeks<\/td>\n<td>Devilliers<\/td>\n<\/tr>\n<tr>\n<td>BTechGeeks<\/td>\n<td>Pant<\/td>\n<\/tr>\n<tr>\n<td>PythonArray<\/td>\n<td>Smith<\/td>\n<\/tr>\n<tr>\n<td>PythonArray<\/td>\n<td>Warner<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-2325 aligncenter\" src=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/sourceExcel-file-image.png\" alt=\"sourceExcel file image\" width=\"628\" height=\"322\" srcset=\"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/sourceExcel-file-image.png 628w, https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/sourceExcel-file-image-300x154.png 300w\" sizes=\"(max-width: 628px) 100vw, 628px\" \/><\/p>\n<p>Now that we are provided all the necessary information about how to use Openpyxl to copy data from one Excel sheet to another in Python. If you have any questions related to this page, ping us through the comment box below and we will get back to you as soon as possible.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Python Copy Excel Sheet: Openpyxl, a Python package used to read, write, and edit Excel files (with the extension xlsx, xlsm, xltx, xltm) is what we need in order to work with them. We must first open the source and target Excel files before we may copy one Excel file to another. Next, we determine &#8230; <a title=\"Python Program How to Copy Data from One Excel sheet to Another\" class=\"read-more\" href=\"https:\/\/sheetstips.com\/python-program-how-to-copy-data-from-one-excel-sheet-to-another\/\" aria-label=\"More on Python Program How to Copy Data from One Excel sheet to Another\">Read more<\/a><\/p>\n","protected":false},"author":3,"featured_media":2332,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[182],"tags":[],"jetpack_featured_media_url":"https:\/\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Python-Program-How-to-Copy-Data-from-One-Excel-sheet-to-Another.png","jetpack-related-posts":[{"id":1971,"url":"https:\/\/sheetstips.com\/change-value-in-excel-using-python\/","url_meta":{"origin":2319,"position":0},"title":"Change value in Excel using Python","author":"David","date":"May 12, 2022","format":false,"excerpt":"Here, we will use Python to update a value in an Excel spreadsheet. 1)Opening an Excel File: openpyxl is a Python package that allows you to read and write Excel xlsx\/xlsm\/xltx\/xltm files. It originated from the lack of an existing library that allowed Python to read and write the Office\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Change value in Excel using Python using xlwt","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/01\/Change-value-in-Excel-using-Python-using-xlwt.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2887,"url":"https:\/\/sheetstips.com\/working-with-excel-files-in-python-using-xlwings\/","url_meta":{"origin":2319,"position":1},"title":"Working with Excel files using Python Xlwings: Learn Excel Python Integration","author":"David","date":"June 1, 2022","format":false,"excerpt":"Use Xlwings To Read Excel: If you want to read or write the Python data using Excel, then using the Xlwings function is the right option for you. This is because most people would like to view or understand the data from Excel rather than open the code background such\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Working with Excel files using Python Xlwings","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Working-with-Excel-files-using-Python-Xlwings.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":3128,"url":"https:\/\/sheetstips.com\/python-writing-excel-file-using-openpyxl-module\/","url_meta":{"origin":2319,"position":2},"title":"Python Program for Writing to an Excel File using openpyxl Module","author":"Anthony","date":"June 1, 2022","format":false,"excerpt":"Openpyxl is a Python library that reads and writes Excel files (with the extensions xlsx\/xlsm\/xltx\/xltm). Python openpyxl module allows the program to read and modify Excel files. For example, a user will have to go through hundreds of rows of data and select a few handful of information to make\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Python Program for Writing to an Excel File using openpyxl Module","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/06\/Python-Program-for-Writing-to-an-Excel-File-using-openpyxl-Module.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2881,"url":"https:\/\/sheetstips.com\/reading-excel-file-using-python\/","url_meta":{"origin":2319,"position":3},"title":"Reading an Excel File and Performing More Operations on it using Python","author":"David","date":"May 31, 2022","format":false,"excerpt":"Reading Excel Files in Python using Pandas: If you are an Excel user who wants to retrieve the data from the spreadsheet in Python, then this page is for you. To read the Excel data in Python, we can use the Pandas read_excel() function. The read_excel() function in Python will\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"Reading an Excel File and Performing More Operations on it using Python","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/05\/Reading-an-Excel-File-and-Performing-More-Operations-on-it-using-Python.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2729,"url":"https:\/\/sheetstips.com\/how-to-create-and-write-on-excel-file-using-xlsxwriter-module-in-python\/","url_meta":{"origin":2319,"position":4},"title":"How to Create and Write on Excel File using xlsxwriter Module in Python","author":"David","date":"May 28, 2022","format":false,"excerpt":"If you want to read or write data in an Excel file using Python, then you can simply use the XLSX writer module. Using the xlsxwriter function in Python, you can simply read or add information to the desired Excel file. Also, with the help of the xlsxwriter function, one\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"How to Create and Write on Excel File using xlsxwriter Module in Python","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/05\/How-to-Create-and-Write-on-Excel-File-using-xlsxwriter-Module-in-Python.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2303,"url":"https:\/\/sheetstips.com\/how-to-merge-all-excel-files-in-a-folder-using-python\/","url_meta":{"origin":2319,"position":5},"title":"How to Merge all Excel Files in a Folder using Python?","author":"David","date":"May 28, 2022","format":false,"excerpt":"In this article, we are going to merge all the files in a folder using Python. Prerequisites: What is Excel? Pandas Dataframe: Python Program to Merge all Excel Files in a Folder Excel sheet 1(SampleExcel1.xlsx): Excel sheet 2(SampleExcel2.xlsx): We will see the step by step approach for merging all the\u2026","rel":"","context":"In &quot;MS Excel&quot;","block_context":{"text":"MS Excel","link":"https:\/\/sheetstips.com\/category\/ms-excel\/"},"img":{"alt_text":"How to Merge all Excel Files in a Folder using Python","src":"https:\/\/i0.wp.com\/sheetstips.com\/wp-content\/uploads\/2022\/05\/How-to-Merge-all-Excel-Files-in-a-Folder-using-Python.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/2319"}],"collection":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/comments?post=2319"}],"version-history":[{"count":9,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/2319\/revisions"}],"predecessor-version":[{"id":3466,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/posts\/2319\/revisions\/3466"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/media\/2332"}],"wp:attachment":[{"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/media?parent=2319"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/categories?post=2319"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sheetstips.com\/wp-json\/wp\/v2\/tags?post=2319"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}