how to merge multiple files in Excel?

[Jason Starkey]

hope it helps

 

[tazarbm]

hope it helps

it didn't! 2,5 minutes into the clip and I began snoring as I got lost in the details and explanations

However...

Click "new query" tab under data, power query is in 2016 version, and there is download version if it is actually excel 2013? Update me!

this worked... kind of... because it truncated the data as soon as it reached 999 rows, as you can see in this screenshot:



I greyed out the content of the cells because they're important and I don't want anyone to see that content, but this shouldn't affect the help you're trying to give me....

As you can see at the bottom of the page (right above the Windows search button in the taskbar) it says "3 columns, 999+ rows".

The 31 files that I wanted to merge (yes, there were 35 but I removed 4 of them as I didn't need them anymore) each consists of 50 rows of data (+1 row which is the header of the file, so 51 rows in total), so this means 51 rows x 31 files = 1581 rows that should have been merged, but the operation stopped at 999 rows, so I don't know what to do next...

I'll leave everything opened until you reply with a solution. If none comes by the time I go to sleep (in about 6 hours) I don't know... I guess I'll close it and start over tomorrow as I now know what to do... and it's pretty easy too (definitely easier than what the guy in the video mumbled for the first 2-3 minutes that I watched)...

 

[Alpha1404]

As you can see at the bottom of the page (right above the Windows search button in the taskbar) it says "3 columns, 999+ rows".

Hello,
This is normal behavior: when you open Power Query, it doesn't show you everything. It's a preview.
You don't need to scroll endlessly.
Go to the top left corner and click on Close & Load.
It will open a sheet and normally all the data will be loaded.

 

[Jason Starkey]

it didn't! 2,5 minutes into the clip and I began snoring as I got lost in the details and explanations

However...

this worked... kind of... because it truncated the data as soon as it reached 999 rows, as you can see in this screenshot:

View attachment 479410

I greyed out the content of the cells because they're important and I don't want anyone to see that content, but this shouldn't affect the help you're trying to give me....

As you can see at the bottom of the page (right above the Windows search button in the taskbar) it says "3 columns, 999+ rows".

The 31 files that I wanted to merge (yes, there were 35 but I removed 4 of them as I didn't need them anymore) each consists of 50 rows of data (+1 row which is the header of the file, so 51 rows in total), so this means 51 rows x 31 files = 1581 rows that should have been merged, but the operation stopped at 999 rows, so I don't know what to do next...

I'll leave everything opened until you reply with a solution. If none comes by the time I go to sleep (in about 6 hours) I don't know... I guess I'll close it and start over tomorrow as I now know what to do... and it's pretty easy too (definitely easier than what the guy in the video mumbled for the first 2-3 minutes that I watched)...

That is just the preview, I think they are all there. I think you change "column profiling to top 1000 rows" to "all" to show them in preview, not sure so update me!

Just seen above message, yes I think he is right

 

[tazarbm]

Hello,
This is normal behavior: when you open Power Query, it doesn't show you everything. It's a preview.
You don't need to scroll endlessly.
Go to the top left corner and click on Close & Load.
It will open a sheet and normally all the data will be loaded.

That is just the preview, I think they are all there.

Just seen above message, yes I think he is right

yeah, the Close & Load did the trick...

I think you change "column profiling to top 1000 rows" to "all" to show them in preview, not sure so update me!

I didn't change anything, I simply combined the binaries and then scrolled all the way down until I didn't know what else to do because the software stopped showing me all of the entries, but then you guys told me to click on Close & Load (which I did) and then, I scrolled down to the bottom of the page to see whether all 1581 rows are there, but there were only 1549 and then - as I tried to scroll back up so I can see if I figure out what's wrong - my Windows (in virtual machine by the way) instantly restarted

So, I logged back in, did the whole get data from query + combine binaries + close & load trick all over again, and it still shows only 1549 rows instead of 1581, but that's because - from what I'm noticing - the headers of the files were either removed, or merged into some of the cells, because I can see the text of column 1 being appended at the end of the cell value of column 3.

For example, let's say that the 3 header columns are...

- Name / Age / Gender

and then the cells have the values

- Robin / 23 / Female
- Jack / 19 / Male
- Sally / 20 / Female
- etc...

Well, after the first 51 rows of data (so, after the first full file), the word "Name" gets added after the word "Female" in the next file, so after every 51 rows, the 2nd cell of column 3 (so R2C3) contains the word Name.

And this tells me that Excel appended R1C3's text at the end of R2C3's text, while completely deleting the header row of each file. That's why I'm getting 1549 rows instead of the usual 1581.

And to be honest, I don't need those headers, so I would like them removed from the finalized workbook. So, how can I do this?

Yes — you can merge multiple Excel files together. Here’s a general, no-specifics method:

1. Collect all the files you want to merge into one folder.
2. Use Excel’s feature (or your OS) to open all files and copy consolidated data into one master workbook.
3. If the sheets have the same structure (same columns), you can append them easily.
4. Clean up duplicates and misaligned formatting after merging.
5. Save the merged file under a new name so you keep originals intact.

If you tell me which version of Excel or OS you’re using, I can guide you step by step.

thanks! I will try this after I am done with Jason's help

 

[Jason Starkey]

yeah, the Close & Load did the trick...


I didn't change anything, I simply combined the binaries and then scrolled all the way down until I didn't know what else to do because the software stopped showing me all of the entries, but then you guys told me to click on Close & Load (which I did) and then, I scrolled down to the bottom of the page to see whether all 1581 rows are there, but there were only 1549 and then - as I tried to scroll back up so I can see if I figure out what's wrong - my Windows (in virtual machine by the way) instantly restarted

So, I logged back in, did the whole get data from query + combine binaries + close & load trick all over again, and it still shows only 1549 rows instead of 1581, but that's because - from what I'm noticing - the headers of the files were either removed, or merged into some of the cells, because I can see the text of column 1 being appended at the end of the cell value of column 3.

For example, let's say that the 3 header columns are...

- Name / Age / Gender

and then the cells have the values

- Robin / 23 / Female
- Jack / 19 / Male
- Sally / 20 / Female
- etc...

Well, after the first 51 rows of data (so, after the first full file), the word "Name" gets added after the word "Female" in the next file, so after every 51 rows, the 2nd cell of column 3 (so R2C3) contains the word Name.

And this tells me that Excel appended R1C3's text at the end of R2C3's text, while completely deleting the header row of each file. That's why I'm getting 1549 rows instead of the usual 1581.

And to be honest, I don't need those headers, so I would like them removed from the finalized workbook. So, how can I do this?


thanks! I will try this after I am done with Jason's help

I should have said "you can change" not "you change" sorry for confusion.

Find the step where headers are promoted (it will look like "Promoted Headers"). Either delete or move this step so that headers are not promoted for each file, only for the first file.

Add a step to filter out any rows where the value in the "Age" or any other expected field equals the header text (e.g., filter out rows where Column1 = "Name" or Column2 = "Age")

Not an expert on excel these days, sorry, had to look it up

 

[CPANathan]

Posting to follow, can surely benefit from this read

 

[tazarbm]

I should have said "you can change" not "you change" sorry for confusion.

Find the step where headers are promoted (it will look like "Promoted Headers"). Either delete or move this step so that headers are not promoted for each file, only for the first file.

Add a step to filter out any rows where the value in the "Age" or any other expected field equals the header text (e.g., filter out rows where Column1 = "Name" or Column2 = "Age")

maan, this is so complicated... I re-created the sheet 3 times, and I'm not seeing anything about headers anywhere, I swear. I managed to remove the 1st row, but only from the first file (probably because it was selected or something)...

Anyway, something came up so I don't have time to deal with this at the moment, but I promise that in a few hours I'll try again and - regardless of the outcome - I will be back with the results

Not an expert on excel these days, sorry, had to look it up

if you're not an expert then what am I?

No worries, man! We'll figure out how to make it work eventually... unless I run out of patience, that could happen too

Cheers! I'll be back again in some hours (can't say how many just yet as it depends on when I'm done with what I'm doing at the moment, but I guarantee that I'll update y'all with the new results, no matter what those are)

 

[Godwin_anis]

Hi,

I have about 35 excel files whose data I need to merge into one, but I've no clue how to do this... Ok, I can copy-paste it, obviously, but I'm curious whether there's a more elegant way of doing it

So, do tell if you know how to do this!

Thanks!

PS: yes, the file header is always the same across all 35 files, and even the number of rows and columns are the same (50 rows + 2 columns), only the data differs, and I need to move this data from 34 files into only 1 so that I can organize my work a little bit...

You can use Power Query in Excel, It’ll automatically combine all 35 files with matching headers into one sheet.

 

[mrsocialselling]

their are online tools, search for Merge multiple CSV files online

 

[Cyber Meta Agency]

You an use tool Aspose online for free! good luck to you.

 

[tazarbm]

I should have said "you can change" not "you change" sorry for confusion.

Find the step where headers are promoted (it will look like "Promoted Headers"). Either delete or move this step so that headers are not promoted for each file, only for the first file.

Add a step to filter out any rows where the value in the "Age" or any other expected field equals the header text (e.g., filter out rows where Column1 = "Name" or Column2 = "Age")

Not an expert on excel these days, sorry, had to look it up

I'e run out of patience for this geeky stuff, I go manually copy-paste everything until I get blue in the face and vomit, and I'll still be happier than if I had to learn coding and programming in order to merge some files...

Any mod reading this, please lock this thread! Thank you!

PS: thanks for all of your support everyone, I'm closing this not because of you (you were kind enough to try and help me), but because I'm not cut out for this techy stuff, so I'd rather do it tediously (but at least I know it will work). Peace!