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

Community Tip - Have a PTC product question you need answered fast? Chances are someone has asked it before. Learn about the community search. X

CSV Parser Help

jeffm
1-Newbie

CSV Parser Help

So, I am running 6.0 and am trying to parse a CSV file.  I have downloaded the CSV Parser Extension and have boiled down the code to the bare minimum and haven't been able to get it to work.

In fact, I have a very simple test and my results are the same as when I use the GetCSVFile Method.  Here below I am using a very simple example and the result is that I only get the very first column and not the second column. I am certain I am doing something wrong but I haven't been able to locate the documentation for the extension.  I have looked through the forum and found links but they are dead links. 

Can someone help with getting the Description column populated?

Thanks in advance

var params = {

  columnMappings: "Tagname; Description;" /* STRING */,

  hasHeader: "false" /* BOOLEAN */,

  longitudeField: undefined /* NUMBER */,

  dateFormat: undefined /* STRING */,

  latitudeField: undefined /* NUMBER */,

  fieldDelimiter: undefined /* STRING */,

  stringDelimiter: undefined /* STRING */,

  content: '"XV101,Valve Input"' /* STRING */,

  dataShape: "PES_Tags" /* DATASHAPENAME */

};

// result: INFOTABLE

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

1 ACCEPTED SOLUTION

Accepted Solutions

Few things that may cause it:

  • ColumnMappings
    • Remove spaces between columns ( you have a space after the ";" )
    • Remove the last ";"
  • hasHeader
    • change "true" for true
  • FieldDelimiter
    • ","
  • StringDelimiter
    • "\""
  • content
    • I've never parsed a string content, I parse csv files from file repositories
    • But on your case, you are opening and closing a " on your content, you should not, at least for all the row, you can for every String column
    • When you parse CSV files you need a blank line at the end, maybe it's the case with content, then you will need to add at the end \n
  • dataShape, it should exist.

View solution in original post

29 REPLIES 29

Few things that may cause it:

  • ColumnMappings
    • Remove spaces between columns ( you have a space after the ";" )
    • Remove the last ";"
  • hasHeader
    • change "true" for true
  • FieldDelimiter
    • ","
  • StringDelimiter
    • "\""
  • content
    • I've never parsed a string content, I parse csv files from file repositories
    • But on your case, you are opening and closing a " on your content, you should not, at least for all the row, you can for every String column
    • When you parse CSV files you need a blank line at the end, maybe it's the case with content, then you will need to add at the end \n
  • dataShape, it should exist.

Hi.

If i am having a column which data type is date time means, how to parse in thingworx? 

Hi,

 

I am new to thingworx, can you help me on CSV Parser plugin, I dont know how to use this and how should i implement it.

 

Regards
Vikas

Hi,

I used below script to parse CSV Data.

 

var params = {
path: "/"+ file /* STRING */,
columnMappings: undefined /* STRING */,
hasHeader: true /* BOOLEAN */,
longitudeField: undefined /* NUMBER */,
dateFormat: undefined /* STRING */,
fileRepository: "SystemRepository" /* THINGNAME */,
latitudeField: undefined /* NUMBER */,
fieldDelimiter: undefined /* STRING */,
stringDelimiter: undefined /* STRING */,
dataShape: "parseCSV" /* DATASHAPENAME */
};
var result = Resources["Parsley"].ParseCSV(params);   //result: INFOTABLE

file - CSV file name(eg. "data.csv")
parseCSV - Datashape Name which have all field names with "STRING" basetype. 

 

Thanks & Regards,

Sathishkumar C.

Hi Satish,

 

I understood the script, I dont know the way from where i should start and upload csv file to run this script?

can you plz tell me?

Use Upload widget with Mashup.

Thanks Satish for prompt reply.

 

so, i have to follow following steps?

 

1) create datashapre

2) upload widget

3) create service with provided code

 

If Am i right? there is otherway to import csv directly without creating Mashup.

 

Regards

Vikas

Yeah. looks good.

 

Or you can put your CSV file in "C:\ThingworxStorage\repository\SystemRepository" path and use file name to run a script.

Hi Satish,

 

I am working on IIOT project with CNC Machine, where i am getting contineous data, so is there any way to calculate data run time or on spacific time interval? So that i can calculate OEE, Temperatures after every hour, Performance, Operating time after intervals.

 

I am not getting more useful informations on this. i have machine data in the form of CSV file to test above calculations, can you provide me such training references or any useful information to make calculations or javascript calculations logics

 

Best Regards

Vikas

How your data is communicating with Thingworx? 

for now, i have collected data in CSV and want to import on thingworx to make data calculations (but i dont know how calculations should work using Query or Javascript). i will bind this data to created mashup.

 

Is this make sense for you?

 

Regards

Vikas

Hi Satish,

 

I have created datashape, csv file with exact column name match with datashape, now i am got confused about script! where should i write script for csv upload?

 

Can you help me on this plz , I have attached csv file i created.

 

Regards

vikas

Need not to create any script to upload a 'CSV' file.

Use "file upload widget" to upload a file.

After uploading, create a thing with new service (here only you have to write a script).

keep output as "INFOTABLE".

Yes, I created new Thing and service in it with name "getCSVData"

 

Here is my all code screenshot! is that right?

 

 

 

Regards

Vikas

put file name in double quotations (eg. "filename.csv").

i wrote above code and when i clicked on test service, it gives me attached error, I am using thingworx trial edition on my system.

plz help me on this.

After putting file name in double quote, error as follows:

 

Error executing service

Error executing service getCSVData. Message :: TypeError: Cannot call method "ParseCSV" of null - See Script Error Log for more details.

My code is:

 

var params = {
path: "/"+ "actual_machine_data.csv" /* STRING */,
columnMappings: "id;timestamp;tag_name;value;tag_time;tag_value" /* STRING */,
hasHeader: true /* BOOLEAN */,
longitudeField: undefined /* NUMBER */,
dateFormat: undefined /* STRING */,
fileRepository: "SystemRepository" /* THINGNAME */,
latitudeField: undefined /* NUMBER */,
fieldDelimiter: undefined /* STRING */,
stringDelimiter: undefined /* STRING */,
dataShape: "machineData" /* DATASHAPENAME */
};

result = Resources["CSVParserFunctions"].ParseCSVFile(params); //result: INFOTABLE

 

i got following error:

 

Error executing service

Error executing service getCSVData. Message :: Wrapped java.lang.NullPointerException - See Script Error Log for more details.

 

i am using thingworx 8.1 version, plz help me out....

i think column mapping can put as undefined.

I have used both way undefined and all column names too! is there any tutorials on this plz?

Have you imported CSV Parser?

No, I am still researching, Can you help me on this plz?

Yes, Thank Satish. Finally i have done it and imported successfully!

 

Regards

Vikas

Cool.

 Hi,

 

I have csv file imported, In csv file i have 5 columns like (timestamp, tagName, condition, timestamp_value, value) so

 

1524046971797.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524046975530.00 0
1524046972789.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524046976535.00 0
1524046973789.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524046977529.00 0
1524046974789.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524046978333.00 0
1524046974804.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524046979350.00 0
1524046975789.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524046980517.00 3
1524046976790.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524046981469.00 3
1524046976804.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524046982449.00 3
1524046977789.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524046983569.00 3
1524046978788.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524046984556.00 3
1524046979790.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524046985722.00 3
1524046980789.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524046986655.00

3

 

1524047070162.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524047093717.00 0
1524047071162.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524047094549.00 0
1524047072162.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524047095863.00 0
1524047073162.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524047096665.00 0
1524047074162.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524047097686.00 0
1524047075162.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524047098625.00 0
1524047091163.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524047116512.00 3
1524047092162.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524047117518.00 3
1524047093163.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524047118781.00 3
1524047094162.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524047119747.00 3
1524047095162.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524047120609.00 3
1524047096162.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524047121857.00 3
1524047097163.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524047122868.00 3
1524047098163.00 Channel2.ARK CNC Machine.General Status Information.Auto Operate TRUE 1524047123886.00 3

 

few rows are coming with value 0 and then 3 and again 0 respectively....

 

I want to calculate summasation of timestamp with intervals which has value 3

 

is there any way to calculate such values or can you give me any idea so that i can try?

 

Regards

Vikas

Filter with value 3. and iterate the infotable or do aggregation based on requirements.

Hi Satish,

 

Can you help to do "Filter with value 3. and iterate the infotable or do aggregation"?

 

I am really new to Thingworx, can you plz?

 

Regards

Vikas

AnnaAn
13-Aquamarine
(To:jeffm)

Hi Jeff,

Here is an example per the test data you provided and it works on my Thingworx 7.0.0-b479:

var params = {

    columnMappings: "Tagname;Description" /* STRING */,

    hasHeader: "false" /* BOOLEAN */,

    longitudeField: undefined /* NUMBER */,

    dateFormat: undefined /* STRING */,

    latitudeField: undefined /* NUMBER */,

    fieldDelimiter: "," /* STRING */,

    stringDelimiter: "\"" /* STRING */,

    content: "XV101,Valve Input\n" /* STRING */,

    dataShape: "PES_Tags" /* DATASHAPENAME */

};

// result: INFOTABLE

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

Hope this helps,

Thanks,

Br,

Anna

jeffm
1-Newbie
(To:jeffm)

Thanks both of you!!!! My issue was the space between the columnMappings... I appreciate it.

Top Tags