cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Upload CSV to the thingworx studio composer

SOLVED
Highlighted
Level 8

Upload CSV to the thingworx studio composer

Hi,

 

Could anyone assist me on how to upload a CSV file from the local machine to the thingworx studio composer. ?

I could also like to know how to use the csv parse extension.

Then I shall access the data from the studio using the external data pane tool. 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Upload CSV to the thingworx studio composer

Hi,

I am sorry for the delay but there was no time to test. Now I tested it and it works but it was for me also not easy to manage it. Yes for me the CVS methods are working only for strings- I tested a lot of formatstrings for date but it was not working so my solution is to use a service which create first a InfoTable with strings  and then in a second service  (is possible also in one service) to convert it to a correct data type.

So I have e.g. the following excel table:

2018-05-24_17-25-37.jpgThe excel table was added to repositrory as earler was already mentioned:

2018-05-24_17-35-08.jpg

So I defined two datashapes - the first one DS1 - there  the fileds are defined only as string and a second datashape DS3 where the field are defined with the correct data type. Also here have to mention , that I had difficulty to convert the german date format with milliseconds and separtor to the correct number. So the solution was to split every thing! So the source Data String was some thing like: "01.01.2018 00:00:00.010"

So I defined the first service   testReadCSV (retuns InfoTable) which convert only to string -something like this:

2018-05-24_17-44-15.jpg

And then in the second service  convertTypeReadCSV  (retuns InfoTable) converted it to the correct dataformat:

{
var result1 = me.testReadCSV();
var params = {
	infoTableName : "InfoTable",
	dataShapeName : "DS3"
};
var InfoTable = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);

for(var i=0;i<result1.getLength();i++){
var myDateStr=result1.getRow(i).getStringValue('DateTime').split(" ",2)[0];
var dd=myDateStr.split(".",3)[0];
var mm=myDateStr.split(".",3)[1];
var yyyy=myDateStr.split(".",3)[2];
var myTimeStr=result1.getRow(i).getStringValue('DateTime').split(" ",2)[1];
var HH=myTimeStr.split(":",3)[0];
var MM=myTimeStr.split(":",3)[1];
var SEC_ALL=myTimeStr.split(":",3)[2];
var ss=SEC_ALL.split(".",2)[0];
var millsec=SEC_ALL.split(".",2)[1];
var NrStr=result1.getRow(i).getStringValue('Nr');
var TorqueStr=  result1.getRow(i).getStringValue('Torque');
var myDate = new Date(yyyy,mm,dd,HH,MM,ss,millsec ); 
    InfoTable.AddRow({StripNr: Number(NrStr).toFixed(0),
                      DateTime:myDate,
                      Torque: Number(TorqueStr.replace(",",".")).toFixed(2)});
    }
result = InfoTable;
}

 So that when I tested it got the desired result:

2018-05-24_17-51-27.jpg

 

6 REPLIES

Re: Upload CSV to the thingworx studio composer

Here is a knowledge base article on using the CSV parse extension in ThingWorx Composer: https://www.ptc.com/en/support/article?n=CS255612

Re: Upload CSV to the thingworx studio composer

I think the documentation contains some very helpful examples for the definition of services

So another question is how to add this to the Thingworx instance. In the article is mention to use the FileRepository services. So here is a mashup which could be  helpful to upload files to a File repostitory (Entity for Mashup and demo File repository)

You can import it - so it contains a mashup + demo FileRepository thing (thing which use as template FileRepository Thing Template)

2018-05-04_10-28-50.png

So you can change the FileRepositroy thing with your own. The upload element could  select all elements which are availible as repostory in Thingworx  (using the FileRepostory template) but you will not see it in the tree. You can replace in the mashup the repository file "My_image-File" by your own repositroy file and set the same binding2018-05-04_10-39-22.png

Re: Upload CSV to the thingworx studio composer

Hi,

Sorry for the late reply. I worked with the CSV Parse function and I could read the CSV file from the local storage. Now what I want to do is, I would like to visualise the data in the studio experience. I have done it via the external data pane previous by giving random value inserted into the thing. So how to I get it here from the csv file to the external data pane?

Re: Upload CSV to the thingworx studio composer

Hi, 

Thank you for the reponse. It helped me alot. But i would like to bind and import the csv values to the studio experience. I could see the db and the data shape via the external data pane, but unable to see the parameters. I could read the csv file using the csv parse function via the ReadCSV call but the call worked only when I gave all the field values in the datashape as string, otherwise the ReadCSV throws a java error. 

Re: Upload CSV to the thingworx studio composer

Hi,

I am sorry for the delay but there was no time to test. Now I tested it and it works but it was for me also not easy to manage it. Yes for me the CVS methods are working only for strings- I tested a lot of formatstrings for date but it was not working so my solution is to use a service which create first a InfoTable with strings  and then in a second service  (is possible also in one service) to convert it to a correct data type.

So I have e.g. the following excel table:

2018-05-24_17-25-37.jpgThe excel table was added to repositrory as earler was already mentioned:

2018-05-24_17-35-08.jpg

So I defined two datashapes - the first one DS1 - there  the fileds are defined only as string and a second datashape DS3 where the field are defined with the correct data type. Also here have to mention , that I had difficulty to convert the german date format with milliseconds and separtor to the correct number. So the solution was to split every thing! So the source Data String was some thing like: "01.01.2018 00:00:00.010"

So I defined the first service   testReadCSV (retuns InfoTable) which convert only to string -something like this:

2018-05-24_17-44-15.jpg

And then in the second service  convertTypeReadCSV  (retuns InfoTable) converted it to the correct dataformat:

{
var result1 = me.testReadCSV();
var params = {
	infoTableName : "InfoTable",
	dataShapeName : "DS3"
};
var InfoTable = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);

for(var i=0;i<result1.getLength();i++){
var myDateStr=result1.getRow(i).getStringValue('DateTime').split(" ",2)[0];
var dd=myDateStr.split(".",3)[0];
var mm=myDateStr.split(".",3)[1];
var yyyy=myDateStr.split(".",3)[2];
var myTimeStr=result1.getRow(i).getStringValue('DateTime').split(" ",2)[1];
var HH=myTimeStr.split(":",3)[0];
var MM=myTimeStr.split(":",3)[1];
var SEC_ALL=myTimeStr.split(":",3)[2];
var ss=SEC_ALL.split(".",2)[0];
var millsec=SEC_ALL.split(".",2)[1];
var NrStr=result1.getRow(i).getStringValue('Nr');
var TorqueStr=  result1.getRow(i).getStringValue('Torque');
var myDate = new Date(yyyy,mm,dd,HH,MM,ss,millsec ); 
    InfoTable.AddRow({StripNr: Number(NrStr).toFixed(0),
                      DateTime:myDate,
                      Torque: Number(TorqueStr.replace(",",".")).toFixed(2)});
    }
result = InfoTable;
}

 So that when I tested it got the desired result:

2018-05-24_17-51-27.jpg

 

Re: Upload CSV to the thingworx studio composer

Hi,

This is good. I will execute this once. Thanks for the response, no matter if its delayed. Smiley Happy

 

I somehow managed to get the data read to the thingworx studio via the external data pane but only the first value is read from the excel. I want to display the values one by one, so:

how to put it in loop and where would I put that? Any idea on that, it would be great if u could help.