Can anyone help understand how I can import a .xls or .csv file into Navision to create a payable to a Vendor and to import data as a journal entry?
Hello Noeli and welcome to Dynamics User Group,
Generally we use dataport for importing data from excel, csv files into navision. So in your case, you might need to write a new dataport for importing this data.
Please don’t cross post.
We have one of these! We send our damaged merchandise to a reclamation center, who processes it and sends back to us a datafile that we import into the purchase journal as credits to our vendors.
The data is pretty simple…Just
Invoice Date
Invoice Number
Vendor No.
Amount
7/7/2011,100035,112214,13.04
7/7/2011,100035,111205,149.3
7/7/2011,100035,111110,141.64
Using Gen. Journal Line as the dataitem
Variables are:
CreditCount type->Integer
VenNo Type->Text 30
MonthName Type->Text 30
Vendor Type->Record->Vendor
Dataport Fields
“Document Date”
“External Document No.”
“Account No.”
Amount
Constants are:
Text0001 → %1 Credits Imported / Would You Like To Delete The File?
OnPreDataport()
CreditCount := 0;
OnPreDataItem()
“Posting Date” := TODAY; // I set MY posting Date
“Journal Template Name” := ‘PURCHASES’;
“Journal Batch Name” := ‘DEFAULT’; //You might have a differnet batch name in mind
“Document Type” := “Document Type”::“Credit Memo”; //I know they will only be credits so I can set it here, else you can deal with it OnAfterImportRecord
“Bal. Account No.” := ‘50300’; //again as above I know the balance account I already want.
“Account Type” := “Account Type”::Vendor;
“Line No.” := 1; //this is so I can count the number of imported lines.
OnAfterImportRecord()
IF Vendor.GET(“Account No.”) THEN BEGIN
IF Vendor.Blocked THEN BEGIN
MESSAGE(‘Vendor %1 Blocked! Not Imported!’,Vendor.“No.”);
CurrDataport.SKIP;
END ELSE BEGIN
VenNo :=“Account No.”;
MonthName := FORMAT(TODAY,0,’<Month Text,3>’); //this is all beacuse I want a specific document no. you datafile might provide one
“Document No.” := VenNo+’-’+MonthName;
“Line No.” := “Line No.” + 10000; //increment my line
VALIDATE(“Account No.”);
Description := ‘MetRec Purchase Returns’; //I’m setting a description-optional
CreditCount := CreditCount + 1; increases my import count
END;
END ELSE BEGIN
MESSAGE(‘Vendor %1 Does Not Exist! Not Imported!’,“Account No.”);
CurrDataport.SKIP;
END;
OnPostDataport()
CurrFile.CLOSE;
IF CONFIRM(Text0001,TRUE,CreditCount) THEN
ERASE(CurrDataport.FILENAME);
Now I designed this for a specific need. you can use it as a guide. see if helps
PS one of the things I like to do when making a new dataport is after I import some data, I will manually enter a line myself.
I then go to the table to view both lines(imported Line & manually entered line) or use zoom.
I do a comparison to make sure all the necessary fields have been updated else you are missing a validate somewhere.
If you look at Report 81 Import Budget to Excel you can get a good example.
I made a copy of this and modified it to Import other things using the Excel Buffer table.