Data Import

Business Central/NAV Developers Forum
1

Hello,

I need to achieve the following and am looking for advice on the best way to achieve.

I have an Excel spreadsheet using which I need to achieve the following.

  1. Create a record header for the permission lines. So this would be user name for example.

  2. For each user add each permission he/she will have based on the content of the file. To a subform.

Please help

2

Andrew,

Creating a spreadsheet with a “header” line and multiple “Detail” lines is very doable using the functions in the Excel Buffer table - table ID 370.

There is a sample of how to do this on MIBUSO - here’s the link:

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

Or search for “Demo of Excel buffer usage” on the "Downloads page.

That example shows how to export single records, but I have used this technique to create header/line exports. Create the export report (this technique uses Report objects, not dataports or xmlports) with multiple dataitems. Include a “Record Type” field so on the import you can tell if the record being read is a header or line. For example, here’s a code snippet. The header in this case is “Issue” (our bug tracking system) and the detail lines are objects that were corrected for the issue. For the header section, the first column is the character “H”:

RowNo := RowNo + 1;

ColNo := 1;

EnterCell(RowNo, ColNo, ‘H’, ‘’, FALSE);

ColNo := ColNo + 1;

EnterCell(RowNo, ColNo, “Issue”.“No.”, ‘’, FALSE);

ColNo := ColNo + 1;

EnterCell(RowNo, ColNo, “Issue”.Description, ‘’, FALSE);

ColNo := ColNo + 1;

EnterCell(RowNo, ColNo, “Issue”.“Lead Developer”, ‘’, FALSE);

ColNo := ColNo + 1;

EnterCell(RowNo, ColNo, FORMAT(“Issue”.“Dev Completed”), ‘’, FALSE);

ColNo := ColNo + 1;

Then, a second indented dataitem for the objects included in the issue has this code. The first column is the character “L”

RowNo := RowNo + 1;

ColNo := 1;

EnterCell(RowNo, ColNo, ‘L’, ‘’, FALSE);

ColNo := ColNo + 1;

EnterCell(RowNo, ColNo, “Issue Object”.“Issue No.”, ‘’, FALSE);

ColNo := ColNo + 1;

EnterCell(RowNo, ColNo, FORMAT(“Issue Object”.Type), ‘’, FALSE);

ColNo := ColNo + 1;

EnterCell(RowNo, ColNo, FORMAT(“Issue Object”.ID), ‘’, FALSE);

ColNo := ColNo + 1;

The resulting excel file looks something like this:

H Issue01 Remove Restrictions on Pick Ron Yes

L Issue01 Table 50066

L Issue01 Form 50118

L Issue01 Report 50054

L Issue01 Report 50228

L Issue01 Codeunit 50013

L Issue01 Page 50118

3

Sorry - I pressed “Send” to early. I was going to conclude by saying that you create a second report that reads the spreadsheet and looks at the first field to determine which type of record to INSERT or copy or whatever you want to do.

Give this a try. If you need a more sophisticated example than is available on MIBUSO, send me a PM and I can send you some additiional samples.

Good luck!

Ron