Chapter 17. Integration with Other Apps

You can load data from external data sources and save the data into your tables.

You can load data from databases, web pages, and text files using the add-in, or from other sources using Excel or other tools like PowerQuery.

External Data Sources

Let's create a worksheet like integration and run Data Connection Wizard.

You can connect to any supported data source including text files and web data.

Let's select the Web Data provider.

Use Gartle Web Data provider to connect to web data sources in Microsoft Excel

At the following step, let's paste the test URL to load data from a JSON data source:

Paste a target URL to connect

You can test the following URLs:

The SaveToDB add-in automatically parses the pages and retrieves the meaningful data.

You can also customize the parsers. Refer to the documentation.

Let's insert the new connected table at cell B3. You can refresh the data anytime.

Sample of imported data from the web in Excel

The external data source contains the name column. Our dbo69.Companies table contains the Company column.

Let's add the target Company column using the formula like =[@name] and run Data Merge Wizard.

Start Data Merge Wizard

Follow wizard steps and select the target dbo69.Companies table at the following step:

Select the target table to merge data

You can see the generated SQL code used to update the underlying table in a merge mode. Click Execute.

Check generated commands to merge data and click Execute

At this step, you see the execution result. The add-in has updated data. Click Finish to exit the wizard.

Check the execution result

The add-in saves the merge configuration. So, later you can use the Save by Merge item to have the same results.

Use Save by Merge when you configured the target once