Mass data import

There was an issue with importing all UK post codes from a csv file, about 3m post codes. Importing using rapidstart services (excel import) can cause buffer overflow messages. excel itself has row/size limitations. Increasing MaxNoOfXMLRecordsToSend in config file ClientUserSettings.config from default value 5000 to e.g. 20000 is no problem and can help. Also changing MaxUploadSize in server config file CustomSettings.config is an option (also available via nav service admin console). Better choice for mass data import are dataports (older nav versions) and xmlports.

Another option is to develope a report, which imports the file contents and loops through the lines. quite simple, no memory issues.

create a new report, add following code to trigger OnPreReport():

OnPreReport()
// variables
 PostCode, Record, Post Code
 file, File
 fileName, Text, 250
 line, Text, 1024
 dlg, Dialog
 idx, Integer
 txtValue, Text, 100
// code
 PostCode.DELETEALL(FALSE);
 COMMIT;
 dlg.OPEN('#1###### #2######'); // show progress dialog
 idx := 1;

// downloaded post code file from https://www.doogal.co.uk/PostcodeDownloads.php
// as test file, size: 500k, 2.1m lines, some of them contain obsolete post codes
 fileName := 'c:\temp\England postcodes.csv';

 file.WRITEMODE := FALSE;
 file.TEXTMODE := TRUE;
 file.OPEN(fileName);
 file.READ(line); // skip header line
 WHILE file.READ(line) > 0 DO BEGIN
   // skip obsolete post codes: 2. value = No
   IF SELECTSTR(2,line) = 'Yes' THEN BEGIN 
     PostCode.Code := SELECTSTR(1,line);
     PostCode.VALIDATE(City,GetValue(SELECTSTR(15,line)));
     PostCode.County := GetValue(SELECTSTR(8,line));
     PostCode."Country/Region Code" := 'GB';
     PostCode.INSERT;
     dlg.UPDATE(1,idx);
     dlg.UPDATE(2,PostCode.Code);
     idx += 1;
   END;
 END;

 file.CLOSE;
 dlg.CLOSE;

 MESSAGE(FORMAT(idx) + ' post codes imported.');

LOCAL GetValue(txtValue : Text[100]) : Text
 txtValue := DELCHR(txtValue,'<','"'); // remove leading "  
 txtValue := DELCHR(txtValue,'>','"'); // remove trailing "
 IF STRLEN(txtValue) > 30 THEN // fields City,County are Text[30]
   txtValue := COPYSTR(txtValue,1,30); // cut text, leading 30 chars

 EXIT(txtValue);

report runs with 1.5m valid lines/records about 5 min.

cheers

Structured data import with Xmlports

An often needed functionality is importing existing data into nav. In many cases the data is structured and not only a simple csv file. there you maybe have connected data on at least 2 levels. Samples for that are sales orders, sales quotes, sales invoices.

As a sample i show, how to import a sales order containing 3 sales lines using a xmlport. Needed tables in this scenario are tables Sales Header and Sales Line. They are used as nodes in the xmlport. As you can see in the image below, first line is called SalesHeader (the node name), node type = element, source type = table, data source = sales header.

after that first step add the needed fields at structure level 1 (for that click -> button to shift right). the node name on a single level must be unique.  when selecting field as source type you can select the table field via assistant.

Next step is adding sub table “Sales Line” as new node on the same level as the “Sales Header” fields. now add the fields, you want to import,  under the new node. shift them to next level, level 2.

A really important thing now is to link the nodes! For that use node’s “Sales Line” properties LinkTable and LinkFields. Set LinkTable to Sales Header as shown above. Additional set property LinkFields to Document No.=Document No., Sell-to Customer No.=Sell-to Customer No., Document No.=No.. Save the changes.

With that xmlport i imported a sample sales order containing 3 sales lines without any problem .

the sample xml import file:
<SalesHeader>
<DocType>1</DocType>
<DocNo>101006</DocNo>
<CustNo>30000</CustNo>
<DocDate>2016-01-14</DocDate>
<SalesLine>
<DocType>1</DocType>
<DocNo>101006</DocNo>
<CustNo>30000</CustNo>
<LineNo>10000</LineNo>
<Type>2</Type>
<No>1920-S</No>
</SalesLine>
<SalesLine>
<DocType>1</DocType>
<DocNo>101006</DocNo>
<CustNo>30000</CustNo>
<LineNo>20000</LineNo>
<Type>2</Type>
<No>1000</No>
</SalesLine>
<SalesLine>
<DocType>1</DocType>
<DocNo>101006</DocNo>
<CustNo>30000</CustNo>
<LineNo>30000</LineNo>
<Type>2</Type>
<No>1001</No>
</SalesLine>
</SalesHeader>

cheers
%d bloggers like this: