difficult to use mapping form

Business Central/NAV Developers Forum
1

Dear All,
I find trouble when using mapping function especially when import data
from excel worksheet. after debugger run, the error message is in
the mapping handle codeunit (ID 8601), in the OPTION. What I mapping is
the data of Account Receivables from excel into sales journal, whenever
the option field types (like account type and document type in the journal)
are not included in the mapping form, it is work, but if I must select
customer, it can’t work, the data will use G/L account as account
type, the account no. is customer no., how I can solve this problem ?
is there any other way? [:(]

I appreciate your answer. tku

Rgds,

Andreas

2

Change the excel option value to the integer value of the option. Remembering they start from 0.

T

3

I am assuming you have a developers licence…

I use variables to get the raw data and then INSERT to the journal lines table after checking the data (field length etc)

As Tony says, use the option number as integer, If varDocType = ‘Invoice’ THEN “Document Type” := 2 (if all are invoices then no variable is needed)

That way, you only have to import the limited amount of data such as the customer account number, amount etc. All other values can be the dafault, for example, you do not have to specify the Account Type if it is G/L (0) but Customer = 1 and must be specified.

My method is to: create variables for all the incoming data fields, check the data, INSERT the record

You can assign your own line numbers this way.

4

Dear T,

Tks for your reply. it works good. Are there another method to import data from excel spreadsheet ?

Rgds,

5

Well, you could set it up as CSV and import using Dataports, or even a report to import.

The other option is table 370 Excel Buffer, which I am kinda fond of.

6

I use csv quite often, it is useful for the one-off transfer of data as you can check the data in clear text format for errors.

In some cases where a description needs to be imported and the quote " or comma , fields have been used, (Item Description comes up quite often) it is possible to use alternate characters such as the carot ^ or the tilde ~ to replace these before export from excel.

After import (often into a temporary table), export from that table using the “,” (standard Navision format).

Then open the .txt file in Textpad and replace all , with pipe | and all " with ¬ or similar unused character (you need to check in the original excel data first), then replace all the original ^ and ~ to get the description commas and quotes back as they were.
Finally, import the data and set the FieldStartDelimiter and FieldEndDelimiter to | and the FieldSeperator to ¬.

Use a report to transfer the data from the temporary table to the final table.
One other post mentions getting a blank Item No., this sort of thing is avoided when using the above method.
Rgds,
Colin

7

Hi Colin,

tks to your answer, I have forwarded to our developer staff here to be consideration.

Rgds,