xmlport

Mass data import

4 Comments

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

2 Comments

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