Excel Buffer to insert in Table 81 (Gen Journal)

[Mohamed_Zayed]

Dear All,

I am new to the excel buffer table, I made some reading and I know that this table are user to export data from Navision to Excel sheet. I downloaded the following example from mibuso and It just great, but the problem is that this example are used to export data to an excel table (See Picture below):

Pic 1: Here I am running the report:

Pic 2: Here is the code:

Now The question is, how can I change this code to make it import a excel table into a Gen.Journal lines (table 81). Please try to provide me with the small details Coz I never tried this before.

Thanks all for help,

Zayed

[archer89]

hi,

you can download a sample code for importing with excel buffer her:

http://www.dynamics.is/?p=409

further infos:

http://www.mibuso.com/forum/viewtopic.php?t=54622

best regards

[Mohamed_Zayed]

Hi,

How can I download it ? I canot see the download section ?#

BR

[archer89]

a bit hard to find.

http://www.dynamics.is/?attachment_id=555

there click on the link arounded with the red line.

best regards

[babrown]

Have a look at the GL Budget form/page. It contains good examples of both import and export.

[Mohamed_Zayed]

Dear all,

I tried to do my best to write the code which will export the Excel tabel. the table looks like this :

The code which I created is as follow:

[Mohamed_Zayed]

can any one help and tell me if what i did is correct ? the code run error free but still its not importing

[archer89]

hi,

i tried following:

1. created a simple excel document “test1.xls”, name of 1. sheet “Sheet 1”.

Column 1	Column 2	Column 3
1	10	Test 1
2	20	Test 2
3	30	Test 3

2. created in nav dev env. a new codeunit “Excel Import”.

// var.: xlsBuffer | Record | Excel Buffer

xlsBuffer.OpenBook(‘c:\temp\test1.xls’,‘Sheet 1’);
xlsBuffer.ReadSheet;

and ran the codeunit.

3. Result

Voila, there was the complete data from the excel file in the table “Excel Buffer”.

regards

[Mohamed_Zayed]

I will try to do this too but what If I wanted to insert in the Gen Journal Tabel directly !

The whole reason for this topic is that I want to import an excel sheet in the Gen Journal to import all entries then to post them later.

so, how can I say

• import in Excel buffer then

• Import from Excel buffel into General Journal tabel ?— > I guess here I need to map the columns

Thanks a lot really

[Mohamed_Zayed]

Thanks a lot for the example.

I will try it right away in order for me to understand more the excel buffer.

My big problem is:

I want to import in Excel buffer then I want to import in General Journal. The main reason for this topic is for me to learn how to import an excel sheet into General journal ?

We always get a sheet with has more than 200 lines, and the employees have to enter this manually in General Journal in order to post it.

So I want to do the following:

• After I imported the sheet in Excel buffer

• I want to import this lines from the excel buffer to the General Journal in order to post them

(I guess here I have to map the columns)

Can anyone tell me how to do it? My programing experience is let say 40%. But I am really keen to learn this and solve this problem.

Thanks again a lot

[archer89]

hi,

try the data migration utility of Nav.

Go to Administration Menu->Application Setup->Company Setup->Data Migration

There select Table 81 (Gen. Journal). Then specify the Data Migration fields .

You can then export the structure and Import it after filling the data.

Last step: Apply Migration Data

other option: import data using a xmlport/dataport.

regards

[Mohamed_Zayed]

Thanks again,

I did try your second solution and it work, now the problem is that the data is in tabel 81. and in order to see this I have to open the table from the object designer.

how can I import this directlly in the General Journal open form ?

Best regards,

Zayed

[archer89]

hi,

try first the data migration utility (last posting).

after that, if you wish, you can create a codeunit to copy the values from the excel buffer table to table 81.

regards

[Mohamed_Zayed]

I did try it and its working fine, All excel table entries are now in table 81

but this solved only 50% of the problem. now how can I copy this records from Excelbuffer to Genral Journal entries ?

Besr regards,

[archer89]

did you try the data migration utility (table 81, form 39)?

http://www.youtube.com/watch?v=O8uyrVi-ADE

[Mohamed_Zayed]

I will follow the video then I will Inform you about the results.

Thanks

[Mohamed_Zayed]

HI, I tried it and I at the end I pressed apply imported entry. but still when I open the General Journal the Form 39 was totally empty? any ideas

[archer89]

did you get migration errors?

if so, there are problems with your import data or the data structure.

so first export data and analyse the structure and the values.

remove all data rows in the excel table but one.

change the exported data in that row a little bit and import that data.

[archer89]

goto “migration errors” and there to “show errors”. it tells you which fields could not be imported.