Skip to main content
H
hpanchamoorthy1-Visitor
1-Visitor
September 20, 2016
Question

How to load data into a grid from file repository which contains an excel file?

  • September 20, 2016
  • 1 reply
  • 9363 views

Hi there...

I'm trying to get the excel file content from file repository and load it into gird.

I have tried a couple of services such as csv parsing and load text. While using csv parsing service, i'm getting the content but not able to load it into the grid.(I checked it in debug option)

I'm a beginner to this platform. It will be very helpful if i get some suggestions.

1 reply

C
CarlesColl1-Visitor
1-Visitor
September 20, 2016

Actually there isn't any service to read XLS files, to read it out-of-the-box with TW, the file should be a CSV file or a simple TXT file.

    C
    CarlesColl1-Visitor
    1-Visitor
    September 21, 2016

    Hi,

    If you had build a service to read the CSV file, you just need that this result returns the Infotable which it's readed from the CSV. If you are reading the CSV file with CSV parser extension ( https://marketplace.thingworx.com/Items/csv-parser ) , you just need to:

    var result = Resources["CSVParserFunctions"].ReadCSVFile(params);

    And set Infotable as Service result type.

    Then you can build a mashup that consumes this service and bind the service result to the grid.

    A
    Ascherer1716-Pearl
    16-Pearl
    May 10, 2018

    If this is from an excel file and you're not adding any rows to the table, you can try:

     TotalTime = LastTimestamp - FirstTimeStamp

     TotalCycleTime = summation of all cycle times

    Availability should be = (TotalCycleTime / TotalTime) * 100 = Y%

     

     Actually getting the summation of cycle times may be a little tricky.  Going by the screenshot, cycle time seems to be based on when part_count > 0.  So you could do something like:

     

    var totalCycleTime = 0;
var start = 0;
var end = 0;
var lastPart_Count = 0;
for(var x in table.rows){
     var newPart_Count = table.rows[x].part_count;
     //if new part count == old part count OR counts are both positive (not sure if this happens?),
     // no transition of part_count so go to next iteration
     if((newPart_Count == lastPart_Count)||((newPart_Count > 0)&&(lastPart_Count > 0)){
     continue;
     }
     //else there was a transition from 0 to a number or vice versa
     //if transition from positive # to 0, set end time, calculate cycle time and add to total
     elseif(newPart_Count == 0){ 
     end = table.rows[x].timestamp;
     totalCycleTime += (end-start);
     lastPart_Count = newPart_Count; // = 0
     start = end = 0;
     }
     //If transition from 0 to positive #, set start time and last part count
     elseif(newPart_Count > 0){ 
     start = table.rows[x].timestamp;
     lastPart_Count = newPart_Count;
     }
     else{
     //ERROR HANDLING
     }

     This is a quick and dirty algorithm of what I understand you're trying to do.  It could definitely be improved and needs error checking.

     

     

    Terms & ConditionsCookie settingsAccessibility statement