The NAV 2013 Universal XMLport

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

In a previous post we introduced a dataport that could be used to quickly bring data into any NAV table without any coding. It's a great little tool that is almost immediately obsolete with the release of NAV 2013. Luckily, we have a solution for that: the DMS Universal XMLport.

NAV 2013 Data Import Options

NAV 2013 is a huge step forward from 2009 - we've using it since the beta was released and we're very impressed. Part of the new and enhanced functionality provided in 2013 are the data migration tools housed in the Rapid Start framework (hint: there is a Rapid Start Role Center that gives you easy access to these tools).

The two biggest problems with the Rapid Start tools are that they're quite slow (at least in the beta), and there's a bit of a learning curve to get them up and running. Once you have some packages set up for master data, they work great. I expect Microsoft will provide a number of these base packages for different industries, making setup much easier.

Another new option for data import is simply pasting data from Excel into NAV (we've been able to do this for years with NavXtender). Pasting from Excel works great for smaller data sets, and I expect to use this feature all the time.

A Simpler Option

So what do you do if you need to quickly bring a larger dataset into NAV 2013 today? Simple: use the DMS Universal XMLport (download here). NAV 2013 eliminates Dataports, but allows you to run XMLports directly from the RTC. Much like the Universal Dataport, all you need for the XMLport is a tab-separated file (usually generated from Excel or SQL Server Management Studio), and a table number. The XMLport will import all the data from the text file, either inserting new records or updating existing ones. Field validation and disabling the change log are also supported.

Performance of the XMLport in NAV 2013 Beta is not as good as the Dataport in previous NAV versions. This might change with the 2013 final release, but the XMLport speed is generally acceptable - depending on the table, it normally imports up to a few hundred records per second when change logging is off.

To use the XMLport, simply import the object (XMLport 50098 - download link at the end of the post), and either add the XMLport to the menu as a Task, or run it directly from the Object Designer. If you add it as a menu item, you can also add it to the ribbon of the Role Center by right-clicking the menu item.

Once running, the XMLport user interface looks like this:

Description of fields:

Filename: The name of the tab delimited text file to import. This is optional, as NAV prompts you for a filename when you hit OK anyway. However, if this is filled in, you can just click "Open" when NAV prompts you again.

Import table number: The NAV table ID to import data into. If you click the "Assist Edit" button beside the lookup, the XMLport will launch the table you've selected - this will open in another instance of NAV.

Allow record inserts: If checked, new records will be created.

Allow record updates: If checked, existing table records will be updated. This will only work if your file includes the record's primary key value.

Validate fields: If "Only validate fields prefixed with '*'" is selected, the NAV data validation logic will only be called for
fields that begin with an asterisk on the first row of the import file. If the "Validate all fields" option is chosen instead, the validation logic will be called for all fields.

Disable change log: Unlike earlier versions of NAV, NAV 2013 logs programmatic record changes to the change log. If you enable the "Disable change log" option, the XMLport will disable the change log during the import and re-enable it when done (assuming it was originally enabled). This can improve import performance quite a bit.

Sample import:

A basic import into the NAV “Location” table appears in Excel below:

The
first row tells the DMS Universal XMLport to import values into four
NAV fields; “Code”, “Name”, “City” and “Phone No_”. The asterisk in
front of the “code” fieldname would indicate to the XMLport to validate
this field if the “Validate all fields” option was not enabled.
Note that the XMLport will accept field names in the NAV
format (using punctuation as in "Phone No."), or in the SQL format that
replaces
punctuation with underscores (like "Phone No_").

Saving this spreadsheet as a tab delimited text file would yield the following file which can be imported into NAV:

Additional information:

If you're importing Contacts, you can include the "No." field in the
header row but leave the values blank/empty and the XMLport will
automatically use the Contact number series for you. This functionality
could be extended to other tables fairly easily.

NAV will attempt to validate fields in the order it encounters them
(from left to right) so be sure to lay out the import file to avoid
validation problems based on field ordering. For instance, if you are
importing into the item journal and NAV encounters the quantity field
before the item number field the validation will fail with an error.

When importing into the Item or Resource table the base unit of
measure will automatically be added to the item unit of measure or
resource unit of measure table for you, so you can import into these
tables in one step.

Be careful with tools like Excel that might insert double quotes
around columns containing certain text data when it creates a tab
delimited text files.

Watch out for duplicate data when running with the ”Allow record inserts” option.

XMLport Object

We've used the XMLport quite a bit (100's of thousands of records) and it's working quite well. However, it was built on beta software and you may find quirks - if you do, please let us know and we'll try to address them.

I did a quick test by adding a "Country Number" field and imported data without any issue. The only thing I can think of is maybe that there were not enough tabs separating the blank fields, but that would have generated an error message. For reference, I put the file I used online at http://www.dynms.com/tmp/Country-9.zip (I changed the option text in the file to the option number to eliminate ML caption issues).

I had exported some financial data from another application into Excel. The last two columns of the rows are debits and credits (both decimal datatypes). There are some rows that have no credit amount. Apparently these cells are simply blank or null (not zero) when I saved the Excel file as a Tab delimited Text file. The Universal XMLport seems to carry the the value from the Debit column into the Credit column instead of the Zero that I would have anticipated.

You are likely using the originally published version of the XMLport that has that issue (the port from 2009 to 2013 caused some issues). If you download the updated file from Mibuso you should be fine, but let me know if you continue to have issues.

I am trying to import into the sales header table and getting an error on the GetCust function when doing the validation of the sell-to customer (local variable custno is blank at the time of validation). My file is formatted with document type, no., sell-to customer no., etc. Is it the format of the file?Thanks, Karen

Hi Karen, First make sure you have the latest version from Mibuso. Then you should be able to import the file with the format you're using (try first with just document type, no., sell-to customer no. Turn on "validate all fields" and "run OnInsert trigger". I tested with values "1 1234 10000" and had no issues.

Hi Krystal - you do not need every field. Just include those that you need. My guess is that you probably need less than 30 as well - many of the fields you're importing are probably set when other fields are validated (e.g., validating Item No. sets description, unit of measure, etc.). Of course, if you already have all the data set up for the 30 fields, there's no point in changing the file now.

"Overflow under conversion of Microsoft.Dynamics.NAV.Runtime.Decimal18value 75724.38 to System.Int32." - likely means you're trying to import a decimal value into the wrong field (such as an integer or option field). To see which field is the issue, turn the debugger on and wait for the error, then check which field is being affected.

DateFormulas (and a few other types) weren't included in the original download, but they're easy to add. In the populateFields function, add a local dateformula variable called "ldfDateFormula" and this code:

Newer builds of NAV enforce the MinOccurs property on the XMLPort, preventing you from importing files with less than 100 fields. An update has been submitted to Mibuso, but you can also get it from here: http://www.dynms.com/microsoft-dynamics-nav-resources/software-downloads/

By default the XMLPort is set to use ASCII (MSDOS) encoding, but just change the TextEncoding property on the XMLPort and it will do Unicode. See http://msdn.microsoft.com/en-us/library/hh168942%28v=nav.70%29.aspx for more information.

I have a requirement of disabling the pop up that comes up asking for the file to be selected from the drives (NOT the XML request page). Is this possible? Or as mentioned in the request page -- Navision FORECES you to select a file when you click OK.

GM - If you got the latest version from Mibuso or our site, all you need to do is add a "Lot No." field to your import of the item journal line table. Make sure your import includes field values for item journal template, item journal batch, and source code as well.

Paul - yes, this is an issue with NAV. Not sure if affects all builds or not, but it definitely affects 2013 RTM and 2013R2 RTM. You get the same effect if you write "standard" code to update customer, e.g.:

Customer.GET('10000');Customer."Phone No." := '12345';Customer.MODIFY(TRUE); // Rec and xRec will be the same in the OnModify trigger.

If you do Customer.VALIDATE("Phone No.", '12345'), Rec and xRec will be different in the OnValidate, but the same in the OnModify.

Not much we can do to change that, and I'm not sure of any work-arounds, other than specific code per table you need to deal with.

while importing XML port i want to skip some lines in csv file... is that possible..??

e.g: im importing purchase line, purchase line has the item no. which does not exist in item master, so error says like-->item no. does not exist in the related table item..now i want to skip that line & import the remaining line...

Please reply me if u have a solution for the above to scssathish@gmail.com.

Hi Sathish, there is no facility in the XMLPort to do this right now, but is not a bad idea to add as an option. That may make it into a future version, but right now the only way to do it is to strip out the lines that are invalid.

Newer builds of NAV enforce the MinOccurs property on the XMLPort, preventing you from importing files with less than 100 fields. An update has been submitted to Mibuso, but you can also get it from here: http://www.dynms.com/microsoft-dynamics-nav-resources/software-downloads/

So I just want to update the property for each column which I did but the only option was once or zero and so it created a blank journal. What do I need to set the value to because if I try to blank it then it defaults to once.

I have just downloaded the tool and using for uploading BOM header Table. But it says to make the number series to be set as 'Default'. Hence, it generates a continuous number series, whereas mine is alphabetic and already defined in the .txt file, which is not accepting. What is the solution?

Importing shortcut dimensions isn't directly supported. If you're on 2013+, you can import the "dimension set ID" for the combination of shortcut dimensions you need to use. That can be tricky if you have a lot of dimension combinations, but the only other option would be to customize the XMLPort to handle the shortcut dims.

I successfully used it to import data, but I have problems when trying to update fields. I tried ti modify some Item Descriptions, I checked "Allow records update" option and I recieve the message "Uou may not enter numbers manually. If you want to enter numbers manually, please activate Manual Nos. in No. Series ART.", as if I've been trying tu add records, not to update them.