Best option to import Excel file into a NAV Table

I have monthly sales data I am getting from several distributors in an excel format.

The data contains:

  • the distributors company code they sell to (which I’d like to cross reference to an internal customer number in NAV via a distributor cross reference table)
  • the distributors Item number (which I plan to cross reference to our internal item number via the item cross reference table)
  • quantity sold
  • there is also customer address data which I can use to identify the customer and cross reference to our customer if we have not cross referenced it yet.

The user would enter or select the distributor NAV Customer No and the date of the sales (either the first or last day of the month as we only track monthly sales). I can either pull all the raw data in and then address any missing cross references after the fact via exception reports or I can try to validate the data on import and somehow identify any missing cross references.

I know I could bring the data in via a data port but since these are going away in NAV 2013, I was looking to use either an xml port or an import from excel using the excel buffer table / report methodology. We do not have a full developer license (I can do code on reports and dataports) and I don;t think we have permission to create new XML ports (which seems wrong if they are doing away with Dataports they should give us access to xml ports). Another option might be SQL analysis services? I’m open for suggestions on tools and methods to use …

So what are my real options to do myself, what is the best solution overall, I always try to do as much myself as possible to save overall cost.


Actually the SQL Integration services would be a possible solution and not Analysis services.