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

Community Tip - Visit the PTCooler (the community lounge) to get to know your fellow community members and check out some of Dale's Friday Humor posts! X

How can i import the CSV or JSON file in Thingworx and put in a Data Table?

mpagliotta
1-Newbie

How can i import the CSV or JSON file in Thingworx and put in a Data Table?

How can i import the CSV or JSON file in Thingworx and put in a Data Table?

How can i import a CSV JSON file? I tried to the import button (data radio button) it told me that was imported successful the file. But where is imported?

How can i work on it with things, shape thing or template thing?

Thanks in advance!

33 REPLIES 33

any infos?

The import functionality expects a file which was exported by a Thingworx server; it won't handle an arbitrary CSV or JSON file..

If you want to import CSV data, you'll have to create a DataShape which matches the CSV content, create a DataTable or Stream which uses that DataShape, and then you have to provide the CSV data to a Service which will parse the data and insert it into the DataTable or Stream.

There are many ways to accomplish this last step, so it depends on your environment and system. You could use a FileRepository to store the file, or you can have a Mashup with a big TextArea that you can paste the CSV data into, or you could have the CSV file an HTTP server and use the ContentLoader Resource to download the CSV data. There are probably other ways, as well.

Thank you!

I create a Data table and a data shape as you suggest, bu ti do not know which service choose to populate my data table with csv data!

Schermata 2016-05-11 alle 14.05.18.png

i put also the csv in the repository

Schermata 2016-05-11 alle 14.06.21.png

I find out, also the extension CSV Parser , i need this extension or there is a service?

Thank you

In CSV Parser, for example i find out this service:

Schermata 2016-05-11 alle 14.16.33.png

but i do not know, how can i use it.

You'll probably want to build a Mashup which has widgets to allow you to choose the CSV file from your file repository, call the ParseCSVFile service, and view the results in a Grid. If the results looks right, then you can send them to your DataTable's AddDataTableEntries service.

Thank you.

I create a mash up and i put inside a grid.

in the data area

Schermata 2016-05-11 alle 23.47.55.png

but i cannot choose the ParseCSVService

Schermata 2016-05-11 alle 23.47.39.png

but i do not understand why this windows is not well sized (view cancel button and the left side of the window)

Maybe depend on the browser (Firefox in my case) ?

Thanks

Ok, i found it but i cannot push ok for that issue window.

Schermata 2016-05-11 alle 23.51.13.png

Schermata 2016-05-11 alle 23.54.17.png

i reduce the size from the option browser and now it works.

the next step is to trigger the data according the "to do"

Schermata 2016-05-12 alle 00.13.28.png

meanwhile i saw that the grid need an infotable as data

Schermata 2016-05-12 alle 00.32.51.png

it's a little bit confusing:

1) service ParseCSVfile that need a trigger

2)grid that need an infotable as source instead of data table

The Trigger for the ParseCSVFile Service will be the Click event of a Button you will add to the Mashup.

The InfoTable for the Grid will be the Returned Data (AllData) of the ParseCSVFile Service.

ParseCSVFile also takes Content, which is returned from LoadText on the File Repository (which you'll need to add as a Data Source).

LoadText takes a 'path' to the CSV file, which is available from any of BrowseFileSystem, GetFileListing, GetFileInfo, and I think a couple other services on the File Repository.

So, you'll want to add the File Repository as a Data Source, include whatever services you'll use to get to the CSV file, and include the LoadText service.

Bind the output of LoadText to the Content on ParseCSVFile.

Bind the output of ParseCSVFile to a Grid widget.

You can use the events ServiceInvokeCompleted or Service Completed Event to tie the services together.

Hope that helps.

Also, you might want to go through the online tutorials to learn more about building mashups.

Ok, thanks to you i begin to see the light.

I suppose that (image below) it is a possible right flow?

Schermata 2016-05-13 alle 00.22.28.png

If yes, i do not know how link "Data" to the repository and the right file (now is a unique CSV file).

i figure out this resolution path of my task:

mashup (invent: loaded)  -> ParseCSV service --> content -> loadtext (where is? i do not find)  --> BrowseFileSystem with GetFileListing and GetFileInfo   --> All Data  --> Datasource (how, where??)

button (event clicked)  -> ParseCSV service  -->gridtable?

Please be patient, but i'm a newbie of this platform!

There is a tutorial, or one step by step tutorial to pursuit this  import data task?

In the PTC University i attended the "Getting Started with Thignworx 6.5 (Part 2 Introduction to Thingworx 6.5)" course,

Schermata 2016-05-13 alle 00.27.56.png

I figure out, i think the work around, but why in my datatable the service GetDataTableEntries do not show any data?

Schermata 2016-05-15 alle 00.44.50.png

Where is the option where i can say to the service to get the data (from the scv in the repository folder)?

Best regards

Maybe shall i create a new service andin readCSVFILE puth the csv file path there?

Schermata 2016-05-15 alle 02.17.35.png

There is a place where i have to write the path for reading the csv?

making a little test  i suppose not!

Schermata 2016-05-15 alle 02.20.55.png

Probably this code will help.

var params = {

    path: "samplecsv2" /* STRING */,

    columnMappings: undefined /* STRING */,

    hasHeader: undefined /* BOOLEAN */,

    longitudeField: undefined /* NUMBER */,

    dateFormat: undefined /* STRING */,

    fileRepository: "fileUpload3" /* THINGNAME */,

    latitudeField: undefined /* NUMBER */,

    fieldDelimiter: "," /* STRING */,

    stringDelimiter: undefined /* STRING */,

    dataShape: "ThiruDataShape2" /* DATASHAPENAME */

};

// result: INFOTABLE

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

var i=0;

while(i < result1.getRowCount())

{

 

      var values = Things["DT1"].CreateValues();

    values.policyid  = result1.rows.policyid; //STRING

    values.line = result1.rows.line;

    values.statecode = result1.rows.statecode;

    values.lat = result1.rows.lat;

    values.longitude = result1.rows.longitude;

    var params2 = {

        values: values /* INFOTABLE*/

    };

    Things["DT1"].AddDataTableEntry(params2);

     i = i + 1;

}

I succeed to see the CSV data testing the service:

var params = {

                path: "csv/Thingworx_Teorema_1.csv" /* STRING */,

    columnMappings: undefined /* STRING */,

                hasHeader: true /* BOOLEAN */,

                longitudeField: undefined /* NUMBER */,

                dateFormat: undefined /* STRING */,

                fileRepository: "csvRepository" /* THINGNAME */,

                latitudeField: undefined /* NUMBER */,

                fieldDelimiter: "," /* STRING */,

                stringDelimiter: undefined /* STRING */,

                dataShape: "csv_Carpe_Datashape" /* DATASHAPENAME */

};

// result: INFOTABLE

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

But when i add the second part:

Schermata 2016-06-01 alle 00.04.22.png

I reedit and now test works fine.

What i hve to do to view the data in the datatable? Always by mashup or i can read directly?

Ok i find the GetDataTableEntries service in the datatable thing.

My question is: but if i have one million row to move from the csv to the datatable? test button i think does not support this  amount of data. How can i do?

Thanks in advance

Don't try to put 1milion rows on a DataTable, it aren't suited for it.

Read this first: Where Should I Store My Thingworx Data?

ok i figure out what you mean, so i have to find out a workaorund for import a million rows csv in  a platform storage?

i read: "  .Data tables can be used when contained in small data sets (around 100,000 records or less) you can use Platform storage for this as these are intended for largely static data structures. "

There is a place in the thinworx composer where can i test these "massive" services? In order to transfer  and work a large amount of data?

Or maybe is better upload the csv file in an external database, so thingworx read data from this external database , processing the million row creating new data and exporting that data in the external database?

You can connect easily to an external JDBC database with TW, and query that external database/datatable from within TW easily. Also you can insert data on the external database.

You have two extensions on the Market:

But depending on the time of data and when you will be storing, you can also look at Stream and ValueStream TW Storage options. TW also has capabilities for large amounts of data using Casandra storage for Stream and ValueStreams.

Best Regards,

Carles.

Thank you very much, but the services (with the calculation i would like to perform) where has to be runned? In which area of the composer?

becasue the unique areas was the Mashup area and when i push the "test" button on the service itself.

So my question is: if i use an external database , in which area of the composer i call the service of the thing of the extensions (database connector , connector extension) that read the data and process them?

now i saw only 2 work around:

extDB --> thing with service readData runned in the mashup --> export   (not well performed way)

or

extDB --> thing with service readData  -> click on test  button (service runned)--> export   (not well performed way)  (runned maximum 500 rows)

thanks

Hi Marco,

Which it's your experience on TW? If you don't have, why doesn't you follow any training courses or elearning materials from TW? I think you need some background and test before going to try to import this 1M rows on a external database from TW.

Best Regards,

Carles.

I followe the PTC University tutorial.

After testing the import, loading of view rows from an excel, the next step i think is import 1 million row like i have done with other tools like MongoDB.

In The PTC UNiversity i read only this, about the services:

Connecting to External Data Sources with ThingWorx 6.5 (Part 3 of Introduction to ThingWorx 6.5)

https://precisionlms.ptc.com/viewer/course/en/23003381/page/23594833

So, i know how create them, how test them, and i figure out that from the mashup area i can run the service  but it is not suitable for 1 million row.

I do not find any other area where i can perform this task.

Thanks

You can't do this out-of-the-box, you should write code in order to import 1milion rows from a file, something like this:

  • Put the file on a FileRepository folder
  • Read the file from FileRepository
    • into memory ( that will be a memory problem )
    • stream way ( I don't know if there's snippets for this, if not you will need a custom Java Extension )
  • Write data onto
    • Stream
    • Value Stream
    • External Database through the connectors already mentioned.

Ok, so i have to write the code for uploading 1 million rows, but, where do i write the code that should be runned without time issues (like in mashuup and service test button)?

Maybe it's easier than i think. There is any other area in the Thngworx Composer except service in order to achieve this task?

Or i have to coding only here:

Script New Service window:

Schermata 2016-06-03 alle 12.03.58.png

If the script area in the new service windows is the unique spot where i can put my code, How can i runn it without time issues (like in mashup) ?

This is my doubt that is not explained in the curse of PTC University.

Thanks again!

Marco

If it's a long running task, you can set "Async" flag to true, and it will run in background.

And yes here it's where you can put your code.

Top Tags