Need to regularly import external data into DataVerse table and replace old data

[rharder]

I'm really surprised at how hard this is turning out to be, so maybe I'm approaching this completely wrong, but here goes:

I have three tables that I need to pull regularly from an existing proprietary data source that uses HTTP Basic Authentication. That is the "truth" data.  We want it copied to the DataVerse, so we can do various reports and other Microsoft-y things with it.

I created the three tables in the DataVerse with all the appropriate column types.  If I import the data from a csv or json file on my computer, that works well. Once.

Two problems I'm having:

1) I cannot get anything to import the data with HTTP Basic Authentication, which seems kind of...basic.  I tried making a custom connector, or I think I did, but I still can't see how to get that into the table.  The only Microsoft tool that seems to be able to import JSON from the Web using Basic Authentication is Excel for Windows (I'm on a Mac).

2) I cannot re-import the data from a file and replace existing rows (according to the primary key). "Replace" doesn't seem to be an option anywhere.

Does anyone have any hints for either problem 1 or 2?  Many thanks.

-Rob

[dpoggemann]

Hi @rharder ,

If you are trying to import data from another data source regularly into Dataverse I would look at a few different approaches to accomplish this, in my recommended order:

1. Data Flows - These can pull from many different sources and you can use power query to transform your data on the import.  You will want setup alternate keys on your Dataverse tables (https://www.linkedin.com/pulse/alternate-keys-microsoft-dataverse-kumar-manish-csm-pmp-/) as this will help with the upsert capability when running the records in again so it won't create duplicates.  
2. Power Automate - This is one of my favorite tools and you can do a ton with it including importing records.  You could create a custom connector to your source of truth data source if it does not exist or you can hopefully use one that exists.  Power Automate has a great connector into Dataverse to import data.  Watch limits with this option (https://learn.microsoft.com/en-us/power-automate/limits-and-config) 
3. Azure Data Factory - True ETL type of tool that you can utilize and run on schedules to pull data from your source and push to Dataverse.  I have not used this one as much and you have to watch costs closely as if you run a lot of operations frequently it can get expensive quickly.

[rharder]

Thanks @dpoggemann, I'm going to go try those out right now! -Rob

[rharder]

Umm... OK so I've already tried data flows and PowerAutomate.  Neither one recognize the custom connector that I guess I did build successfully previously.  When I "Test" the custom connector, it pulls in the records.

Any ideas why a custom connector wouldn't show up wherever all those other dozens of connectors are listed?

-Rob

[sudipm]

Hello @rharder 

Cant say without digging deeper why your custom connector does not show up, ensure you followed the guidelines below (assuming your api works with postman or some other web client)
https://learn.microsoft.com/en-us/connectors/custom-connectors/define-postman-collection 

Since you are looking for a quick way to get this done, here is a suggestion, which does not involve custom connectors

Build a new power automate (you can set trigger as scheduled or manual)
Use the HTTP web request step and hit your end point and select authentication type as basic and punch in your auth infos
It should be able to get data from your api
Once you are able to get it, loop through your data and then create/update the records in the table(s) in dataverse which you need to map to.

[rharder]

OK... trying in Power Automate with the flowchart-like tools. I don't see basic authentication, but I guess I can pre-hash the values and manually put them in the http header.

I'll keep learning this flow tool, but it sounds like I need to manually loop, check if a row (primary key) already exists in the table, then either update or add depending on that conditional.  The condition tool doesn't have any obvious way to do something as complex as lookup a row in the dataverse.

This would be so much easier in a real programming language, but I thought the point was that this could be done without programming.

(rant) But is this actually the right track?  There's no easy way to just import data from another data source?  How can this be so hard?  Am I the first person to have to do this operation?

Sorry, it's been a really frustrating journey with these supposedly-powerful Microsoft tools. So far nothing has been straightforward. And I'm not a novice at this kind of work.

-Rob

http://LinkedIn.iHarder.net

[rharder]

And more strangeness, I don't even see my dataverse tables in the Add Row action.

[rharder]

Found the no table problem. It moved me to some random environment. Moved back to the right one, and my table shows up. So now I'm trying Apply to Each and Add a New Row, but that's not it.

This is unbelievable.