Routes with Bing Maps API: what is the correct format of postcodes in Europe ?

Question

I use the Bing Maps webservice from an Excel sheet to calculate the distances between 2 points (post codes only) in Europe. Strangely this works for Belgium but often gives errors for other European countries. See printscreen attached.

The formula I use is:

=WEBSERVICE("https://dev.virtualearth.net/REST/v1/Routes/Driving?o=json&wp.0="&A2&"&wp.1="&C2&"&key=MY_KEY")

(and same between C2 and E2).

Then I extract the distance from the result in columns B and C. The whole table is 12000 lines.

Can anyone help with the expected format of the post code. For example in the first line, I've already tried many variants ("DE", "D", "DEU", "Germany", "Deutschland") but it doesn't work either.

Thanks in advance for your help.

Answer 1

Hi @Jean-Christophe G Greetings! Welcome to Microsoft Q&A forum. Thank you for posting the question here.

I have tested the API through Postman using the Zip codes you have shared and could get the distance results fine without any issues.

Please find the below image showing the response I got from the API when I made the following GET request http://dev.virtualearth.net/REST/V1/Routes/Driving?o=json&wp.0=DE 78315&wp.1=BE 1730&key=<key> to get the distance between DE 78315 and BE 1730

The issue here is related to Excel loading the data from the fetched result. I have tested the Excel with the same inputs and observed the same results. I could not understand the root cause of the behavior. But I can confirm that this is an issue with Excel formatting data and not the API.

I have a found a workaround for this issue. We can use a C# console application to read the data from the Excel file, calculate the distance by invoking the API through the code and update the Excel sheet. Please refer the below code I have used to achieve this.

using System;
using OfficeOpenXml;
using Newtonsoft.Json;

namespace BingToExcel // Note: actual namespace depends on the project name.
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            string filePath = "C:\\Users\\lsayana\\Desktop\\BingAPI.xlsx";
            using (ExcelPackage excelPackage = new ExcelPackage(new FileInfo(filePath)))
            {
                // Get the worksheet you want to read (e.g., by name or index)
                ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets["Sheet1"];

                // Get the value of a specific cell
                object origin = worksheet.Cells["A1"].Value;
                object destination = worksheet.Cells["C1"].Value;

                var client = new HttpClient();
                string URL = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=json&wp.0=" + origin.ToString() + "&wp.1=" + destination.ToString() + "&key=<key>";
                var request = new HttpRequestMessage(HttpMethod.Get, URL);
                var response = await client.SendAsync(request);
                string result = await response.Content.ReadAsStringAsync();;
                var data = JsonConvert.DeserializeObject<dynamic>(result);
                double travelDistance;

                for (int row = 1; row <= 7; row++)
                {
                    //Update column B
                    origin = worksheet.Cells["A" + row].Value;
                    destination = worksheet.Cells["C" + row].Value;
                    URL = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=json&wp.0=" + origin.ToString() + "&wp.1=" + destination.ToString() + "&key=AkPF5f44J1dXvjCy-pxzWEC01YM_viKwSJGv2oTg027evnLmLsq2-M8Bmd2YqrYV";
                    request = new HttpRequestMessage(HttpMethod.Get, URL);
                    response = await client.SendAsync(request);
                    response.EnsureSuccessStatusCode();
                    result = await response.Content.ReadAsStringAsync();
                    data = JsonConvert.DeserializeObject<dynamic>(result);
                    travelDistance = data.resourceSets[0].resources[0].travelDistance;
                    worksheet.Cells["B" + row].Value = travelDistance;

                    //Update column D
                    origin = worksheet.Cells["C" + row].Value;
                    destination = worksheet.Cells["E" + row].Value;
                    URL = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=json&wp.0=" + origin.ToString() + "&wp.1=" + destination.ToString() + "&key=AkPF5f44J1dXvjCy-pxzWEC01YM_viKwSJGv2oTg027evnLmLsq2-M8Bmd2YqrYV";
                    request = new HttpRequestMessage(HttpMethod.Get, URL);
                    response = await client.SendAsync(request);
                    response.EnsureSuccessStatusCode();
                    result = await response.Content.ReadAsStringAsync();
                    data = JsonConvert.DeserializeObject<dynamic>(result);
                    travelDistance = data.resourceSets[0].resources[0].travelDistance;
                    worksheet.Cells["D" + row].Value = travelDistance;

                }
                excelPackage.Save();                

            }
        }
    }
}

Note to replace the filePath with correct file path and provide appropriate API key in the URL. Modify the end limit of the for loop to calculate data for all rows

The above application has the follwoing Nuget package dependencies - EPPlus and Newtonsoft.Json Please make sure to install them in the project directory before executing the code. Here is the resultant Excel with the data generated.

Hope this helps with the next steps. Please let us know your findings and do not hesitate to reach out to us with any additional questions.

---

If the response helped, please do click Accept Answer and Yes. Doing so would help other community members with similar issue identify the solution. I highly appreciate your contribution to the community.