Read Excel

tonypang

Does any one master here how to read the data from excel ?
Do you have any sample code to read the data from excel

example excel
ItemNo Desc B1 B2 B3 ..........................
A BBB 10 20 30 .......................




Result store in new table
Item No Location Qty
A B1 10
A B2 20
A B2 30


Any one who know how to do it?
Thank you for all master

kine

Use the Excel buffer to read the excel into excel buffer temp table and go through the records and process them.

Or save the file as csv and import it through Dataport...

tonypang

Example below is my Read excel code.

ExcelBuf.OpenBook('C:\Data.xlsx','Sheet1');
ExcelBuf.ReadSheet;


then how to get read the data by column then by row?
If i need to use this concept?

kine

if ExcelBuf.FINDSET then
repeat
  //process the record 
until ExcelBuf.NEXT=0;

What is hard to understand on that? ;-)

dimus

Take a look at report 81 - Import Budget from Excel, AnalyzeData function. You can find a good example of how to read data from Excel.

Luc_VanDyck

Let Me Search Mibuso For You: http://lmsmfy.com/?q=Excel+buffer+usage

rsaritzky

tonypang wrote:

Does any one master here how to read the data from excel ?
Do you have any sample code to read the data from excel

example excel
ItemNo Desc B1 B2 B3 ..........................
A BBB 10 20 30 .......................




Result store in new table
Item No Location Qty
A B1 10
A B2 20
A B2 30


Any one who know how to do it?
Thank you for all master

In your code, you have to first use the
ExcelBuf.ReadSheet
function to load the ExcelBuffer table.

Now, the Excel Buffer table contains one record for each column in your spreadsheet, row-by-row, i.e. if your spreadsheet has 5 columns and 10 rows, you will have 5 ExcelBuffer records for each row. The "important" fields to know about in the Excel Buffer table are:

Row No. - the row number (duh)
xlRowID - same row number
Column No - the column NUMBER (not letter)
xlColiD - the column LETTER

In your example, your columns appear to be your different locations.

So to populate a complete "NewLocation" record, you have to get values from several ExcelBuf records all from the same row.

So let's say you are on row 2 of your spreadsheet. I usually save the current row number in a variable and construct a loop something like this (Note that this loop has to be contained within a higher-level loop that will read all the ExcelBuf records, saving the "SaveRowNo" each time the "Row No." value changes:

DO WHILE ExcelBuf."Row No." = SaveRowNo
// As we loop through all the columns, save the appropriate values:
CASE xlColID Of
'A': // Column A is your item number
NewTable."Item No." := ExcelBuf."Cell Value as Text";

// Column B doesn't require decoding since it's not in the new table

'C': // Column C, i.e. location B1
BEGIN
NewTable.Location := 'B1';
// ExcelBuf always stores value as text, so you have to convert numerical values
EVALUATE(NewTable."Qty", ExcelBuf."Cell Value as Text");
END;
'D': // Column D, i.e. location B2
BEGIN
NewTable.Location := 'B2';
EVALUATE(NewTable."Qty", ExcelBuf."Cell Value as Text");
END;

'E': // Column E, i.e. location B3
BEGIN
NewTable.Location := 'B3';
EVALUATE(NewTable."Qty", ExcelBuf."Cell Value as Text");
END;

END;
ExcelBuf.NEXT;
END;
// At this point, Your NewTable record has the Fields "Item No." , "Location" and "Qty" populated, so...
NewTable.INSERT;


Now, the code can be made more efficient, but I think you get the idea.

Hope this helps.

tonypang

Thank you Ron

but for the
NewTable.Location := 'B1';
Now u fixed my Location not load from excel right ?
because my location may be more or can be less

So that location can't fix it.

How do loop dynamic

rsaritzky

tonypang wrote:

Thank you Ron

but for the
NewTable.Location := 'B1';
Now u fixed my Location not load from excel right ?
because my location may be more or can be less

So that location can't fix it.

How do loop dynamic

No - the Location is based on the column number using the CASE statement. Here's the logic in "pseudo-code":

Read the ExcelBuf record. Let's say it's row 1.
Save the row number (1) in SaveRow
//Now, check the column ID
If the column ID is 1, then you have cell row 1, column 1, which is the Item number
So save the "Cell Value of Text" in this record into the NewTable."Item No."

Now, read the next ExcelBuf record
If the ExcelBuf row is still 1, you are still in row 1 of your spreadsheet
//Now, check the other columns
If the column ID is 2, then you can skip the column because it's just the description.

Now, read the next ExcelBuf record
If the column ID is 3, you have cell row 1, column 2, which is column C, which is location B1
So save the value of "B1" into the NewTable."Location" and
Save the the content of this record ("Cell Value as Text") into the NewTable."Qty" field (you have to convert it
from Text to Decimal)

Now read the next ExcelBuf record
If the ExcelBuf row is still 1, you are still in row 1 of your spreadsheet
If the column ID is 4, then you have cell row 1, column 3, which is column D, which is location B2
So save the value of "B2" into the NewTable."Location", and
Save the the content of this record ("Cell Value as Text") into the NewTable."Qty" field (you have to convert it
from Text to Decimal)

Now read the next ExcelBuf record
If the ExcelBuf row is still 1, you are still in row 1 of your spreadsheet
If the column ID is 5, then you have cell row 1, column 4, which is column E, which is location B3
So save the value of "B2" into the NewTable."Location", and
Save the the content of this record ("Cell Value as Text") into the NewTable."Qty" field (you have to convert it
from Text to Decimal)

.....

I didn't use a code "window" in this forum (because I forgot), and I used a CASE statement along with a DO WHILE to control the looping. If you're unfamiliar with the CASE statement, here's the same code in a "code" window so it will be indented, hopefully illustrating it better. I added the outer control loop for a bit more clarity. I also corrected one minor flaw in the previous code - We don't INSERT the NewTable record until we've inserted a quantity, i.e. the column number we've read is 3 or higher....

ExcelBuf.Find('-');
// Start the loop by saving the first row number and initializing the NewTable record
SaveRowNo := ExcelBuf."Row No.";
NewTable.INIT;
// Now loop through ExcelBuf - use a Boolean to check for end of table
LoopDone := FALSE;
DO WHILE (ExcelBuf."Row No." = SaveRowNo) and (LoopDone = FALSE);
  // As we loop through all the columns, save the appropriate values:
  CASE ExcelBuf.xlColID Of
  'A': // Column A is your item number
       NewTable."Item No." := ExcelBuf."Cell Value as Text";

   // Column B doesn't require decoding since it's not in the new table

  'C': // Column C, i.e. location B1
      BEGIN 
         NewTable.Location := 'B1';
         // ExcelBuf always stores value as text, so you have to convert to a numerical value
         EVALUATE(NewTable."Qty", ExcelBuf."Cell Value as Text");
      END;

  'D': // Column D, i.e. location B2
      BEGIN 
         NewTable.Location := 'B2';
         EVALUATE(NewTable."Qty", ExcelBuf."Cell Value as Text");
      END;

  'E': // Column E, i.e. location B3
      BEGIN 
         NewTable.Location := 'B3';
         EVALUATE(NewTable."Qty", ExcelBuf."Cell Value as Text");
      END;

  END;   // End of CASE statement
  
  // At this point, we've read and analyzed the ExcelBuf record, but we've analyzed only one cell (row/col combination),
  // so check to see if the record is complete, i.e. it has an item number, location and quantity.
  IF (NewTable."Item No." <> '') and (NewTable."Location" <> '') and (NewTable."Qty" <> 0) THEN BEGIN
     NewTable.INSERT;
     // Get the record ready for the next location - save the Item number
     CLEAR(NewTable."Location");
     CLEAR(NewTable."Qty");
     // Now we can read the next ExcelBuf record.  Set the LoopDone variable to TRUE if there are no more records
     IF ExcelBuf.NEXT = 0 THEN
        LoopDone := TRUE;
     ELSE 
      // Check if we are on a new Excel Row
      IF ExcelBuf."Row No." <> SaveRowNo THEN BEGIN
        SaveRowNo := ExcelBuf."Row No.";
        // Also clear out the "Item No." field in the NewTable record
        CLEAR(NewTable."Item No.");
      END;
  END;    // End IF (NewTable."Item No.".....

END;  // End of DO WHILE loop

If you have more questions about the code, I'm happy to try to help. Just send me a Private Message.

tonypang

Thank you ron.
Current your method is fixed column A B C etc
can we make it dynamic?

kine

tonypang wrote:

Thank you ron.
Current your method is fixed column A B C etc
can we make it dynamic?

You can, but you need to somehow tell NAV,in which column is what... :-)

tonypang

Thank you.
I found the solution

David_Singleton

rsaritzky wrote:

No - the Location is based on the column number using the CASE statement. Here's the logic in "pseudo-code":

Read the ExcelBuf record. Let's say it's row 1.
Save the row number (1) in SaveRow
//Now, check the column ID
If the column ID is 1, then you have cell row 1, column 1, which is the Item number
So save the "Cell Value of Text" in this record into the NewTable."Item No."

:thumbsup:

That's an excellent post...


But you may want to seriously reconsider saying

rsaritzky wrote:

If you have more questions about the code, I'm happy to try to help. Just send me a Private Message.

Trust me, you really do NOT want this.

tonypang

Thank you.