Import excel via reports(excel buffer)

Hi, jus lik Financial Management=>General ledger=>Budgets i want to add a function “Import from excel” in purchase journals…

I tried surfing and also saw report 81 but i cannot understand how they are importing using excel buffer… can sum1 give a small example on how to import using reports (excel buffer) jus lik dis savatage99.googlepages.com/Excel … ummies.doc (this 1 is for export and i need for import)…

And also in report which data item should i add,a created table for this (or) table related with purchase journal(general journal line) ???

Did you check DataMigration option under Administration–>Application Setup–>Company Setup

i jus now checkd…Am not able to get it ,can we do it even without using report…???

XLSDataFile := CommonDialogMgt.OpenFile(Text004,’’,DefaultFileType::Custom,’*.Xls’,Action::Open);
IF XLSDataFile = ‘’ THEN
EXIT;

MigrationMgt.ImportFromExcel(XLSDataFile);

Wat they have done , i cant understand…[:’(]

i jus now checkd…Am not able to get it ,can we do it even without using report…???

XLSDataFile := CommonDialogMgt.OpenFile(Text004,’’,DefaultFileType::Custom,’*.Xls’,Action::Open);
IF XLSDataFile = ‘’ THEN
EXIT;

MigrationMgt.ImportFromExcel(XLSDataFile);

Wat they have done , i cant understand…[:’(]

Read the Help or contract your senior in office how to use it

I dont have anybody 2 guide here…anyways thanks…!!!

Have a look at this video

http://www.youtube.com/watch?v=O8uyrVi-ADE

i saw that video…But i need to do only using reports…

am having aa bit complicated excel 6 different columns Column A-Posting date Column B-Document No Column C-Description Column D-Account Name(not account No.) Column E-Amount Column F-Balance Account No. can sum1 give some basic or example coding for importing these datas into respected table…

Hi Harish,

Following links might help you to get the output of ur report in excel

http://dynamicsuser.net/forums/t/54227.aspx

i tried some coding

When i run am getting a dialog box with message

“The Gen.Journal Line already exists”
Identification fields and values

Journal Template Name=",Journal Batch=",LineNo=0;

My coding(i jus tried to import 1 column )

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 > 3 THEN BEGIN
GenJournalLine.INIT;
WITH GenJournalLine DO BEGIN
ExcelBuf2.SETRANGE(ExcelBuf2.“Row No.”,ExcelBuf.“Row No.”);
ExcelBuf2.SETRANGE(ExcelBuf2.“Column No.”, 1);
IF ExcelBuf2.FIND(’-’) THEN
IF EVALUATE(“Document No.”, FormatData(ExcelBuf2.“Cell Value as Text”)) THEN;
GenJournalLine.INSERT;
ClearVariables;
END;
END;
UNTIL ExcelBuf.NEXT = 0;
END;

Increase Lineno before Insert

Lineno:=Lineno+10000;

If you are importing data in Gen Journal Table then Use Dataports…

they PK of Gen. Journal Line is: Journal Template Name,Journal Batch Name,Line No.

you need to assign these 3 before INSERT, which I didn’t see in your code. the error come from;
first insertion → ‘’,’’,’’
2nd insertion → ‘’,’’,’’ ← this is duplicate

I got that error fixed… that error was with lineno… Now my code reads the total number of records…

Now I need proper coding for importing the data from excel to the table…

GenJournalLine.INIT;
GenJournalLine.“Journal Template Name” := ‘PURCH’;
GenJournalLine.“Journal Batch Name” := ‘DEFAULT’;

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 REPEAT
MESSAGE(FORMAT(ExcelBuf.“Row No.”));
RecNo := RecNo + 1;
Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
{
GenJournalLine.INIT;
GenJournalLine.“Journal Template Name” := ‘PURCH’;
GenJournalLine.“Journal Batch Name” := ‘DEFAULT’;
LineNo:=LineNo+10000;
GenJournalLine.“Line No.” := LineNo;
GenJournalLine.INSERT;
}
GenJournalLine.INIT;
WITH GenJournalLine DO BEGIN
ExcelBuf2.SETRANGE(ExcelBuf2.“Row No.”,ExcelBuf.“Row No.”);
ExcelBuf2.SETRANGE(ExcelBuf2.“Column No.”, 1);
IF ExcelBuf2.FIND(’-’) THEN
IF EVALUATE(GenJournalLine.“Posting Date”, FormatData(ExcelBuf2.“Cell Value as Text”)) THEN;
LineNo:=LineNo+10000;
GenJournalLine.“Line No.” := LineNo;
GenJournalLine.INSERT;
ClearVariables;
END;
UNTIL ExcelBuf.NEXT = 0;