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.

All utilities and downloads are available at www.dmsiworks.com

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:

DMS Universal XMLPort

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:
*code name City Phone No_
DMS Dynamic Mfg. Solutions Edmonton 780-988-0215

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.

You can download the XMLport from Mibuso.

51 comments:

Michael said...

Hello. I'm testing this program with some of the simpler tables first (i.e. Standard Text) and it seems to work fine.

I'm trying to use it with Table 9 Country/Region where I've added a 3 character text field 50000 - Country Number Text 3.

Here's my input file

Code Name EU Country/Region Code Intrastat Code Address Format Contact Address Format Country Number
ABW Aruba City+State+ZIP Code First 533
AFG Afghanistan City+State+ZIP Code First 004
AGO Angola City+State+ZIP Code First 024
AIA Anguilla City+State+ZIP Code First 660


Everything works except the Country Number. Any idea

Thanks
Michael

Mark Hamblin said...

Hi Michael,

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).

If you're still having problems, give me a shout.

Michael said...

My mistake. I had two tabs after a field instead of one. Thanks. Just trying it on the Customer table now and getting an answer that the file is too large.

Mark Hamblin said...

Yes, there does seem to be a limit on the file size an XMLport can handle - you may want to split the file into 2 or more parts.

Michael said...

I'm doing that now. But it's not liking fields with quotes in them. Any ideas?

Mark Hamblin said...

Strange. Do you have a sample of the file? If you do, fire it off to support@dynms.com and I'll have a look.

TonyH said...

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.

Mark Hamblin said...

Hi Tony,

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.

TonyH said...

Hi Mark,
I just downloaded the latest version from mibuso this morning. I seem to be having the same results.

Mark Hamblin said...

Hi Tony - that's strange. I'll get someone to have a look, but in the meantime I guess the easiest approach is to make sure those cells have zeros in them instead of leaving them blank.

- Mark

Anonymous said...

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

Mark Hamblin said...

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.

Anonymous said...

Mark,
It did work for me with just those 3 fields. I'll just have to keep adding fields and see where I run into trouble. Thanks.
Karen

Anonymous said...

Hi Mark,
Dumb question... Does the .txt import file have to have every field in the table?
I am trying to import data into the gl journal line table (#81) but only need 30 out of the 175 fields.
Krystal

Mark Hamblin said...

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.

Anonymous said...

Hi am receiving this message...
Overflow under conversion of Microsoft.Dynamics.NAV.Runtime.Decimal18value 75724.38 to System.Int32.

Mark Hamblin said...

"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.

Anonymous said...

I do not understand how DateFormula fields can be populated. I am looking at fields such as Lead Time on the vendor. Can you assist? Thanks, Karen

Mark Hamblin said...

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:

'DateFormula':
BEGIN
EVALUATE(ldfDateFormula, ltxtValue);
IF abFieldValidate[lnFileFldIndex] THEN
lfrField.VALIDATE(ldfDateFormula)
ELSE
lfrField.VALUE(ldfDateFormula);
END;

Or send an e-mail to our support e-mail and we can send you an updated version.

Anonymous said...

Hello Mark,

We try to import a file with 3 fields.

We get the following error:

The element is expected by Min Occurs value: Once.
Element received:

What can cause the problem?

Anonymous said...

Hello Mark,

We try to import a file with 3 fields.

We get the following error:

The element field4 is expected by Min Occurs value: Once.
Element received: Dummy table

What can cause the problem?


Mark said...

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/

Anonymous said...

Mark, thanks for your reply!

Anonymous said...

Thank author for the tool! It works fine for ANSI data. However, my customer got Unicode data, for example:

*Code Name City Phone No_
DMS ソリューション Edmonton 780-988-0215

I tried to save text file as:
- Unicode Encoding: error occurs '.', hexadecimal value 0x00, is an invalid character. Line 1, position 90.
- UTF-8 Encoding: made data broken.

Please help me!

Anonymous said...

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.

Anonymous said...

Thank you! I also found another solution at http://www.mibuso.com/forum/viewtopic.php?f=7&t=55043

pratyusha menon said...

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.

Any Help?

Thank You.

Mark said...

Hi Pratyusha,

Unfortunately, if you run the XMLPort interactively you are stuck with the file dialog. The code in the XMLPort could be moved to a report or page instead with some effort.

Rune Foyn said...

How can Dimension data be imported using the NAV 2013 Universal XMLport?

Anonymous said...

Rune: if it's a shortcut dimension, you can just import the value into the field and make sure it's validated (either validate all fields, or put a * beside the field name in the file).

The other option is to first build all your dimension sets (either through importing or manually creating them) and then import the dimension set ID.

Anonymous said...

Hi When I browse for the file I occasionally get the following error:

"An I/O Exception occurred during the operation"

If I close and reopen Nav and re browse for the file this tends to fix it however I was wondering how we can resolve this.
Thanks

Anonymous said...

The I/O exception is likely a NAV client issue (or bad network potentially). Trying a newer/different build for the client may help.

Anonymous said...

I am receiving an error on the customer table import the county field I believe is restricting itself to 10 chars and the telephone is stating 10 as well when the tables hold up to 30
Thanks

Anonymous said...

Sorry some fields had " round them causing them to move slightly

Thanks

GM said...

How do u use this tool to import inventory opening balances with lot numbers?

Anonymous said...

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 said...

When I try to update the Customer table, it doesn't change anything. I noticed that Rec and xRec are the same, so it skips the MODIFY(). Any ideas?

Mark said...

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.

Sathish Chinnappan said...

Hello Mark,

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.

Thanks & Regards,
Sathish

Mark H said...

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.

Anonymous said...

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.

Mark H said...

Set it to "Zero", or grab the latest version from Mibuso or the dynms.com website. The newer versions have additional functionality as well.

satlongtuong said...

Thank you for this tool, it's so helpful. Can I change fieldseperator from to another (exp: <;> for csv file)?

Mark H said...

Yes, just change that fieldseparator property in the XMLPort and it will work with CSV. However, if your data has commas in it, the import will not work properly.

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...

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?

Mark H said...

To import number series fields, either set the number series to manual and default or don't validate the field as you import.

Anonymous said...

This tool works great! I use it to import G/L data into table 81, but I have a problem with the shortcutdimensions 3 to 8. Can I use the tool for these fiels, as they are no real table field?

Mark H said...

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.

Marius Paul said...

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.

Mark H said...

Hi Marius, I'm not sure what would cause that. If you have the item # and description in the file, and only "allow record updates", you should have no issues.

Post a Comment

Your comment will be posted once approved.