Importing data into Excel automatically ...

Question

This may not be possible!

I have an Excel table that has a number of columns - each record (row) has a unique identifier - but once in the table every record must be persistent ie an further records are appended to the table ...

Periodically I receive another Excel table from a third party system - again it has multiple columns - and each record has a unique identifier which is the same as the one in my table - the table should contain only new records, but that's not always the case ...

In my table some of the columns are populated by the update table (others are updated directly by the user) - but the update table also contains columns I'm not interested in ...

Currently it's a pretty laborious manual task to get the new records (checking they're not already in my table!) - then copying/pasting the data from the new records from the update table into my table ...

I hope that all makes sense so far???

What I really want to do is use the various functions in Excel that allow me to point my table at a new update table and automatically import - and append - the new records into my table ...

If it's possible I'm pretty certain it's going to involve some pretty complex stuff, but I am a reasonably advanced Excel user (but haven't come across this requirement before!) ...

Any Ideas???

Thanks

Answer 1

Lz

I went back to the responses to my previous post (many thanks for pointing me at them) and they actually gave me the answer to this post!!!

I'm still missing a few pieces before I solve my first post, but now I understand Power Query far far better, I suspect I will be able to do something us PQ.

Thanks for your replies/help.

PS; "Notify me when someone responds to this post" doesn't appear to work! Now I know that, I will manually check any future posts.

Answer 2

Hi Adam

My previous reply was to Renzo but now that you've written to me - as I'm polite - I won't leave you unanswered. My sincere apologies in advance for being straightforward but English isn't my native language

On your previous case 2 persons provided guidance and a 3rd was ready to assist assuming you would provide the same kind of information that is required on this one. A couple of weeks later they haven't received a single word of feedback (whatever it is).

It's very personal but I don't see a forum/community as a one stop shop if you see what I mean. I totally understand people who can't/don't want (or don't have time) to contribute but IMHO their minimum contribution should be to provide feedback (even if not positive) and to mark as answer the solutions they get as this could help the few number of people who search here and there before asking something that's already been answered, sometimes n times

Re. I can't see a way to attach examples

When you create (or reply to) a thread here there's a toolbar at the top. The squared icon below allows uploading a picture, the previous one to post an hyperlink

And, if you want to upload and share a file on OneDrive => How To here

Answer 3

Lz., many thanks for your reply ...

I can't see a way to attach examples, so let me see if I can do it with words?

Using your terminology ...

• the CurrentTable and UpdateTable are in separate Excel files
• the CurrentTable contains columns that are ONLY populated from the UpdateTable and columns that are ONLY populated (subsequent to a new record coming across) by users ... the users NEVER update any of the columns populated by the UpdateTable
• the unique identifier for each record comes from the UpdateTable ie when a new record is imported into CurrentTable, Column A is populated with the value from Column A in the UpdateTable
• the UpdateTable is supposed to ONLY contain new records, but it often contains a record that has already been imported into the CurrentTable ... I only want to import new records from each UpdateTable (but thinking aloud ... if I can check whether a record has changed or not and do something with it if it has, that might solve another challenge I have!!!)
• but it is MANDATORY that the UpdateTable/NewRecord is APPENDED to the CurrentTable, because the columns in CurrentTable that are populated by the users are dynamic ie they are populated (and amended) over time

I think that's it, but please ask again if I've missed anything?

Answer 4

Hi Renzo

Don't get me wrong please ;-) Power Query might be an option... There's an important point in the above description => In my table some of the columns are populated by the update table (others are updated directly by the user).

So, it's not necessarily only a question of appending only new records (would be too easy) from the Update Table, i.e. What does populate mean (replacement if data has changed)?

IMHO, to determine the feasibility with PQ one needs to understand how CurrentTable must look like after "combining" the information from the UpdateTable

hope this makes sense

Answer 5

Dear AdamBC,

Thanks for participating in the Microsoft Community. I'm an Independent Advisor and I'll help you.

If you want to update information between different Excel files or others databases, I suggest you to connect all your data with PowerQuery, now is also know as "Get & Transform"

https://support.office.com/en-us/article/gettin...

PowerQuery is a grate tool to combine data from multiple data sources:

https://support.office.com/en-us/article/combin...

Do not hesitate to ask if you need more information.

I hope it has been useful to you.

Regards,

Renzo.