Transferring Navision data to Excel

Estimated reading time: 32 minutes

Navision has always been able to transfer its data to Excel very quickly and easily - even with the native Navision version, i.e. between Navision 2.00, 2.01 and the 3 version up to 2009R2. Since Microsoft Dynamics NAV 2013 and subsequent RTC versions, this has become increasingly easier. Until the web-only version, where it is more complicated again. Microsoft...

Specifically prevent the copying of Navision / Business Central data

Yes, that can also be a motivation! Perhaps you don't want your employees to be able to simply copy all special prices with their customer names in plain text to Excel, or that the trainee can just copy all suppliers with the respective articles and purchasing conditions into an e-mail? This was only recognized as a problem by Microsoft very late, and depending on the Navision / Business Central version, this data extraction was sometimes made more difficult, sometimes simplified, sometimes provided with rights, sometimes completely released again. An important argument behind this has "always" been that data theft cannot be prevented anyway; if necessary, an employee can simply take a photo of their screen or simply copy the necessary data with paper and pencil. So far, so true... But this kind of behavior is conspicuous and has a time problem. An employee can only steal data in a "mechanical" way to a limited extent (by pulling it off), and the behavior can be noticed and countermeasures can be taken.
Quickly copying tens of thousands of data records in just a few seconds, saving them in Wordpad, Excel or wherever, transferring them via USB stick, USB camera, USB cell phone, caching them in the cloud: there is simply more and faster. The legally enforced data theft via state authorities is also interesting... When exactly did it become fashionable again to use police force to make us trust our own state, which obviously trusts us the least?Shouldn't we have had these gray times behind us for many decades? Talk to me if you want or even need to restrict data extraction in your company. At the end of the linked article you will also find documents against government-enforced data extraction. For the Classic Client, there are a few crutches that can help.

Clipboard

Possible since Windows Navision version 1.3, until today.
Lists: (customer list, article list, vendor list, G/L account list...) Set desired filter, CTRL+A ("Select all"). Then: Native Navision: CTRL+C ("Copy"). Navision RTC: Shift+Ctrl+C. The list will be in the clipboard after a short time. Depending on the number of data records and the flow fields they contain, "a short time" may well be a few minutes. Incidentally, it is generally not a good idea to have flow fields in lists. However, this is part of the topic of performance optimization, for which there will be another guide.
Cards: (customer card, vendor card, article card...): This ONLY works in native Navision, i.e. up to Navision 2009R2. Not RTC. Not web client.

The screenshot shows a special variant that is rarely used but is good to know: You can also select and copy a card in text form in order to paste this data record formatted into Word! It is more common to copy from lists from Navision Financials Attain/ Microsoft Business Central 365 to Excel, as described above.

In the target program, i.e. Excel, Libre Office Tables, Google Tables, Word, Documents or wherever, the data is then simply inserted with Ctrl+V.
Tip if you have columns with leading zeros, e.g. zip codes:
Immediately after inserting, select these column(s) (CTRL+spacebar), call up "Format" (CTRL+1) and call up the "Text" format. Repeat this for all critical columns (i.e. columns with data containing leading zeros 0).
Then go to cell A1 at the top left (CTRL+Pos1) and paste exactly the same clipboard again (CTRL+V): Voila, all leading zeros are now retained!

Navision Send function

The clipboard has always been a bit slow. There are actually massive technical reasons for this: Navision or Business Central has to prepare a lot of data for the clipboard, in different formats! Navision cannot know where the files are to be inserted at the end. In the native client, there was therefore a new function from the 2009 (Navision 6.0) version: Transfer data or send data. The format templates were used for this. You had to choose Word or Excel (or a "something" that was practically never used) as the destination. This allowed Navision to prepare the data directly for the correct destination, which was much faster. As a rule of thumb: If filling the clipboard took about 1.5 minutes (~100 seconds), sending the same data took about 10 seconds (1/10 of the time). And it was also nicely formatted using the adjustable format templates, including leading zeros, e.g. in article numbers or zip codes. The problem with missing or swallowed zeros in G/L accounts and customer numbers has finally been solved. And a few formatting bells and whistles have also been added. I don't think any other ERP system can transfer any data to Excel or Word more beautifully or universally. Editing and extending the format templates is a separate topic. If you need instructions on how to customize and create style sheets: Contact me! Please also read my warning about customizing and creating style sheets below.

Technically, this "Send to" function is no different to a mail merge in Word or an XML import in Excel. Codeunit 403 creates the necessary data and then enriches it with the above-mentioned sales data, for example, Word or Excel take the stylesheet (the "format template") and then apply the XML data transferred to it - and the finished Word or Excel document is ready. Incidentally, this is exactly the same technique used by Business Central up to the current version. With the same disadvantages, but more support. A little more sophisticated, but there's not that much new in the new Navisons. Just as an aside.

In both cases, the shortcut CTRL+E applies. Of course, these templates can also be customized, even separately for each page. So the article list could be transferred with the tabular paper effect (the older among my readers will still remember this endless paper for dot matrix printers...), but the customer list without the colorful frills. Please note that the format template shown for a Navision offer in Word arouses certain desires... You can see here that Navision can even resolve referenced tables (salesperson name...). However, this is not an alternative to the report generator and should not be used in this way... although there are customers who do the same. A very big warning here: Forget about customizing the stylesheets! You will need to invest days, if not weeks, of training in XLST editing! There are better tools for creating documents from Navision / Business Central, even the RDLC is a walk in the park and can be used even more flexibly than Navision's format templates/style sheets.

You have a Navision version up to version 2009R2, you see the buttons shown above with the texts "Send to Microsoft Excel (Ctrl+E)" or "Send to Microsoft Word (Ctrl+E)", but nothing happens when you press them or press Ctrl+E? (Ctrl+E)", but nothing happens when you press them, or press Ctrl+E? Unfortunately, this happened very often: back then, system houses simply took the existing Navision and imported it into the latest Navision version. Back then, this was child's play and usually without side effects, unlike the later updates, e.g. from 2015 to 2016. Do you still have an older Navision, e.g. 2.01? Check your license! Often you already have, without knowing it, a license that allows you to use the still very current Navision 2009 R2! You can easily update from an older version to a new one in the Classic Client versions! Talk to me.
As a result, the new Fin.exe or Finsql.exe then also displayedthese buttons directly. . But for something to work, some objects had to be adapted/imported. E.g. in codeunit 1 the new triggers

LaunchApp(DataXML : Automation „‚Microsoft XML, v6.0‘.DOMDocument60“;StylesheetID : GUID;ApplicationXML : Automation „‚Microsoft XML, v
EXIT(AppLaunchMgt.LaunchApp(DataXML,StylesheetID,ApplicationXML));

SelectStyleSheet(ObjectType : Integer;ObjectID : Integer;VAR StyleSheetID : GUID) : Boolean
EXIT(AppLaunchMgt.SelectStyleSheet(ObjectType,ObjectID,StyleSheetID));

ManageStyleSheets(ObjectType : Integer;ObjectID : Integer)
StyleSheets.SetObject(ObjectType,ObjectID);
StyleSheets.RUN;

But there are more objects involved: Codeunit 403 Application Launch Management,
different tables...
1 2000000065 Send-To Program Yes 26.05.14 10:06:17 Yes
1 2000000066 Style Sheet Yes 26.05.14 10:06:17 Yes
1 2000000067 User Default Style Sheet Yes 26.05.14 10:06:17 Yes
Forms:
2 690 Manage Style Sheets No NAVW16.00 05.11.08 12:00:00 Yes
2 692 Import Style Sheet No NAVW16.00 05.11.08 12:00:00 Yes
2 694 Style Sheets No NAVW16.00.01 14.08.09 12:00:00 Yes
2 697 Manage Style Sheets – Pages No NAVW16.00 05.11.08 12:00:00 Yes

Just talk to me if you miss these functions in your old Navision.There is no "old Navision", in my opinion, please contact me if you have any doubts..

Programming and setting up the style sheets takes me a little over a day, after which you are the master of your new style sheets. In the same breath, I also recommend setting up the changelog. However, this must also be activated in your license.

These functions are built into the RTC and always work. If Word/Excel is missing, the finished file is offered for download and can also be opened in other programs (Libre Office, Google Tables).
In the native 2009 Navision as well as in the RTC and BC versions, you can also change these formatting/templates (templates or style sheets) and thus predefine any colors and other formats. This is even possible per Navision table! For example, output debtors in orange, articles in blue, creditors in green and G/L accounts in fiery, warning red. You also have this option in every Navision from version 2009.

Transfer via program code

The options proposed so far are used to transfer ready-to-view data from Navision and Business Central to Excel, for example.

However, data often has to be processed before it can be transferred in compressed or enriched form.
The built-in Excel table "Excelbuffer" has been available in Navision since Navision Financials 3.01 for this purpose. This can be used to transfer ready-made KPIs (Key Performance Indicators, highly summarized key figures of a company) from reports/reports into an Excel table.
This looks like this in a report using the Excelbuffer table, for example:

In my extended Excel spreadsheet "Excelbuffer", some additional functions have been added that make it even easier to create Excel spreadsheets from Navision & Business Central than the standard Navision functions. For example, the InsCell function, with which I can write a value directly into a selected cell. Much easier than the standard functions in the Excelbuffer that Microsoft provides here. E.g. also simple addition of further Excel tables in a single file.

This technology has been available in Navision since version 3.01, Business Central RTC and Microsoft Dynamics 365 NAV. The advantage: It is even easier to insert formulas, put the columns in a sensible order and have a very detailed influence on the finished table (or Word document). And: ideally, the user has no formatting work at all. This is the output of choice, especially for complex data that needs to be displayed simply. Even for data that is output repeatedly, e.g. every Friday or every morning. Because you save yourself any rework here! Just press a button or use the automatic scheduler to output the data at the desired time, mail it and you're done. Navision can even take over the mailing automatically and time-controlled, if necessary with my Cmail Connector.

In my Excel buffer customization there is also a special FastExport, with which the transfer of data prepared in this way takes place in fractions (typically 1/100 of the regular runtime) of the usual runtime. However, this is only worthwhile for large amounts of data, let's say 3,000 lines or more. Below that, my transfer is still super-fast, but whether you wait 8 or 2 seconds doesn't justify the effort involved. Still faster than Crystal Reports 🙂 This special transfer mode is used, for example, for the PowerBI/Pivot export of enriched Navision data to Excel described at the end of this page. With 300 or 500 thousand lines, the original Excel buffer is practically unusable.

Data transfer via Dataport / XML-port

Similar to the output via the Excel buffer, you can also easily output data from Navision via Dataport (native Navision) or XML port (RTC) so that it can also be imported directly into Excel. There are numerous videos for both techniques on YouTube, e.g. this one here or this one for XML. I will therefore spare you the background here. If you need support with this , please contact me.

Report export to Excel

This is, especially in the older Navision, probably the coolest and most powerful of the unknown functions.
You can export/import the results of all(!) reports (evaluations) in any Navision or Business Central version to Excel (or Word)! This was already possible with the 3.53 or 3.56 ("DOS version"), via the first graphical financials under NT, Windows or OS/2, up to the still widely used RTC versions. Choose one of the two following instructions that suit you.

Report export to Excel or Word with RTC

This is indeed the simplest solution of all Navisions.
In each report, you have the option of outputting it as a real printout, as a PDF, Word or Excel file:

Report export/transfer to Excel or Word under the native (old) Navision 2009R2 and before

Also in these versions (I will leave out the 3.53/3.56 here. But it works! Just this much: The trick is the CSV printer driver!) you can output any (!) report to Libre Office, Google Sheets, Google Docs or even Excel and Word!

After calling up a report, please do NOT call up Page view or Print. Instead, please go to File/Save as HTML.
Save the result in a place where you can find it again, e.g. c:\temp\Debitor - Totals balance list.html

Then please start Excel (or Word) and go to:

In older Excel versions, you can also access this via File/Open.

You now have the report in Excel! But unfortunately it's not yet really easy to edit and process.
But you can also achieve this desired result (pure, easy-to-process data) in just a few more steps.
1. Delete the formatting:
1.1 CTRL+A CTRL+A (select all, really press twice in succession, better safe than sorry), then delete formats

1.2 Optional! This step is used if you want or need to restore the original data sequence at the end.
1.2.a Insert new leftmost column
1.2.b Insert a number series 1-x in this column by formula, or by dragging, or however you like:

1.2.c If you have created the series using a formula: Copy and paste values (otherwise the formula with the beautiful original sequence will be broken immediately)
1.3 Now sort the table according to a meaningful column, in our example column B (customer number)
(in the following example I have continued without the optional numbering, because we don't need it for this list)
Now you have a data block with the real usable data, and above or below (or above and below) garbage data:

Now you are free to decide how you want to continue working. Maybe you want to copy one (!) of the headings back to the top, maybe you don't need various columns. Delete to your own heart's content!
From here you have clean, usable data for further processing.

These steps are always the same, for every process, for every report.

Configuration packages

Configuration packages (also known as Rapidstart in some versions) have been available since version 2009R2.
This function was initially intended to quickly initialize an empty Navision, e.g. for a craftsman's business, a grocery store or another specialist application. But that was always... well... pretty garbage, as the configuration packages supplied were not significantly better than the Cronus client. And, even worse: an unbelievable number of system houses have actually used precisely these sources - configuration packages or Cronus AG - to set up companies. The result was a completely cross number range and totally incorrect access rights . And an SKR03 or SKR04 that would make any accountant beat their hands over their head. And thus also the consequences that in many companies that use Navision, all users still work as "SUPER" users, or an invoice/delivery note/credit bill that has just been posted cannot be found immediately with CTRL+END. Sad...
Long story short:
With the configuration packages, you can also transfer any tables from Navision to Excel. Completely without report HTML export or the Excel buffer, and also with leading zeros that are not lost. You can select any number of tables for a configuration package and any number of columns per table. When the Rapidstart or configuration package is output, an Excel file is created that contains all tables in one file.

ODBC

ODBC is of course already included as standard in every Navision that runs under the SQL server.

With native Navision you need the granule Query. If you don't have this in your license yet, you can buy it today - if you are still in maintenance. If not, this option (ODBC to the native database) is not available to you. You will then have to use one of the other methods described here.
You will find the necessary setup files on your Navision CD in the CODBC subfolder.

After installation, you will find an ODBC driver for your Navision in the ODBC data source setting, where you enter access data. A data source similar to the following SQL server is then available to you. Please be sure to switch the ODBC driver to read-only mode! Otherwise you can easily damage or destroy your Navision database or its contents.

In Excel, go to Data/Data retrieval

Now enter your login data for the Navision database. Tip: These are the same as you use in the C/Side development environment! If you are still using them...

Simply confirm the following 1-2 questions (I assume that you are using "Windows authentication"), and the list of Navision / Business Central tables will open.
Forget all tables with VSIFT at the end, these are the key tables for the flow fields. As a rule, you cannot/do not need these for your Excel data.

You can enter part of the English (original) table name in the search field, which makes the search much easier:

You can easily find the English table names in the Navision / Business Central Client by pressing CTRL+ALT+F1 at the desired position (Classic Client: CTRL+F8)

Klicken Sie die gewünschte Tabelle an, dann auf „Laden“, und die komplette Tabelle wird in Ihr Excel-Arbeitsblatt geladen.

However, the result is not a pure Excel spreadsheet, but a query. This behaves slightly differently to a normal spreadsheet. The most important thing is that you can update the data at any time! This raw data is usually used for further processing, e.g. a pivot table. This is where things start to get magical...

OData

And this is where we really arrive in the 21st century. It has never been so easy to prepare data specifically for a ready-made application and then make it available for Excel or other OData consumers. OData is effectively a REST interface that you can put together with just a few clicks. Let's take a look at the simple version here, via a page. For real professional queries, we recommend using it via an XML data port. However, this will overwhelm "normal users", to whom this information is directed, in almost all cases.

Any list page (article list, main article list, customer list, terms of payment, orders, etc.) is suitable as a data supplier for an OData query. Of course, you can also use pages you have created yourself!

Let's take an article list that already exists: The lookup page, and release it.
Which page is it? CTRL+Alt+F1 on the lookup page: Page 32!

Now we release this page as an OData (OpenData) URL: Ctrl+F3, then web s (for web services). In German it should actually be web die for web services, but I find the web s more catchy.

There are already numerous OData queries in the list, you can use these instructions to deal with each one later, the possibilities are great.

We add our article selection list as an OData data source:

Press "New", object type = page, object ID 32 (we found it in the first step), ignore error message, enter service name (here article selection list), "All tenants" = Yes, Publish = Yes.

No further input is necessary, Navision generates the rest.

Nun die OData URL kopieren (NICHT V4, das kann Excel nicht) oder notieren, dabei den Rechnernamen zu der IP Adresse ändern.
Otherwise the error message "The target principal name is incorrect" will appear later, which is not very helpful. This message has nothing to do with OData itself, but with incorrect/missing DNS certificates.

The finished string then looks like this, for example:
http://x.0.16.xx:7048/xxx/OData/Company(‚1‘)/Artikelauswahlliste
With the 1 in Company you can switch the client, we will stay with the first client for now.

Now it continues in Excel (Word and other data consumers work in a similar way)

In 1 we paste the previously copied V4 URL, in two we paste a valid Windows/Navision user name.

Funnily enough, in many cases you can enter whatever you want here, OData V3 will still give you the data.
To avoid problems due to a lack of authorization, enter a correct name straight away.

However, if you receive the following error message, we are playing a little trick!

In this case, please set the query screen as follows:

In the following screen, enter your correct Windows user with password as a test, often it also works if you simply enter nonsense here.
You will now see the selection list with the predefined query:

Excel now wants to save this query immediately, please fill in the fields as required:

You can now specify what you want to do with the returned data.
Recommendation: First import as a normal table, pivot etc. can be added later.

After a short time, Excel will deliver the Navision data for this view free of charge.
This works with every page... and with queries too.

Incidentally, Navision spends most of the time required for this query calculating the "Inventory" column.
Without this, the query time is significantly reduced!
"Calculated fields", you know... The speed killer par excellence in Navision if used incorrectly.

The premier class: output BI data for pivot analyses / key performance indicators

Would you like to know how many picks you had on Monday or Thursday or any other day of the week? Whether you have more outgoing goods in the morning or in the afternoon? Whether your customers tend to order at the beginning or end of the month? How much your salesperson Müller sold in zip code area 35? How much cover your sales staff make per weekday? "In the past", a separate report was written for each of these evaluations. And as a manager, you often had to wait for IT to provide you with the most important KPIs (key performance indicators) once a week.
Thanks to Excel and pivot tables, this can now be easily determined in no time at all.

With my BI output, Navision creates a BI cube for you at the push of a button, or with my scheduler, a BI cube (business intelligence cube) every night, every Monday .
It's a bit hard to describe, you have to experience it for yourself. How quickly you can make evaluations with Excel and one of the BI cubes. Very briefly summarized: Navision Financials Attain / Business Central 365 creates a list in which many columns are already formatted multiple times. For example, each individual date is output as a day of the week, day number, year/month, month alone, year alone. In this way, you can then use Excel / pivot tables to analyze your sales by day of the week, your coverage by number of days, your representatives by month, your picks by time (if available). The data is also enriched. For example, you do not usually have a zip code in your item ledger entry 32 or value entry 5802. It doesn't belong there in ERP either! But in my output, this data is enriched with such information in no time at all. Hundreds or thousands of times faster than you can do with Crystal Reports or SQL Joins! Temporary tables and massive RAM usage during output provide a speed boost during enrichment, and ready-to-use KPI raw data lets you experience a second speed rush in Excel. Of course, you can also load these prepared tables/data into Google Analytics, Tableau, Microsoft Power BI or Sisense and play around with them there. Or even in Crystal reports, Minitab, JASP and whatever they're all called... but why go to the trouble of familiarizing yourself with these tools and fiddling with their interfaces when 5 minutes of training in Excel is enough?

So many words, and still not quite clear what a cauldron of data is waiting to be analyzed?
I have therefore attached an Excel table with raw data for you to try out yourself and with some examples.

This really works with Point&Click or Drag&Drop:

Simply drag the desired columns from the previously enriched table output from Navision Financials Attain/Business Central 365 into the pivot columns, rows and values. Set whether you want to see the number (e.g. "Picks" or "Deliveries") or the totals or the amounts... and you will see the result in real time! If your raw data exceeds the magic limit of one million rows (1.048.576 rows) of Excel, that's no longer a problem! Simply integrate the same table output by Navision Financials / Microsoft Business Central 365 as an "External data source" and forget about any row limit!

Just try it out with this sample file here:
Attention! The file is without any macro, Visual Basic or any other automatic function. It is pure Excel. If you receive any virus or macro warning, something is wrong!