mibuso.com

Universal Excel Importer
Import data from Excel to any table in just 3 clicks – now possible !

Universal Excel Importer allows to import data directly from Excel file to any table. Select Excel file, then select worksheet, then select fields and your’e done !

UEI has many extra options available. Most of the options are selectable by user (1), or settable inside Excel file (2), or can be predefined as a parameter passed to import function(3).

The options are:

- selection whether to import new records, update existing ones, or do both actions (1)(3)
- predefinition destination table number (1)(2)(3)
- individual selection of fields to import (1)(2)
- define mapping from Excel column to Table Field (1)(2)
- put a filter to column numbers which user can choose to import (3)
- individually select fields on which VALIDATE will be launched (1)(2)(3)
- selection whether to use OnInsert/OnValidate triggers ( 1)(3)
- matching of fields by Field No, Field Name or Field Caption in working language (3)

User can be also prevented from changing any of above option.

UEI does not modify any standard table.

UEI assumes that header is placed in first non-empty row. It skips empty rows between data, and skips non empty rows if the data is placed in columns not selected to import

UEI by default tries to match names in Excel header row to field captions, so easiest way to prepare Excel template to import is:

1. open destination table (directly or on some form)
2. select one row
3. copy and paste to Excel
4. delete copied data but leave header row
5. delete unnecessary columns or rename names in header
6. fill desired data in appropriate columns.

All fields which are parts of primary key of destination table have to be included in Excel.

To define destination table inside Excel put ‘Table:’ in A1 cell (no spaces!). That case Im porter starts to search for header from row 2.

To define individually which on columns launch VALIDATE simply bold the header field, and put 128 in option passed to Import function. By default all fields are VALIDATEd

I’ve learned a lot from mibuso and its users and this is my way to thank all of you.

Hope you enjoy this tool. If you have any questions, or suggestion what to improve, or find any errors, please write to sguzekSGUZEK@onetPL.pl (remove all CAPITALS from email address)

http://www.mibuso.com/dlinfo.asp?FileID=811

Discuss this download here.

Great job, but I doubt the practicality of it. Generally, for example for loading starting inventory, you get a file containing item numbers, location codes and quantities, if you want to do it this way, you have to:

- enter some other data to the excel file such as posting date, document no.
- change the order of columns to the correct order of validation (or for a nonvalidating import, fill about 100 columns)
- you cannot handle nonexisting item numbers, on each occurence the import will stop and complain
- do the whole thing over again when the client says oh we found some extra stock in a corner, please import it too
- also, Excel might do funny things about data exported from another system in CSV: for example decimals become dates

It's just not efficient. It's a lot easier to import everything to a new table, consisting of text50 fields (so it accepts everything) then write code that will check the relational dependencies, mark those that are not met and move those that are OK to the real tables.

Hi,

Thanks for the input.

When you use Import option only new records are added, if you use Update, only existing records are updated but Importer doesn't comply on records which are not existing (at least it _shouldn't_ do). When you click both Import and Update then existings records are updated from Excel, and new are inserted.

Maybe it is some error Can you send me excel file and a fob or txt with destination table definition ?


Well, I agree, when you are getting CSV files, especially in some constant format, it is better to write dataport plus some code. It works much faster. But even then you can have other problems - like different codepage in Navions and OS. That case importing to Excel is better because you doesn't have to write any character code translations.

Well, the tool was designed to do fast imports of reasonable amouns of very different data to different tables, like customer files, account chart, posting setups, etc. Importing directly form Excel indeed is not as fast as importing from plain text file. I spend some time to get it as fast as possible, but reading from automation object takes its time and there is not much to do.

I would'n say if it is better to have "universal" table and every time write some code to transfer data to the destinstion. If you like it just go this way. Others may have other opinions

Regards,
Slawek

Awesome!

Before
You can copy paste data into excel(export) from any form or table but you cant do viceversa(import) by using the same technique thanks to Navision Security.

Now
You can run the code-Unit and
If you Check the "Dont use the OnInsert Trigger" and Validate->Inverse Validate Settings you have all the data in the excel sheet in the selected destination table.

I know Enhancements are surely possible but it has brought a simple but powerful functionality to standard Navision.

Hi Slawek Guzek,



I read your posting on the Excel Importer on Mibuso. Sounds great, but I have a few questions, hope you would like to answer them.



- Which versions of Navision are supported. We are still (don’t laugh) working with Navision 2.6, think this will not work. However we will be implementing 4.01 soon.

- Is it possible to import postings too? Into a (general) journal instead of directly into G/L?



Thanks very much in advance.



Kind regards,

Gerard van Kuijl

Hi Gerard,

Importer will not work on 2.6, since 2.6 does not support RecordRef datatype. It is build on 4.0 version, will work on higher versions, and I think it also should work on 3.xx, although I've never tested it in any version prior to 4.0

Current version supports importing to journals, but VALIDATE should be performed on each imported field, and import process have to be done in two separate steps. First step is to insert Journal Template fied, then Journal Batch field and Line No. field. And some minimal number of other required fields (as transaction type, etc), In the second step - update just inserted journal lines with the rest of required data. Description field should be imported as last field. Both steps can be imported from the same Excel file but different import mode and different fields have to be selected each time.

During importing journals it is very important to insert and validate fields in some particular order, otherwise import process may give strange effects. After such a import posting can fail, or some unpredicted data can be posted. The best practice is to follow fields order from original journal form. Importer follows the column order in Excel - it picks the data from lower to higher columns id.

Next version will have new mode supporting insertion to journals in just one step, but order of fields will still have to be maintained correctly in Excel.

Regards,
Slawek

PS.
BTW. Hmm, I'm not quite sure if importing directly into GL is a good idea..

Hi Slawek,

Thanks for you quick answer.

Story is clear. Will try this as soon as we upgraded to MBS Nav 4.
Your PS is true, I have no intention to import directly into GL and passing by al validations and checks...

Hi Slawek,

great Job.

I'm just not sure why you did it because standard 4.0 brings the "Excel Mapping Tool" with it. You can find it in the Setup Checklist under Functions or just look for Form 8601.

Is your version doing different things then the MS tool?

Best regards, Frank

Hi FDickschat,

As you can read in one of my previous posts, we still have to upgrade to Nav 4.0. However I have a testsetup running with our own license-file.

Problem is that I can't run Form 8601 and unfortunately can't check the functionality of this tool.
Do you know if there is any documentation on this subject? Have been googeling for this, but can't find very much...

Thanks very much and kind regards,
Gerard

Hi Gerard,

no, I haven't been able to find a piece of documentation for the mapping tool . I found out how to use it just by trial and error. Most important: The Excel Tab must be named as the Mapping Code.

A Cronus license can run the form (4.0 SP3) so you can check it out. What is missing in your license I don't know.

Best regards, Frank

Hi Frank,

Excel Mapping Tool was introduced to standard in 4.0 SP2, as I remember. Before it was a part of RIM 2 (Microsoft's Rapid Implementation Metodology), which was released with 4.0 SP1, but in separate package. I started to write my tool much earlier, a couple months after 4.0 was lauched.

The story is quite simple - every new installation I needed to import some configuration data and some customer specific data. And every time threr was not enough time and too much differernt data . What worse usually the customer which had to prepare some data to initial import didn't follow strictly provided data format. So I've started to write some code to make import more and more flexible, and because I'm very lazy finally I've prepared a tool which allows me to import any data in a few clicks. Sometimes customers wanted to have interface to read some data to Navision so to make Importer safe I've builded in filtering capability to restrict in C/AL function call possible target field range.

Importer has a little different philosophy than Excel Mapping Tool. It doesn't require any target field configuration before import. <selfpromotion> It is much easier in use, and much more flexible </selfpromotion>.

Regards,
Slawek

Universal Excel Importer v1.1
Import data from Excel to any table in just 3 clicks – now possible !

Universal Excel Importer allows to import data directly from Excel file to any table. Select Excel file, then select worksheet, then select fields and your’e done !

UEI has many extra options available. Most of the options are selectable by user (1), or settable inside Excel file (2), or can be predefined as a parameter passed to import function(3).

The options are:

- selection whether to import new records, update existing ones, or do both actions (1)(3)
- predefinition destination table number (1)(2)(3)
- individual selection of fields to import (1)(2)
- define mapping from Excel column to Table Field (1)(2)
- put a filter to column numbers which user can choose to import (3)
- individually select fields on which VALIDATE will be launched (1)(2)(3)
- selection whether to use OnInsert/OnValidate triggers ( 1)(3)
- matching of fields by Field No, Field Name or Field Caption in working language (3)

User can be also prevented from changing any of above option.

UEI does not modify any standard table.

UEI assumes that header is placed in first non-empty row. It skips empty rows between data, and skips non empty rows if the data is placed in columns not selected to import

UEI by default tries to match names in Excel header row to field captions, so easiest way to prepare Excel template to import is:

1. open destination table (directly or on some form)
2. select one row
3. copy and paste to Excel
4. delete copied data but leave header row
5. delete unnecessary columns or rename names in header
6. fill desired data in appropriate columns.

All fields which are parts of primary key of destination table have to be included in Excel.

To define destination table inside Excel put table:tableNo in A1 cell (no spaces!). That case Importer starts to search for header from row 2.

To define individually which on columns launch VALIDATE simply bold the header field, and put 128 in option passed to Import function. By default all fields are VALIDATEd

I’ve learned a lot from mibuso and its users and this is my way to thank all of you.

Hope you enjoy this tool. If you have any questions, or suggestion what to improve, or find any errors, please write to sguzekSGUZEK@onetPL.pl (remove all CAPITALS from email address)

New in version 1.1:

- error in Update mode removed (occurring in some cases when updating records with option data type fields in primary key)

- default insertion mode changed - now importing to journals is possible in only one step

- added Delayed Insert option (1)(3) – record will not be inserted until all fields are filled from Excel.

- added possibility of specifying where the data starts inside Excel file (2)(3) - put header:headerRowNo in configuration row and Importer will skip all rows until selected row number, and then will start to search for table header from here. Useful for putting some comments, or any oth er non imported data, at the beginning of Excel file.

Configuration row – it is first non-empty row in Excel file containing at least one parameter definition in any cell. If found Importer will search for table header from next row or from row number specified in parameters

Valid parameters in version 1.1 are:
header:headerRowNo
table:tableNo

http://www.mibuso.com/dlinfo.asp?FileID=811

Discuss this download here.

Hi,
I create excel sheet as mension above
But when I run form no.99991 it shows me error No source defind()
What should I do to import that sheet in to navision???

Hi,

Sorry for delayed answer.

Run codeunit 99991, not the form.

Regards,
Slawek

Slawek:

This is an awesome tool. I want to thank you for putting it up into MIBUSO....AWESOME!

I do have one quick question....when I'm importing text fields in the spreadsheet that are more than 3 digit numbers, the importer trys to add a comma. for example:

Customer no: 123456 (I have the field formatted as text in Excel) tries to be imported as 123,456 and obviously it can find the customer when validating sine the customer no. does not hav a comma.

What can I do to fix this?