Combine multiple excel files into single text file

Question

Hello,

I have many excel files, each with many sheets, each sheet is almost 1,048,576 rows. These data are exported from reporting tool, where when the data are exported as excel and if the rows exceeds the sheet limit, then the data continue to next sheet (without the header), so is there any method to combine all sheets in all excel files as single text file that can then be handled with Notepad++ or any other editor.

Also, if Power query is used, which I have no much experience with it, can options as remove duplicates used?

Thanks for any help.

Ahmed Moursi

Answer 1

Hi,

Yes, You can use Power Query to remove duplicates from the combined data before saving it as a text file.

You can remove duplicates by choosing the column(s) you want to remove them from, going to the "Home" tab, and selecting the "Remove Rows" option. You can then select "Remove Duplicates" to get rid of any duplicate rows according to the selected column(s).

Also, you can use Power Query in Excel to combine all sheets from multiple Excel files into a single text file. Here's how you can do it:

1. Open a new Excel workbook where you want to create the combined text file.
2. Click "Get Data" in the "Get & Transform Data" section of the "Data" tab.
3. Select "From File" and "From Folder" in the drop-down menu.
4. Locate the folder containing your Excel files and choose it. Make sure "Combine & Transform Data" is selected, then click "OK".
5. You can see a preview of each file in the folder in the Power Query Editor. To combine all the files, click the "Combine" button.
6. Select the desired Combine operation (such as Combine & Load, Combine & Transform, etc.) in the "Combine Files" dialogue and click "OK".
7. The selected Excel files' sheets will now be loaded by Power Query and combined into a single table.
8. To load the combined data into a new worksheet in Excel, click the "Close & Load" button.
9. When the data is loaded, you can select "Save As" and the text file format (.txt) in the new worksheet to save the data as a text file.

Let me know if you have any doubts.

Best Regards.