Reading all data from a table

If it is necessary to frequently read data from files (Excel or CSV), it is better to once read the data to an array, and then address the array to screen its elements for the retrieval.

In this recipe we will take up an example of assigning the data from Excel file to an array in such a manner that the elements of the array are easily accessible via the use of the line number and the column heading of the table.

Getting ready

Create an Excel file c: eadall.xls with the following contents:

Getting ready

How to do it...

To read the data from the file into an array, we will need to go about the following actions:

  1. First open the file with the help of the DDT.ExcelDriver method (to this end, we will need the path to the file and the name of the spreadsheet).
  2. With the help of the while loop, iterate through all of the lines of the file, each time adding a new element to the lines array.
  3. For each of the elements of the lines array that correspond to the line, create the properties with the names that tally up with the names of the columns. For example, if the table has a FirstName column, then each element of the array will have the FirstName property.
  4. In the result, the function will appear as follows:
    function DDTReadAll(file, sheet)
{
  var lines = [];
  
  var data = DDT.ExcelDriver(file, sheet);
  while(!data.EOF())
  {
    var line = {};
    for(var i = 0; i < data.ColumnCount; i++)
    {
      var colName = data.ColumnName(i);
      line[colName] = data.Value(colName);
    }
    lines.push(line);
    data.Next();
  }
  return lines;
}
  5. The following example demonstrates use of the function:
    var people = DDTReadAll("C:\readall.xls", "Sheet1");
Log.Message(people[1].First);

How it works...

This example will output the message John to the log (the name from the first line of the file). After having read the data once into the people variable, we can address similarly any other fields and lines.

The same approach to handle the data retrieval from a file allows, first of all, to bring up to speed accessing the data since we need to just once open the file and read all of its content out, after which referrals are made inside the computer memory; secondly, this will improve code readability due to the comprehensible names of the properties and lines indices.

Besides, it is much easier and handy to manipulate the data that are being stored in the variables, rather than with the data that is being stored in a file.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.216.77.153