Importing Data from an Existing Excel File into Another file

Question

I have two Excel files with the same headers. I want to import all the data from one file into the other but I don't want the header from the second file. Both files are huge, each has over 100K rows. I created two test files to experiment with and I almost have them doing what I want but not quite. I can import the second file into the first but I get the headers from the second file as a row in the first and when I click on that row to delete it, it won't let me (see first picture below). I'm currently using:

Data>Get Data>From File>From Workbook

That gives me a file browser, I navigate to the file to import and select Import. I then select Sheet1 and at the bottom click on the drop down menu on Load and select Load to. That gives me an Import Data dialog (see second picture, note: ignore the cell number below the existing worksheet, that was just an effect of when I did the screen snapshot, when I do the import I have the cursor positioned in the first column and row after the current data, i.e., after Case 4 in row 6 column A).

I select Table and Existing worksheet. I've tried both checking "Add this data to the Data Model" and  unchecking it and in both cases it still adds the header as a row that I can't get rid of.  All I want to do is to add all the rows as new rows at the end of the first worksheet (see the third picture for an example of the desired outcome). I can get it this way with my toy example by just copy pasting but there is too much data in the real files to make that a desirable option. 

         

Answer 1

Hi

You cannot prevent the "load" of the header row [Case, Name, Favorite pet] (row #6 in your 1st picture) when getting the data (from your other file) with Get & Transform (Data > Get Data...) as this is the Table header row

You can possibly proceed as follow:

1/ Click somewhere in the imported Table

2/ Uncheck 'Header Row' in the Table Style Options (in Table Design tab) > The row becomes "blank"

3/ Delete that "blank" row

IMHO this is a dirty workaround that might cause you other problems. If it was me I would combine/append (still with Get & Transform) the 2 files/sheets as a single table. In that case you only have one header to at the top of the resulting import and all data are in the same table. Hope this makes sense

Answer 2

Hi MdeBellis:

Based on your description, we understand that you want to integrate two tables with the same header.

Thank you for providing your test information and results. We kindly recommend that you first use Power query to create two queries based on your tables then use Append queries to create a new query that contains all rows from a first query followed by all rows from a second query.

About append queries：

The append operation is based on the names of the column headers in both tables, and not their relative column position. The final table has all matching columns from all tables appended. If the tables do not have matching columns, null values are added to the unmatched column. The tables will be appended in the order in which they are selected, starting with the Primary table.

The following are my test results for your reference only:

Using Power query to create two queries: Data>Get Data>From File>From Workbook.

The two tables have the same header:

![](https://learn-attachment.microsoft.com/api/attachments/a9d41704-bdc2-4eae-a0b2-26893f04aee4?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/0eeb5bae-8a36-412c-9216-0a8cdd6d63ee?platform=QnA" rel="ugc nofollow">

Select the two queries to be merged:

![](https://learn-attachment.microsoft.com/api/attachments/be62d187-92e5-4af5-b1d6-617cf0f81db3?platform=QnA"https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fcombine-multiple-queries-power-query-16b1421c-9708-466a-8d6e-30a324949722&data=04%7C01%7Cv-zhangjian%40microsoft.com%7C4064e1e9c683403bfab808d90a0b83f8%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637551866845843961%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=E5suqYN0HJVQCILoqdGsa%2BgxEpEPUU2YgVMJhU4nf%2BE%3D&reserved=0" rel="ugc nofollow">Combine multiple queries (Power Query) - Excel (microsoft.com)

Hope this will help you.

Tin

Answer 3

Thanks, that worked!

Michael

Answer 4

Thanks for the reply. The first reply gave me what I needed to make this work but yours also provided good info. 

Michael

Answer 5

Glad we could help & Thanks for posting back Michael