Universal client export and import

Nav123: Navision, Showare, OrderApp

Navision and Business Central already offer various ways of exporting clients and - in principle - importing them into another database. Unfortunately, these often do not work as desired, expected or hoped for. Here I offer you a simple (!) AND universal option. However, even this cannot work miracles. For miracles in Navision, Dynamics, Financials and Business Central you have to contact me directly.

Use cases (examples)

Save or restore Cronus client

That was the original requirement for this function. The system house had deleted the Cronus client in a customer database. Why? I have no idea. It was definitely a stupid idea. I've also had customers who deleted Cronus AG by mistake. And then you realize (including me) that it's not that easy to restore a single client! So you have to live with the fact that Cronus AG remains deleted. But it doesn't have to be that way!

Create test environment

I wouldn't use this function for that, there's a better method here and here . But if it makes you happy...

Transferring a customized database to a standard database

This is indeed a very practical use! Suppose you have a very unfortunate bent database from Navision or Business Central, and now you really want to switch to a standard Navision version in the same release version. My universal export outputs all data fields of all tables that are found. The corresponding universal import , on the other hand, only reads the fields into the tables that are found in the target database. I think this function alone will make many wishes come true. Of course, there are also massive limits here. For example, you will never get a Food-Vision version, no matter which one, to run in a standard. The adjustments (if you can still call them that) made here by Modus also affect the Navision and Business Central standard so deeply that intensive care is required here. Of course, this applies not only to Foodvision, but to many industry solutions. Do you also suffer from a badly tinkered industry solution?

Updating Navision across version boundaries

Often the differences between major release changes (e.g. between Navision Dynamics 2015 and Navision Dynamics 2016) are not that big, so you can try out how far you can get with this mallet. However, there are also version changes that have such a massive impact on the data structure that you still have to do a lot of rework on the transferred data. For example, in the earlier Navision versions, it was still possible to simply deletewithout any problems. In later versions, table 339 Article adjustment items is vital for the - always hilarious - stock adjustment. But perhaps it is enough to at least be able to view an ancient client in an ultra-modern Navision / Business Central? If you use this function here, you will already know what you are doing... otherwise you better contact me! Maybe you just want to have a look at your 2009R2 database in the RTC without much effort? You have Cronus in a new demo installation anyway. With this little tool here you can also simply transfer a real client, various statistics, order entry, customer card and much more will work at least a little.

Data manipulation

Yes... a very interesting usage option. Output client, modify data, read back client... I strongly advise against it!

Output of all data for analysis

This can be very practical again. You output all the data that your Navision knows into the data files in one go and then integrate the desired data into Access or Excel using a CSV driver, for example. But here, too, I would choose other methods .

Output of all data for system change

Really? You want to replace Navision / Business Central? Why? Don't do that! They say you shouldn't stop a traveler, but I'm not so sure about Navision and Business Central. Presumably you have had bad experiences with Navision or Business Central? Let's give it a try together?

Previous options for outputting and importing clients from Navision / Business Central

Export to a data file / Import from a data file

Have you ever used the Export to a data file or Import from a data file function(s) under the RTC? If you have been using it for a while: Does this look familiar to you? That's right! This is the old data backup that you could call up in the old Navision via Extras/Create data backup!

Ansicht der Funktion "In eine Datendatei exportieren" im RTC: Das ist die alte Datensicherung!
"Export to a data file" in the RTC: This is the previous data backup!
Ansicht der Funktion "Datensicherung erstellen" im alten 2009er Client: Das ist die Basis für "In eine Datendatei exportieren"!
Screenshot of the "Create data backup" function in the old 2009 client: This is the basis for "Export to a data file"!

And this means you also have the same restrictions. If you only output a single client here (the smallest amount of data) and then want to import it into another database, the source and target databases must be 100% (more like 110%) identical. Even extensions must be absolutely identical, even if they do not make any changes to data structures or are not active. In addition, the data is encoded during export so that no plain text editing is possible.

For all the use cases described above, transferring clients from one database to another database is with this function is therefore practically not possible.

This also applies to the corresponding Powershell command, which calls the same Navision function.

Export-NAVData -ServerInstance <service name> -Tenant <tenant ID> -CompanyName "<Company name>" -FilePath <location>

Technically, both the Powershell command and the Export to a data file function are nothing more than a call to the (relatively new) command [Database.]ExportData:

Calling the Navision / Business Central command ExportData directly via the program code creates the same files as Export to data file, in effect an old Navision data backup.

If you have searched for and found this page here, you probably already know that this is not suitable for the applications described.

Configuration packages / Rapidstart / Data exports (formerly Electronic Tax Records Files export)

I will leave out data exports here, they are too specific, but they can also be a suitable tool! They used to be called Electronic Tax Records Files export, which already gives an indication of the actual purpose.

The configuration packages (old name: Rapidstart) are an incredibly cool tool, which has become a real egg-laying wool-milk sow over the various versions.
You can use it to output ALL data from Navision to Excel (well... "all" up to 2 million data records...), and also read back a lot of data. "A lot" is limited to tables that are not write-protected. So no posted documents such as sales invoices, delivery bills, registered reminders... and posted items (article items, value items, debtor items...). Apart from this, however, it is highly recommended that you familiarize yourself with the configuration packages if you have not already done so. This tool is not suitable for quickly restoring a Cronus or copying a client from an old Navision version to a new version. A) it is often not available in the older version, B) sometimes the internal structure of the output files differs between different configuration packages / Rapidstart versions of Navision, and C) it is simply far too time-consuming to enter all the tables you need there. Well, and D) again, of course, that this tool cannot export/import into protected areas such as posted invoices or directly into customer items.

Copy & paste

Yes, indeed! In the old Navision you can copy a lot of table data (all Navision data is in some tables!) directly via the tables (Designer, Table, Run) from one table/database using CTRL+A (Select all), CTRL+C (Copy, in Navision RTC and Business Central CTRL+Shift+C), and then paste it into the other Navision database using CTRL+V (Insert, the V is reminiscent of a funnel, in Navision RTC and Business Central CTRL+Shift+V).
However, the tables concerned must be 100% identical again (not quite so 100% with RTC), and depending on the number of data records, copying (not pasting!) takes a long time. One disadvantage should be mentioned separately: all table triggers and checks are run through, so the order of the columns on the screen and the order in which the tables are inserted play a major role. And, for example, the "Corrected on" date is always a current date. Here and there, however, copying and pasting is actually a practical thing that can save a lot of time.

How my universal client export & client import works

In principle, I have simply written a serializer and de-serializer, which simply runs through all tables one after the other, creates a file for each table and then writes one line per data record to this file. Empty fields, flow fields and flow filters are automatically skipped, which makes the file(s) somewhat leaner. Tables can of course also be restricted by making adjustments in the function.
The deserializer in turn can understand this data format, it then creates one data record per line in the file in the correct table.

By the way, this is also a nice example of the use of Recordref (recRef) and Fieldref, which keeps the program code surprisingly short.

Jede einzelne benutzte Tabelle von Navision / Business Central wird in eine einzelne Datendatei ausgegeben
The export writes to a single data file for each individual Navision / Business Central table used. In this way, a file can also be simply deleted before an import (e.g. the article item table). This is then simply no longer read/imported in the target client.

No BLOB fields are output, so that, for example, no article images etc. can be seen in the imported client. If you need this, please contact me. However, you can simply add the binary export/import function yourself. Technically, a separate file with the binary content is then simply output for each BLOB field content and read in again later.

This function is "free to use" with this release, of course without any functional guarantees or securities.
Use is NOT permitted, not even in extracts, for the company Landefeld Druckluft & Pneumatik from Kassel and for Dr. Ulrich Obermüller (possibly living in Kiel). There is a previous story about both of them, which I will publish when I get the chance. I have had unbelievably bad experiences with both of them.

Suitable Navision or Business Central versions

It does not work without RecordRef. Recordref or RecRef was introduced with the 3.60, stable from the 3.70 Navision version. The good news: Older Navision databases, e.g. from 2.01(b) etc. can be converted very easily to a 2009 or 2009 R2 or 4/5 version.
In addition, the unlimited string length introduced with the RTC (Navision 2013, 2015 etc.) was used. Here, however, the data fields could simply be output line by line instead of in one line for older versions. There are no further restrictions, e.g. all tables & fields that are identical in Navision 2009R2 and Navision 2018 (Business Central BC 14) could also be transferred from 2.01b or 2009R2 to BC14. During import, all fields that cannot be mapped are automatically ignored. Minor changes, e.g. changing from an integer field to a code field, are made automatically.
Spoiler alert: You certainly won't be able to do more than "have a look" with this database! But that's already more than you can do with official tools.

In principle, a 3.56 Navision could also be read into a 2.01 Navision, which could then be converted to 2009R2, and so on. But! You don't need that at all! You can simply import a Navision 3.53, 3.56, 3.5x data backup directly with a 2.01(b) Navision! All tables and all fields that are missing are automatically created by Navision in the previously empty database. However, no process logic is imported, only the data. You can then convert this to 2009R2 with a flick of your finger (well... almost...) and then quickly view your master data under RTC or in the web client.

The code is published here as it works. It will not be maintained or extended here! If you are interested in this function, but hope to get more out of it than just quickly restoring a Cronus AG, then please contact me.

OBJECT Codeunit 50003 RTH_ExportImportCompany
{
  OBJECT-PROPERTIES
  {
  }
  PROPERTIES
  {
    Permissions=TableData 17=id,
                TableData 21=id,
                TableData 25=id,
                TableData 32=id,
                TableData 45=id,
                TableData 46=id,
                TableData 110=id,
                TableData 111=id,
                TableData 112=id,
                TableData 113=id,
                TableData 114=id,
                TableData 115=id,
                TableData 120=id,
                TableData 121=id,
                TableData 122=id,
                TableData 123=id,
                TableData 124=id,
                TableData 297=id,
                TableData 298=id,
                TableData 339=id,
                TableData 379=id,
                TableData 380=id,
                TableData 405=id,
                TableData 480=id,
                TableData 5802=id,
                TableData 5811=id,
                TableData 9004=id,
                TableData 9006=id,
                TableData 9008=id,
                TableData 9999=id;
    OnRun=VAR
            TXTImportExport@1000000000 : TextConst 'DEU=Firma %1 in Datei exportieren,Datei in Mandant %1 migrieren,Mandant %1 mit Datei initialisieren;ENU=Export company %1 to File,Migrate File into Company %1,Init Company %1 with File';
            ConfirmString@1000000001 : Text;
          BEGIN
            ConfirmString := STRSUBSTNO(TXTImportExport,COMPANYNAME);
            CASE STRMENU(ConfirmString) OF
              1 : ExportCompany;
              2 : ImportCompany(FALSE);
              3 : ImportCompany(TRUE);
            END;
          END;

  }
  CODE
  {
    VAR
      Tables@1000000002 : Record 2000000028;
      SystemObject@1000000008 : Record 2000000029;
      RecRef@1000000000 : RecordRef;
      TXTbasicPath@1000000003 : TextConst 'DEU=c:\temp\';
      Window@1000000004 : Dialog;
      DataFile@1000000001 : File;
      RecCounter@1000000005 : Integer;
      TXTextension@1000000007 : TextConst 'DEU=NavCompany';
      TXTdivider@1000000006 : TextConst 'DEU=Ý';

    LOCAL PROCEDURE ExportCompany@1000000000();
    VAR
      FileName@1000000000 : Text;
    BEGIN
      Tables.SETRANGE("Table No.",1,1999999999);
      Tables.SETFILTER("No. of Records",'>0');
      Tables.SETRANGE("Company Name",COMPANYNAME);
      Window.OPEN('Export #1########################################\@2@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@');
      IF Tables.FINDSET THEN REPEAT
        FileName := TXTbasicPath + FORMAT(Tables."Table No.") + '_'+ DELCHR(Tables."Table Name",'=','/\?*+') +'.'+ TXTextension;
        Window.UPDATE(1,FileName);
        CLEAR(RecRef);
        RecRef.OPEN(Tables."Table No.");
        IF RecRef.READPERMISSION THEN
          IF RecRef.FINDSET THEN BEGIN
            CLEAR(DataFile);
            DataFile.TEXTMODE(TRUE);
            DataFile.CREATE(FileName,TEXTENCODING::UTF8);
            CLEAR(RecCounter);
            REPEAT
              RecCounter += 1;
              IF RecCounter MOD 1000 = 0 THEN BEGIN
                Window.UPDATE(2,ROUND(RecCounter / Tables."No. of Records" * 10000,1,'<'));
                CLEAR(RecCounter);
              END;
              DataFile.WRITE(ExportRecord(RecRef));
            UNTIL RecRef.NEXT = 0;
            DataFile.CLOSE;
          END;
      UNTIL Tables.NEXT = 0;
      Window.CLOSE;
    END;

    LOCAL PROCEDURE ExportRecord@1000000002(pRecRef@1000000000 : RecordRef) RecordString : Text;
    VAR
      FieldRef@1000000001 : FieldRef;
      FieldCounter@1000000002 : Integer;
      FieldAsText@1000000003 : Text;
    BEGIN
      FOR FieldCounter := 1 TO pRecRef.FIELDCOUNT DO BEGIN
        FieldRef := pRecRef.FIELDINDEX(FieldCounter);
        IF FORMAT(FieldRef.CLASS) = 'Normal' THEN BEGIN
          CLEAR(FieldAsText);
          CASE FORMAT(FieldRef.TYPE) OF
            'BLOB','Binary','Media','MediaSet':;
          ELSE
            FieldAsText := FORMAT(FieldRef,0,9);
          END;
          FieldAsText := CONVERTSTR(FieldAsText,TXTdivider,'_');
          IF FieldAsText <> '' THEN
            RecordString += TXTdivider + FORMAT(FieldRef.NUMBER) +';'+FieldAsText;
        END;
      END;
    END;

    LOCAL PROCEDURE ImportCompany@1000000001(pInitTables@1000000000 : Boolean);
    VAR
      FileList@1000000001 : Record 2000000022;
      TableNo@1000000002 : Integer;
      TXTallTables@1000000003 : TextConst 'DEU="Sollen ALLE Tabellen gel”scht werden? Nein=nur die eingelesenen.";ENU="Clear ALL Tables? No=Only imported."';
      DataFileLen@1000000004 : Integer;
      DataFileLine@1000000005 : Text;
    BEGIN
      IF pInitTables THEN
        IF CONFIRM(TXTallTables) THEN BEGIN
          Window.OPEN('Delete Table #1###########');
          Tables.SETFILTER("No. of Records",'>0');
          Tables.SETRANGE("Table No.",1,1999999999);
          IF Tables.FINDSET THEN REPEAT
            Window.UPDATE(1,Tables."Table No.");
            CLEAR(RecRef);
            RecRef.OPEN(Tables."Table No.");
            RecRef.DELETEALL;
          UNTIL Tables.NEXT = 0;
          Window.CLOSE;
          COMMIT;
        END;

      Window.OPEN(
        'Import #1########################################\'+
        '@2@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\'+
        '#3########################################');
      FileList.SETRANGE(Path,TXTbasicPath);
      FileList.SETFILTER(Name,'@*'+TXTextension);
      FileList.SETFILTER(Size,'>0');
      IF FileList.FINDSET THEN REPEAT
        Window.UPDATE(1,FileList.Name);
        EVALUATE(TableNo,COPYSTR(FileList.Name,1,STRPOS(FileList.Name,'_')-1));
        CLEAR(RecRef);
        RecRef.OPEN(TableNo);
        IF RecRef.WRITEPERMISSION THEN BEGIN
          DataFile.TEXTMODE(TRUE);
          DataFile.OPEN(FileList.Path + FileList.Name,TEXTENCODING::UTF8);
          DataFileLen := DataFile.LEN;
          WHILE DataFile.POS < DataFileLen DO BEGIN
            IF DataFile.POS MOD 1000 = 0 THEN
              Window.UPDATE(2,ROUND(DataFile.POS / DataFileLen * 10000,1,'<'));
            DataFile.READ(DataFileLine);
            ImportRecord(RecRef,DataFileLine);
          END;
          DataFile.CLOSE;
        END;
        COMMIT;
      UNTIL FileList.NEXT = 0;
      Window.CLOSE;
    END;

    LOCAL PROCEDURE ImportRecord@1000000003(pRecRef@1000000000 : RecordRef;pDataLine@1000000001 : Text);
    VAR
      FieldRef@1000000002 : FieldRef;
      FieldNo@1000000003 : Integer;
      FieldContent@1000000004 : Text;
      FieldContentPosition@1000000005 : Integer;
      FieldContentLen@1000000006 : Integer;
      RecCounter@1000000007 : Integer;
    BEGIN
      WHILE STRLEN(pDataLine) > 0  DO BEGIN
        IF COPYSTR(pDataLine,1,1) = TXTdivider THEN
          pDataLine := COPYSTR(pDataLine,2);
        EVALUATE(FieldNo,COPYSTR(pDataLine,1,STRPOS(pDataLine,';')-1));
        IF pRecRef.FIELDEXIST(FieldNo) THEN BEGIN
          FieldRef := RecRef.FIELD(FieldNo);
          FieldContentPosition := STRPOS(pDataLine,';')+1;
          IF STRPOS(pDataLine,TXTdivider) > 0 THEN BEGIN
            FieldContentLen := STRPOS(pDataLine,TXTdivider) - FieldContentPosition;
            FieldContent := COPYSTR(pDataLine,FieldContentPosition,FieldContentLen);
            pDataLine := COPYSTR(pDataLine,STRPOS(pDataLine,TXTdivider)+1);
          END ELSE BEGIN
            FieldContent := COPYSTR(pDataLine,STRPOS(pDataLine,';')+1);
            CLEAR(pDataLine);
          END;
          IF EVALUATE(FieldRef,FieldContent) THEN;
        END;

      END;
      IF pRecRef.INSERT THEN BEGIN
        RecCounter += 1;
        IF RecCounter = 100 THEN BEGIN
          Window.UPDATE(3,pRecRef.GETPOSITION);
          CLEAR(RecCounter);
        END;
      END;
    END;

    BEGIN
    END.
  }
}