Load data from excel (3 tables only see the 2)

[cmano]

Hello guys.

I have 3 tables at excel.

I load the data drag and drop.

When i open the Table Viewer i only see the 2 tables.

Fyi.

The 2/3 tables have the same headers etc.

Best regards.

[stevedark]

Hi @cmano 

Two of the tables have auto-concatenated, as the fields are exactly the same. The other table is slightly different, perhaps a column extra, missing or perhaps named differently.

Having identical tables concatenating is a good thing, as you don't get weird associations between data in different tables.

You want to ensure the columns in all tabs are the same.

You might also want to consider adding a new column with a fixed value for each sheet. This can be done simply in the load script editor, like this:

LOAD
  'Sheet 1' as [Sheet Name],
  *
FROM [lib://DataFiles/MySpreadsheet.xlsx] (ooxml);

You will then have a new field that tells you which data came from where. I'm sure you can do this in the drag drop environment also - but I tend to use the script editor...

Hope that helps.

Steve

[cmano]

Hello @stevedark .

I tried as you mentioned and i have the below as you can see on attached images.Before

That was the initial status.

Then i add this on script editor as you said on both tables (S007 & S008) ( i am not sure if i did this right.

Script addAfter

That was the result...is that we expected to see?

thank you for your time.

[stevedark · Accepted solution]

Hi @cmano 

This is showing the tables not concatenating, because you have a different field in each table.

In each case change the fieldname to be consistent, i.e.

'S007' as [Source Table],

This should then get you back to having just two tables in the data model.

I believe that all three tables from the Excel spreadsheets were originally concatenating correctly. The other table, REDCARDS, is most likely coming from somewhere else.

Check your load script for additional bits of load that you are not expecting. In the load script editor you can have many tabs on the left hand side, so check each of those.

If the rogue table was created by the Data Manager you will need to unlock the script before you can edit it.

Hope that helps.

Steve