Empty spaces while Importing Excel

Business Central/NAV User Forum
1

Hi,

I tried importing excel sheet to grn.journal table…Everything works fine but got 2 problems

  1. Am getting a empty line before every record in the table…

2)if i have row in excel with single digit numbers that row is not imported

My coding is as follows

Window.OPEN(
Text007 +
‘@1@@@@@@@@@@@@@@@@@@@@@@@@@’);
Window.UPDATE(1,0);
TotalRecNo := ExcelBuf.COUNT;

RecNo := 0;

ExcelBuf.SETRANGE(ExcelBuf.“Column No.”, 1);
RecNo := 0;
IF ExcelBuf.FIND(’-’) THEN BEGIN
REPEAT

RecNo := RecNo + 1;
Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
IF RecNo <> 1 THEN BEGIN
“Gen.journal”.INIT;
“Gen.journal”.“Journal Template Name” := ‘PURCH’;
“Gen.journal”.“Journal Batch Name” := ‘AMEX’;
LineNo:=LineNo+10000;
“Gen.journal”.“Line No.” := LineNo;
“Gen.journal”.INSERT;

ExcelBuf2.SETRANGE(ExcelBuf2.“Row No.”,ExcelBuf.“Row No.”);
ExcelBuf2.SETRANGE(ExcelBuf2.“Column No.”, 1);
IF ExcelBuf2.FIND(’-’) THEN
IF EVALUATE(“Gen.journal”.“Document No.”, FormatData(ExcelBuf2.“Cell Value as Text”)) THEN;

ExcelBuf2.SETRANGE(ExcelBuf2.“Row No.”,ExcelBuf.“Row No.”);
ExcelBuf2.SETRANGE(ExcelBuf2.“Column No.”, 2);
IF ExcelBuf2.FIND(’-’) THEN
IF EVALUATE(“Gen.journal”.“Posting Date”, FormatData(ExcelBuf2.“Cell Value as Text”)) THEN;

ExcelBuf2.SETRANGE(ExcelBuf2.“Row No.”,ExcelBuf.“Row No.”);
ExcelBuf2.SETRANGE(ExcelBuf2.“Column No.”, 3);
IF ExcelBuf2.FIND(’-’) THEN;
IF EVALUATE(“Gen.journal”.Description, FormatData(ExcelBuf2.“Cell Value as Text”)) THEN;

ExcelBuf2.SETRANGE(ExcelBuf2.“Row No.”,ExcelBuf.“Row No.”);
ExcelBuf2.SETRANGE(ExcelBuf2.“Column No.”, 5);
IF ExcelBuf2.FIND(’-’) THEN
IF EVALUATE(“Gen.journal”.“Shortcut Dimension 1 Code”, FormatData(ExcelBuf2.“Cell Value as Text”)) THEN;

ExcelBuf2.SETRANGE(ExcelBuf2.“Row No.”,ExcelBuf.“Row No.”);
ExcelBuf2.SETRANGE(ExcelBuf2.“Column No.”, 6);
IF ExcelBuf2.FIND(’-’) THEN
IF EVALUATE(“Gen.journal”.“Shortcut Dimension 2 Code”, FormatData(ExcelBuf2.“Cell Value as Text”)) THEN;

ExcelBuf2.SETRANGE(ExcelBuf2.“Row No.”,ExcelBuf.“Row No.”);
ExcelBuf2.SETRANGE(ExcelBuf2.“Column No.”, 7);
IF ExcelBuf2.FIND(’-’) THEN
IF EVALUATE(“Gen.journal”.“Credit Amount”, FormatData(ExcelBuf2.“Cell Value as Text”)) THEN;

ExcelBuf2.SETRANGE(ExcelBuf2.“Row No.”,ExcelBuf.“Row No.”);
ExcelBuf2.SETRANGE(ExcelBuf2.“Column No.”, 8);
IF ExcelBuf2.FIND(’-’) THEN
IF EVALUATE(“Gen.journal”.“Bal. Account No.”, FormatData(ExcelBuf2.“Cell Value as Text”)) THEN;

LineNo:=LineNo+10000;
“Gen.journal”.“Line No.” := LineNo;

IF NOT “Gen.journal”.INSERT THEN
“Gen.journal”.MODIFY;
ClearVariables;

END;
UNTIL ExcelBuf.NEXT = 0;
END;
MESSAGE(‘Import Successfully Completed’);

2

I suggest use data port for importing the data in Table. It will be easier