Problem with importing items using excel import/export function

Business Central/NAV User Forum
1

hi,

i’m trying to migrate some item data into nav using excel import/export function.

i’ve built a table with item card, net weight and material content.

i export it. fine.

i amend it fine.

save it. fine.

i then import it. slow but eventually ok.

i check migration records and remove the ones i dont need (or keep them all, no big fuss - tried both) and my data change is there.

i apply the changes and my item card does not change…

i cannot ifnd nav manuals for love nor money, and the few i have got hold of don’t deal with the darker aspects of data migration.

anyone got any ideas?

thanks for your help,

rosy.

2

Did you mean “Export to Excel” functionality (toolbar button) in NAV5? If so - this is one way only, to EXPORT the, say, Item Card or whatsoever, and as a result you get Excel sheet with approximately the same layout, nothing more. It doesn’t work for import.

To import data, you have two options - DataPort or XMLport, they work both ways, export and import. But with import you must be careful about data validation, read more on this in ADG manual, sections about DataPorts and XMLports. Typically, import side contains some C/AL code to verify and validate data, without it you can get a corrupted database. Most typical error - lowercase (and other restricted characters, or most worse - BLANK) in fields of type Code.

3

thanks for your swift response.

i’m a bit confused. i used the export then import function for excel. if the excel import doesnt work, then why is it on there?

i’m only adding numbers to an item card cell. not rocket science. the excel import worked as far as the imported numbers appearing in the migration records. they just didnt write to the card when applied.

i would use xml but i do not know how to edit xml and resave in correct format. any dummy tips?

where can i get an adg manual. i’m a good learner/reader but find nav manuals hard to come by…

thanks

r

4

I am fairly certain that the OP is using the Data Migration functionality…

You find it here: Administration → Application Setup → Company Setup → Data Migration

Where it is possible to import/export in Excel format or in XML. Which to my knowledge should work fine.

5

Can you type in the wanted numbers directly on an item card? Or are they perhaps very long numbers or something?

6

i’m using that OP. no joy.

i have a net weight cell on item card. have 1000’s of items. wanted to shortcut putting in weights…

r

7

I just tried the following:

I choose Table id 27 and Form id 30

I export No. and Net Weight field to Excel…

Then i manually entered Net weight (8, 10 and 32,3 and 7,8) for the first four items (out of 144 as it is a Cronus i am working on)

then Saved the excel sheet and imported the records

Then i pressed the Migration Button and choose Migration records and choose the four records i have changed.

It works for the records where i have entered a decimal like 32,3 or 7,8 but it didn’t work where i typed in 8 or 10…

So try using a decimal number (it is a decimal field in table 27).

8

copied your steps. formatted cell as number with deci places.

no joy. i exported x thousand records. tested on first line and deleted rest. i see no reason why that affects things.

this is frustrating.

thanks for your kind efforts to help.

r

9

only other difference is i exported tariff no and pfsuserdefinedfield too…

10

I didn’t format the cell as a decimal in Excel.

It works for me if i simply type 3,0 (or 3.0 depending on what separator you are using?) instead of 3 and 4,0 instead of 4… But maybe you tried that to?

11

Hi,

Rosy - did You found something about “… darker aspects of data migration…” ?

I dream about something like “Checklist for data migration in NAV from legacy system”. Sequence of importing. Just standard w1 tables. Later in my dream I run one T-SQL script to import all initial data from temporary SQL database to Navision’s SQL database tables. And then I wake-up :frowning:

About Your particular update:

I am total newbie, but may be You can think about “Processing-only report” option… In this case You need dummy table with key (to link with “Items”) and some fields containing Your special info. Then You create processing-only-report using data items “Item” and “Dummy table” with correct relation and some code to update right fields in “Item”. Difference is that You can write CAL code in reports with “normal” license.

Lauris

12

sounds well beyond my means.

try all sorts of methods so far. no joy. mystery unsolved.

NAV 1 ROSY 0.

13

Hi Rosy,

Don’t let Nav beat you [:)]

If all you’re wanting to import is the Net weight and Material Content for each item, you may wish to try a simple dataport instead ?
Once you’ve written your first dataport, you’ll wonder how you ever managed without them for moving data about - that’s what I found anyway.

Kind regards

Tim

14

SOLUTION FOUND:

thanks.

i’m not familiar with dataports so would be interested to know where to start. manual?

i found the solution!! despite no error warnings, i unistalled office 2007 and put in 2003 professional. it works now [:D]

R

15

I’ve never come across a manual - I see you’re based in the UK… if you want a quick lesson I’d be happy to share my screen with you (thanks to the wonders of the internet) and give you a quick overview [:)]

16

Manual sits in your Product CD/DVD directory Docs and is named w1w1adg.pdf (Application Designer’s Guide) [H]

For DataPorts, read Part 7…

PS I have always been wondering how much people have NEVER looked at what they have on NAV Product disk - approximately once a week I or someone else write a post here about this documentation and where to find it…

17

thanks.

fyi: the company that set us up gave us no manuals. i had to ask for them and got some on our shared server. further to your message i enquired and have just been given an install disk which has additional info, including the file you mention.

seeing how i have scoured over 20 manuals so far, i kind of resent your comment. its not my fault i wasn’t given it.

does one normally get a fromal microsoft nav install disk? they just gave me a basic dvd home burned copy…

thanks

R

18

No, there aren’t “nice factory-made” NAV disks - Partners themselves get them as ISO images from MS intranet site PartnerSource… They simply can’t give you anything else, than “burned copy”.

The same with Manuals - no printed copies exist, only these .pdf files.

If (and while) you are on acrtive Maintenace Plan, you are eligible to access CustomerSource - another MS intranet site, but for Clients. There are lots of training materials available, even more, than in Partner’s site (and other good stuff regarding Dynamics products in general).

For CustomerSource access, you should ask Partner’s help - if they try to avoid doing so (I’ve heard about such cases) call your countrie`s Microsoft subsidiary and THEY will set you up - but again, this is only if you have active Maintenance Plan (which is mandatory for first year after purchase and optional later).

19

Hi Rosy,

Have a look at the Application Designer’s Guide on the product DVD (w1w1adg.pdf) - it will give you a good grounding.