Using Canadian Postal Codes to Create a Map in Excel

If you have a list of postal codes and would like to those postal codes to create a map in Excel, you may be disappointed. If the program succeeds at giving you anything at all without the dreaded “Map charts need geographical data such as country/region, state/province, county or postal code.” message, you probably won’t be happy with the output. The message even suggests using postal codes, so why is it so hard to use them? I can’t say, but there is a data adjustment you can make for more satisfactory results.

Continue reading “Using Canadian Postal Codes to Create a Map in Excel”

Save Excel Table to JSON File using VBA and VBA-JSON

While importing JSON files to an Excel Table is easy using a Get and Transform query, going the other way and saving an Excel table as a JSON file is unfortunately not as simple. Luckily, with the code below, it’s easy to save the first Excel table in your sheet as a JSON file.

Continue reading “Save Excel Table to JSON File using VBA and VBA-JSON”

Excel Import PDF Using Get & Transform Query Connection

Microsoft has introduced a feature in Microsoft Excel 365 that makes it easy to import data from PDFs on your computer or directly from the web. The feature has its quirks but will work for many applications. In this article, I will show you how to check if the feature is available in your version of Excel and import data from a PDF on the web.

Continue reading “Excel Import PDF Using Get & Transform Query Connection”

Excel Import MySQL Table with ODBC Database Query

In this article, you will learn how to import a MySQL Table from the database into an Excel table using an ODBC Database Query. If you are looking to do the reverse and import an Excel table into any SQL database (including MySQL), try my other tutorial here. This tutorial should work on Microsoft Excel 2016 or greater with Microsoft Windows.

Continue reading “Excel Import MySQL Table with ODBC Database Query”

Excel Live Currency Exchange Rate with Web Query

If you want to create a table of exchange rates that can be updated live in Excel, you have come to the right place. In this article you will learn how to retrieve exchange rates using a Get & Transform web query, also known as a PowerQuery query. You will also learn how to turn that query into a function that can be used in another query to populate a table of exchange rates.

The API I will use to do this is the currency.com API. You can find the documentation at https://currency.com/api. For a free API I find this is pretty good. There is no need to authenticate yourself to get a rate. More expensive APIs such as XE.com might be of interest if you need to retrieve more niche currency pairs or more recent data.

Continue reading “Excel Live Currency Exchange Rate with Web Query”

Microsoft Outlook Export Contacts to Excel (CSV)

In this article we are going to export contacts to an Excel sheet. The resulting file is a CSV file that can be opened in Excel or used for another purpose, such as importing in another program. If you wish to save it as an XLSX file (regular excel file format), simply open it in Excel and save it as a regular file. To get started, open Outlook. Locate the folder containing the contacts you want to export if you have multiple.

Showcasing contacts to export in Outlook interface.
The folder containing the contacts to export is highlighted. Remember its name so you can select it in the export wizard.
Continue reading “Microsoft Outlook Export Contacts to Excel (CSV)”

Microsoft Outlook Import Contacts from Excel or CSV

In this article we are going to import contacts to Outlook from an Excel file. My file is currently in .xlsx format so we will first convert it to .csv format. Then, we will map it and import the contacts.

excel file with contacts to import
The Excel file with contacts to import
Continue reading “Microsoft Outlook Import Contacts from Excel or CSV”

Import an HTML Table into Excel

In this article, I will show you how to import an HTML table into Excel. This will work for any data from a local HTML file or online URL that is stored in a table. I will import the HTML table in a way that the data can be updated if it changes, which is particularly valuable for online data. This will be done using a Get & Transform Query. As such, this method is only compatible with the Windows version of Microsoft Excel.

Continue reading “Import an HTML Table into Excel”

SQL Import Excel File to Table with Python Pandas

If you’re looking for a simple script to extract data from an excel file and put it in an SQL table, you’ve come to the right place. In this article, I will show you how to use python pandas and sqlalchemy to import an excel file to a SQL database (MySQL) in a free, fast and flexible manner. The same method also works for SQLite or other SQL databases, but the connection details will be different.

Continue reading “SQL Import Excel File to Table with Python Pandas”