Join Microsoft Excel SpreadSheet (XLS) files together manually
Note that the following procedure can now be automated using Monkey Merge|
Before attempting the following procedure, we recommend that you back up your xls files.
Note that the final joined xls file will not contain any of the formatting from the constituent xls files.
You will need Microsoft Excel installed for this procedure to work.
Summary Of Procedure:
1. Convert your xls files to plain text format.
2. Use FileMonkey to join the plain text files together.
3. Convert the joined file back to xls format.
4. Verify the resulting xls file.
1. Convert your xls files to plain text format:
For each of your xls files, you must:
Now, repeat the above instructions for each xls file.
- Open the file using Microsoft Excel.
- Select "File" and then "Save As" from the Excel menu bar.
- This will open the "Save As" window.
- Select "Text (Tab delimited) (*.txt)" from the drop down menu.
- Type the name that you wish to save the file as (Example: Book1.txt).
- Navigate to the location that you wish to save the file to.
- Click on the "Save" button.
- Excel will display an information prompt about multiple sheets.
- Click the "OK" button.
- Excel will then tell you that features (such as fonts, colors etc) will be lost.
- Click "Yes"
- You have successfully saved the currently displayed sheet in plain text format.
- Repeat this procedure for each sheet in the xls file.
2. Use FileMonkey to join the plain text files together:
- Close Excel.
- Right click on the FileMonkey icon and select "Run as administrator".
- Navigate to the folder that you saved the plain text files to.
- Target the plain text files (targeting tutorial.)
- The text files should now be displayed in the FileMonkey main window.
- Click on the "Split Join" button (bottom right of main window).
- In the "Split Or Join" window, select the "Join" option.
- Then select the "Merge files in to one file named" option.
- Un-check (clear) the "Include sub-folders" check box.
- Click on "Start" to start the operation.
- When the operation completes, close all open FileMonkey windows.
3. Convert the joined file back to xls format:
- Open Excel.
- Select "File" and then select "Open" from the Excel menu.
- Select "Text" from the "Files Of Type" drop down menu.
- Find the new joined file that you created and select it (left click it).
- Click on the "Open" button.
- Excel will now display a text import wizard.
- On the first screen, select the "Delimited" option.
- Click the "Next" button.
- Check (tick) the "Tab" check box and then click the "Next" button.
- Click the "Finish" button.
- Select "File" and then select "Save As" from the Excel menu.
- Select "Microsoft Excel Workbook" from the "Save as type" box.
- Navigate to the folder that you wish to save the file to.
- Click the "Save" button.
- Close Excel.
4. Verify the resulting xls file:
As formatting is lost during this process, you may want to verify the resulting file. Open the file in Excel and browse up and down to skim the data for any errors that may stick out. Count the number of entries or sum columns and rows to determine if the numbers are what you expected them to be.
The data items may not be in the order that you require (for example: alphabetic). You can now use Excel to re-order the data as required.