Excel Import Problem

haryramani

Hi,

I tried importing excel sheet to grn.journal table...Importing works fine but i have got 2 problems

1) Am getting a empty rows 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 +
'@\');
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';
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');

lavanyaballurgi

I couldn't go through your entire code but you should be getting empty lines because you may be incrementing the line no. twice.
regarding not importing the row with single digit can I know which column are you filling this single digit?

And what are you not using Dataport for importing?

haryramani

1)My code is reading number of lines in the excel files correctly but in table it is not importing......
2)And in excel buffer all line are imported(i.e even the single digit rows are imported)

The single digit is for one of the dimension code.....!!
Nope,am not using dataport....!!

knm

Simply searching your codes for "INSERT" statements, I see that you have 2 lines that are executing INSERT.
If you are not inserting set of 2 lines at once, you may want to revise that part.

haryramani

knm wrote:

Simply searching your codes for "INSERT" statements, I see that you have 2 lines that are executing INSERT.
If you are not inserting set of 2 lines at once, you may want to revise that part.

Gr8 bro ..!! thats d pblm,solved ...!!!

My first record of the excel sheet alone not importing .....Dunno why ](*,)

haryramani

My first record of the excel sheet alone not importing ..Can sum1 tell y ?

SPost29

Hi
Your Code says

IF ExcelBuf.FIND('-') THEN BEGIN
 REPEAT

 RecNo := RecNo + 1;
 Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
 [b]IF RecNo <> 1[/b] THEN BEGIN
 "Gen.journal".INIT;
 "Gen.journal"."Journal Template Name" := 'PURCH'

There are no instructions on what to do when recno = 1

haryramani

SPost29 wrote:

Hi
Your Code says

IF ExcelBuf.FIND('-') THEN BEGIN
 REPEAT

 RecNo := RecNo + 1;
 Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
 [b]IF RecNo <> 1[/b] THEN BEGIN
 "Gen.journal".INIT;
 "Gen.journal"."Journal Template Name" := 'PURCH'

There are no instructions on what to do when recno = 1

Thanks bro thats d problem i have set >=1 ...now working correctly..