'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Import Excel 97-2003 Files into Power Query and Repeat Rows
Question details
The user needs to import legacy Excel 97-2003 (.xls) workbooks into Power Query, and also wants to know how to duplicate worksheet rows or repeat specific rows across printed pages.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Handling older spreadsheet formats for data transformation and configuring page setups for physical document printing.
- Observed behavior
- The user is looking for the exact menu navigation to import single or multiple legacy files and the page layout settings required to repeat rows on prints.
Ensure your legacy Excel files (.xls) are not corrupted or password-protected, and create a backup of your original workbooks before transforming data.
Import a Single Excel 97-2003 File into Power Query
Use the standard Get Data menu to locate and transform a single legacy workbook.
This is the primary method for extracting data from older Excel files. Power Query handles .xls files seamlessly if the correct import path is selected.
Open your current Excel workbook and click on the 'Data' tab located on the top ribbon.
Click on 'Get Data', choose 'From File' from the drop-down menu, and then click 'From Excel Workbook'.
Browse your computer to locate the specific Excel 97-2003 (.xls) file you want to import, select it, and click 'Import'.
In the Navigator window, select the worksheets or tables you need. Click 'Load' to send the data directly to your workbook, or 'Transform Data' to open the Power Query Editor for further formatting.
Import Multiple Legacy Excel Files from a Folder
Use this method to combine and merge multiple Excel 97-2003 files stored within the same directory.
Set Rows to Repeat on Printed Pages
Configure your worksheet so that specific header rows print at the top of every physical page without duplicating the actual data.
Import Data and Set Print Layouts in WPS Spreadsheet
WPS Spreadsheet makes it simple to open legacy Excel 97-2003 files, manage complex datasets, and configure your page layouts to repeat specific rows during printing without complex queries.
- 1. Download WPS Office: Install WPS Office for free and open the WPS Spreadsheet application.
- 2. Open Legacy Files: Click 'Menu' > 'Open' to directly load your Excel 97-2003 (.xls) files without conversion errors or complicated import steps.
- 3. Set Print Titles: Navigate to the 'Page Layout' tab, click 'Print Titles', and select the rows you wish to consistently display at the top of your printed pages.
Frequently Asked Questions
Why can't I open an Excel 97-2003 file in Power Query?
Legacy .xls files can sometimes cause compatibility errors if necessary OLEDB drivers are missing on your PC. Ensure you are using the 'From Excel Workbook' option. Alternatively, open the file normally, save it as a modern .xlsx format, and then import it.
How do I physically duplicate rows in my worksheet?
To duplicate rows in your actual dataset, highlight the row numbers you want to copy, press Ctrl+C, select the destination row number, and press Ctrl+V to paste them. You can also highlight the cells and use the 'Fill Down' (Ctrl+D) command.
What is the difference between duplicating rows and repeating rows?
Duplicating rows creates physical copies of the data inside your spreadsheet's cells, which alters your dataset. Repeating rows (via Print Titles) only visually duplicates those rows at the top of physical printed pages, keeping your underlying data intact.
