Universal Excel Importer v1.2

Administrator

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.

Miklos_Hollender

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.

Slawek_Guzek

Hi,

Thanks for the input.

- you cannot handle nonexisting item numbers, on each occurence the import will stop and complain

:-s 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.

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

- also, Excel might do funny things about data exported from another system in CSV: for example decimals become dates

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.

It's just not efficient. It's a lot easier to import everything to a new table, consisting of text50 fields...

:roll: 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

gulamdastagir

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.

=D>

gjvk70

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

Slawek_Guzek

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

gjvk70

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

FDickschat

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

gjvk70

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

FDickschat

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

Slawek_Guzek

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

Administrator

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.

vyanku

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???

Slawek_Guzek

Hi,

Sorry for delayed answer.

Run codeunit 99991, not the form.

Regards,
Slawek

edmasto

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?

Slawek_Guzek

Hi,

Look into Control Panel->Regional Settings and digit groupping char. Change it to space.

Regards,
Slawek

Patridepatricia

HELLO, IN RELATION TO THE DEVELOPMENT TO MATTER FROM EXCEL THAT YOU PUT IN MIBUSO.COM.



SINCE I DEFINE THE TABLE INSIDE EXCEL TO BE ABLE TO IMPORT THE INFORMATION?



I ATTACH THE FILE FOR IF YOU DID NOT HAVE IT

Slawek_Guzek

Hi,

I'm not sure if I understand your question correctly.

If you're asking about how to create the file in order to import it later to the Navision the very basic rule is you have to name each column which is intended to be imported. The name shoud be the same like the name of the field to which the data will go. One column per one field, one row per one imported record. The column names should be in your local language - like field captions. All column names have to be in the same row inside excel file.

The simplest solution to prepare the file is t to open the table in Navision and copy just one row to the Excel file, and then remove unnecesary columns and data.

Regards,
Slawek.

vaxo_jj

The tool is great! but there is a one problem - It is not possible to make the tool understand exp. G/L account number as as text. That is, if I want to insert in "Account No" field - 1234, it understands as 1,234. I tried to change digit grouping to space (from regional settings), but in this case it understands as 1 234 and still the error massage appears. Tried also changing excel cell format to Text, General, Number, but without success.
Can you advise me a resolution way, please.

rocatis

vaxo_jj wrote:

That is, if I want to insert in "Account No" field - 1234, it understands as 1,234. Tried also changing excel cell format to Text, General, Number, but without success.

I'm stuck with the same problem.

Weird thing is, that if you format the column in Excel as Text BEFORE entering data into it, it works... It also works if you have an existing column and put an apostrophe (') before the number in each cell although that solution is a bit tedious.

But formatting a column as text AFTER data has been entered doesn't work. Frankly, it smells like a bug in Excel.

Slawek_Guzek

Dear vaxo_jj, rocatis

Sorry for the inconvenience. This is indeed the Excel bug, well, maybe not bug but a 'feature'... and yet another 'feature' of NAV automation objects interface.

Anyway - in the new version of Importer, which is already sent to mibuso and waiting publication, this problem is sorted out (I hope )

Regards,
Slawek

Administrator

Universal Excel Importer v1.2
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.2:

- No more headache with country-specific number formatting.

- Auto-Increment of fields. Very useful when importing journals. Instead of filling entire "Line No." column with consecutive numbers just include text 'incr:Line No.=00001' in one of the first cell in your Excel file

- Constant values of fields. Very usefull when importing journals. Instead of filling entire "Journal Template" column with "GENERAL" word just include text 'const:Journal Template=GENERAL' in one of the first cell in your Excel file

- new field mapping interface.

- more parameters from Excel are recognized

- short manual included

Valid parameters in version 1.2 are:

table:destination_table_no
TableHeader:Row_no_with_column_headers
ImportAll
SkipTriggers
Const:Destination_Field=Constant_Value
Incr:Destination_Field=Counter_starting_value

WARNING - Importer default settings, parameter keywords and header text formatting dependence are slightly changed from the previous version. Consult included manual

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.

Guidoo

Awesome tool!
Automatic mapping of fieldnames goes very well.
Great job!

mrQQ

do you think you could do field matching using FIELDCAPTION?

Slawek_Guzek

:shock:
Importer by default matches fields by FIELDCAPTION :-k

In order to change this you need to change function call in OnRun trigger in Codeunit 99991 from ImportData(0,'',128+8 ) to ImportData(0,'',128+16+8 ) if you wan to match fields by field name

searching for fields in Excel header
d5 d4
0 0 - match Excel column header to Field Caption
0 1 (importOptions=16) - match Excel column header to Field Name
1 x (importOptions=32) - match Excel column header to Field No.

mrQQ

oh, sorry, dumb me:) it's just that in match window it displayed field caption in one place, and field name in another..

Andwian

Hi Slawek,

TWO THUMBS UP for its flexibility! =D>

Thanks for making importing easier!

Sog

Is it me or is it impossible to run the silent mode? (because 2147483648 can not be converted to integer)

dipakpatel2505

Hi All,
After Import Universal Excel Import V1.2 in Nav 2009 Database,
If I am getting following error:
1) When I compile codeunit 99991 Universal Excel Importer,I am getting Error at "SelectExcelSource()" function. (Error 1 Screenshot).
2) When I directly run Codeunit 99991 Universal Excel Importer,I am getting Error. (Error 2 Screenshot).
Please See screen shot for Error Description.


With Best Regards,
Dipak Patel

mahesh

Hey,

Thanks for uploading this tool. I have been using this since last 1.5 years. It's just fantastic.

Here i have one request to you..
Have you worked upon RTC version of Universal Excel Importer OR Can you have that one as well?


Thanks,
Mahesh Jain

Hayan

i am trying to use the Universal Excel Importer with NAV 2009, but i am not able to do that , is it compatible with NAV2009, i am trying to import to Barcode Table i am getting an error that there is no excel buffer within the filter Filter Row No: >1 , my excel sheet is 2 columns ( barcode and Item number) am i missing anything.

Please anybody can help on this...

Thnx
Hayan