Excel to Sales Lines

roshandilruk

Dear All,

I have one scnario that the client will create Sales Order header details and for lines they want to import from a excel sheet. They have a seperate excel sheet where they enter Line details, in the Sales Order form they want to call that excel file and import in to the Lines.

Any idea how to overcome this?? Any codings??

Nikk

This can be done using a dataport ....

David_Cox

Nikk wrote:

This can be done using a dataport ....

The Dataport cannot import from Excel xls without saving to csv first

So look at the Import from Excel report on the system Report 81, this reads a sheet and create a Budget.

It should give you a guide to get started.
The columns in the Excel should refect how you would enter data by hand

Read the sheet into the Excel Buffer then use the excel buffer Row and Column Number to Add the Lines, Here is a sample to get you started!

Headings in Row 1 data starts in Row 2
Columns = 1: Type, 2: "No." 3: Quantity
Function Readsheet has been run

Window.OPEN( '@1@@@@@@@@@@@@@@@@@@@@@@@@@\');
Window.UPDATE(1,0);
TotalRecNo := ExcelBuf.COUNT;
RecNo := 0;
NextLineNo :=0;
IF ExcelBuf.FIND('-') THEN BEGIN
  REPEAT
     RecNo := RecNo + 1;
     Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
     IF ExcelBuf."Row No." > 1 THEN BEGIN
        CASE ExcelBuf."Column No." OF
          1: //New Line
            IF ExcelBuf."Cell Value as Text" <> '' THEN BEGIN
               NextLineNo := NextLineNo + 10000;
               SalesLine.INIT;
               SalesLine."Document Type" := SalesHeader."Document Type";
               SalesLine."Document No." := SalesHeader."No.";
               SalesLine."Line No." := NextLineNo.
               SalesLine.INSERT(TRUE);
               EVALUATE(SalesLine.Type,ExcelBuf."Cell Value as Text");
               SalesLine.MODIFY;             
            end;           
          2:
            IF ExcelBuf."Cell Value as Text" <> '' THEN BEGIN         
               EVALUATE(SalesLine."No.",ExcelBuf."Cell Value as Text");
               SalesLine.VALIDATE("No."); 
               SalesLine.MODIFY;
            END;
          3:
            IF ExcelBuf."Cell Value as Text" <> '' THEN BEGIN         
               EVALUATE(SalesLine.Quantity,ExcelBuf."Cell Value as Text");
               SalesLine.VALIDATE(Quantity); 
               SalesLine.MODIFY;
            END;
     END;
  UNTIL ExcelBuf.NEXT = 0;

Nikk

David Cox wrote:

Nikk wrote:

This can be done using a dataport ....

The Dataport cannot import from Excel xls without saving to csv first

Hi David,
I did not mention that he would have to save the worksheet as a csv first before importing :-# ... but its the alternative I was trying to suggest....we already have a scenario like this with a client.. The Import from Excel function you suggested is of course the other way...

David_Cox

Hi Nikk

Just did not want to give the idea that a Dataport will upload a xls.

Saving as csv is fine in most cases, but I prefer Excel, you cannot always import a csv file from UK to USA for example, as the date is a different format, and some countries format the decimal as 1.000,00 as in the UK 1,000.00, these scenario's Excel deals with, and if you are getting data from an external company, and you open and save as csv, if anything goes wrong who gets the blame.

Likewise if you are getting a csv file, use a dataport, do not open in Excel, use notepad to view, as Excel has been know to change the format, leading zero's etc: :shock:

I recently created a codeunit to update exchange rates, by getting a EUR to currency rate, csv file from the web, all was fine I dealt with the date for the USA, but forgot about the decimals, it was tested on all databases, but when a user had a regional setting that changed the Decimal from 1.2345 to 1,2345, we got some strange FX rates :?

Had this been Excel no problem!

David