Let’s solve a real task, using tricks learned on this channel with AL and Business Central

In this video, I use a bunch of tricks shown in other videos to import currency exchange rates from the Central European Bank. A fun exercise, come check it out:

https://youtu.be/1AWaFffdIMM

In this video, Erik takes on a real-world challenge: importing historical exchange rates from the European Central Bank (ECB) into Business Central — using only techniques that have been previously covered on his channel. The result is an elegant solution in under 70 lines of AL code that downloads a ZIP file from the web, extracts a CSV, parses it, and populates the currency exchange rate tables.

The Challenge

The European Central Bank publishes a ZIP file containing historical exchange rates for dozens of currencies against the Euro. The URL looks like this:

https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip

Inside the ZIP file is a CSV with a somewhat unconventional format:

  • The first row contains currency codes (USD, JPY, GBP, etc.) — acting as a header but also containing data
  • Each subsequent row starts with a date, followed by the exchange rate for each currency
  • Some values contain “N/A” for unavailable rates
  • There’s a trailing comma creating a blank column at the end

The goal: get all of this data into Business Central’s exchange rate module, creating currency records as needed along the way.

Step 1: Downloading the ZIP File with HttpClient

The first step is to use the HttpClient to download the file from the ECB. This is about as simple as web service consumption gets — no REST APIs or authentication, just downloading a ZIP file.

HttpClient.Get('https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip', Response);
Response.IsSuccessStatusCode();
Response.Content.ReadAs(InStream);

The key concept here is that ReadAs(InStream) doesn’t actually read the data anywhere — it connects the InStream as a pipe to the downloaded data sitting in memory, ready to be pumped somewhere else.

Step 2: Extracting the CSV from the ZIP

Since the downloaded file is a ZIP archive, we use the Data Compression codeunit to open it and extract the contents:

Zip.OpenZipArchive(InStream, false);
Zip.GetEntryList(FileList);
Zip.ExtractEntry(FileList.Get(1), OutStream, LengthOfCSV);

Erik explains the stream pipeline clearly: we connect an InStream to the downloaded data, hand it to the ZIP library, then ask the ZIP library to write the extracted file to an OutStream. That OutStream is connected to a TempBlob, which serves as an in-memory container for the CSV data.

TempBlob.CreateOutStream(OutStream);
Zip.ExtractEntry(FileList.Get(1), OutStream, LengthOfCSV);

Step 3: Loading Data into the CSV Buffer

The CSV Buffer table is a powerful tool for working with CSV files in AL. Each field in the CSV becomes its own record, essentially giving you spreadsheet-like cell access to the data.

TempBlob.CreateInStream(CSVStream);
CSV.LoadDataFromStream(CSVStream, ',');

Erik emphasizes that the CSV Buffer table should always be made temporary — Microsoft hasn’t marked the table type as temporary yet since it predates that feature, but you should always declare it as such.

Step 4: Processing by Column Instead of by Row

Here’s where the approach gets clever. Since the CSV Buffer gives cell-level access (by line number and field number), Erik processes the data by column rather than by row. Each column represents a currency, and each row within that column is a date’s exchange rate.

for Column := 2 to CSV.GetNumberOfColumns() do begin
    CurCode := CSV.Value(1, Column);  // Get currency code from header row
    
    if CurCode <> '' then begin
        // Create currency if it doesn't exist
        if not Currency.Get(CurCode) then begin
            Currency.Code := CurCode;
            Currency.Description := CurCode;
            Currency.Insert(true);
        end;

        // Process each date row for this currency
        for Line := 2 to CSV.GetNumberOfLines() do begin
            // Get the date from column 1
            ExchangeRate."Currency Code" := Currency.Code;
            Evaluate(ExchangeRate."Starting Date", CSV.Value(Line, 1), 9);
            
            // Get the exchange rate value
            ExchangeRate."Exchange Rate Amount" := 1;
            if Evaluate(ExchangeRate."Relational Exch. Rate Amount", CSV.Value(Line, Column), 9) then begin
                ExchangeRate.Validate("Relational Exch. Rate Amount");
                if ExchangeRate."Relational Exch. Rate Amount" <> 0 then
                    ExchangeRate.Insert(true);
            end;
        end;
    end;
end;

Key Tricks Referenced from Previous Videos

Erik deliberately uses only techniques covered in earlier videos on his channel:

  • HttpClient — Featured in dozens of videos about calling web services from AL
  • Streams (InStream/OutStream) — Covered in a dedicated deep-dive video about how streams work as pipes between data sources
  • ZIP file handling — The Data Compression codeunit and working with ZIP archives
  • CSV Buffer table — A flexible way to parse and access CSV data cell-by-cell
  • TempBlob — The in-memory blob storage pattern for working with file data
  • Records that don’t exist — The pattern of using if not Record.Get() to create records on the fly
  • Evaluate with format 9 — The magic number 9 tells Evaluate to use XML/ISO format for dates, making it locale-independent. This handles the YYYY-MM-DD date format from the ECB file without worrying about regional settings
  • Handling N/A values — Since some exchange rates contain “N/A”, the Evaluate call simply fails for those entries, and the if Evaluate() pattern gracefully skips them

The Result

After running the import, Business Central’s exchange rate tables are populated with data going all the way back to 1999 up to the most recent available date. The exchange rates represent how much of each currency you get for one Euro.

With approximately 40 currency columns and over 6,300 date rows, the solution inserts a significant number of records — all processed from a single button click on the Currencies page.

Summary

In under 70 lines of AL code, Erik demonstrates a practical, real-world integration that chains together multiple fundamental Business Central development techniques: downloading files via HTTP, working with ZIP archives, parsing CSV data, handling date and number formats safely, and creating records on the fly. The elegance of the solution lies in how naturally these building blocks compose together — each one simple on its own, but powerful in combination. It’s a great example of how mastering the fundamentals pays off when tackling real tasks.

Picture of hougaard

hougaard