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

[hpanchamoorthy]

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.

[CarlesColl]

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.

[hpanchamoorthy]

Hi Carles,

You're correct. I have also tried CSV file but not able to load the content to grid. I would like to explain the task that i'm doing.

1. I shall import any csv file to the file repository.

2. The path of the csv file shall be taken using **GetFileInfo** service.

3. Then, mapping the path from **GetFileInfo** service to **LoadText** service shall provide the csv content. (I have done till this)

4. Here's the final one...​ When I try to load the content, the service throws an exception saying "Datashape must be specified". If I know the content of the table, I can create DataShape for that.(Please correct me if i'm wrong) But in my case, i don't know about the content and want to load it to grid.

Will rephrase if this is not clear.

Thanks.

[CarlesColl]

Hi,

Step 4. If you don't know the format of CSV file you can't use CSVParserFunctions, as they expect that you know the format before and you pass it as a DataShape.

If you need to load a free format CSV file, then you will need to use FIleRepository.LoadText({ path: }) service and parse the file by hand and then build the Infotable. Another option can be to Build your own extension using some powerful CSV java parser library.

Best Regards,

Carles.

[CarlesColl]

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.

[jgabriel]

Hello Charles, any experience with encodings? We are using ReadCSVFile, but it only works with utf8 encoded file?

Is it possible to convert somehow within TX?

[CarlesColl]

Hi Jan,

No we don't have experience out of UTF4, you may decompile the CSV Read File extension and customize to your needs, we already did to support correctly "," instead of "." for decimal point...

Carles.

[vsquaresystem]

Hi Carles,

I did exactly you mentioned, I got results in result but i am unable to display it on grid over mashup.

Plz help

thanks

Vikas

[Ascherer17]

If setting up a datashape isn't working for you, try ticking the 'ShowAllColumns' box on the Grid widget.  It somewhat forces the widget to display whatever you give it.  I think you just may not be able to edit the grid data.

[vikasdwankhede]

Thanks, it works for me!

[vikasdwankhede]

Hi,

I am getting continuous data from machine, I want to calculate real time values using timestamp difference of spacific intervals, Can you help me on this?

Regards

Vikas

[Ascherer17]

Are you wanting to display the time difference between all data entries or just a single difference value between 2 data entries?  

Basically you'll query the data history for the Thing using QueryPropertyHistory or one of the single property queries (i.e. QueryNumberPropertyHistory), then you can reference the result Info Table and find the difference between the timestamp values of the 2 rows that you want.  

Example:

var firstDate = new Date(result.rows[0].timestamp);  //milliseconds

var lastDate = new Date(result.rows[1].timestamp);  //milliseconds

var timeDifference = lastDate - firstDate;  //milliseconds

var resultDateTime = new Date(timeDifference);  //Date object

I've read that Thingworx uses Joda datetime format so you have to be careful when using Javascript's Date() class.  I believe it's able to convert the resultDateTime to DateTime without any trouble though.

My example digs into the InfoTable structure a bit.  There's a good document about the structure of InfoTables somewhere in the Thingworx document catalog that may help understanding InfoTables if you haven't looked into them much; I think it's called "Getting to Know InfoTables."  

[vsquaresystem]

Hi,

Thanks for your prompt reply, I want to calculate "Cycle time" as attached in the screenshot. summation of all "cycle time" is our "Operating time".

And from operating time, I will get actual "Availability" of Machine.

Please can you help me on this.

Regards

Vikas

[vikasdwankhede]

Any one Can help on this?

regards

Vikas

[Ascherer17]

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.

[vsquaresystem]

Thank you for your quick reply, I have attached excel sheet with corrected algorithm, can you please look inside it.

My Part Count changes to 1, 2, 3 respectively on every cycle time changes. 

eg. If timestamp start with tag value 3 till end with 3 and part count become to 1 , this is my first cycle time. then it will again start with o  and end to 0 and start from 3-3, it gives part count 2 with cycle time 2...... & So on...

Total Cycle Time (its nothing but 'Operating time') = Sum(Cycle Time1 + Cycle Time 2+ .... + Cycle Time N);

Availability = Total Cycle Time / Shift Time (it could be 8hours and its fixed);

Performance = (Parts Produced * Ideal Cycle Time[i.e its fixed])/ Total Cycle Time ;

this is something, i am going to achieve from the above calculations.

Best Regards

Vikas