Solved: CSV Parser Help

jeffm · ‎Mar 22, 2016

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);

CarlesColl · ‎Mar 22, 2016

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

CarlesColl · ‎Mar 22, 2016

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.

Sathishkumar_C · ‎Mar 21, 2018

Hi.

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

vsquaresystem · ‎Apr 27, 2018

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

Sathishkumar_C · ‎Apr 27, 2018

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.

vsquaresystem · ‎Apr 27, 2018

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?

Sathishkumar_C · ‎Apr 27, 2018

Use Upload widget with Mashup.

vsquaresystem · ‎Apr 27, 2018

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

Sathishkumar_C · ‎Apr 27, 2018

Yeah. looks good.

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

vsquaresystem · ‎Apr 27, 2018

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

Sathishkumar_C · ‎Apr 27, 2018

How your data is communicating with Thingworx?

vsquaresystem · ‎Apr 27, 2018

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

vsquaresystem · ‎Apr 27, 2018

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

Sathishkumar_C · ‎Apr 27, 2018

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".

vsquaresystem · ‎Apr 27, 2018

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

Here is my all code screenshot! is that right?

Regards

Vikas

Sathishkumar_C · ‎Apr 27, 2018

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

vsquaresystem · ‎Apr 27, 2018

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.

vsquaresystem · ‎Apr 27, 2018

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.

vsquaresystem · ‎Apr 27, 2018

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....

Sathishkumar_C · ‎Apr 27, 2018

i think column mapping can put as undefined.

vsquaresystem · ‎Apr 27, 2018

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

Sathishkumar_C · ‎Apr 27, 2018

Have you imported CSV Parser?

vsquaresystem · ‎Apr 27, 2018

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

vsquaresystem · ‎Apr 27, 2018

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

Regards

Vikas

Sathishkumar_C · ‎Apr 27, 2018

Cool.

vsquaresystem · ‎Apr 28, 2018

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
1524047071162.00	Channel2.ARK CNC Machine.General Status Information.Auto Operate	TRUE	1524047094549.00
1524047072162.00	Channel2.ARK CNC Machine.General Status Information.Auto Operate	TRUE	1524047095863.00
1524047073162.00	Channel2.ARK CNC Machine.General Status Information.Auto Operate	TRUE	1524047096665.00
1524047074162.00	Channel2.ARK CNC Machine.General Status Information.Auto Operate	TRUE	1524047097686.00
1524047075162.00	Channel2.ARK CNC Machine.General Status Information.Auto Operate	TRUE	1524047098625.00

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

Sathishkumar_C · ‎Apr 28, 2018

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

vsquaresystem · ‎Apr 30, 2018

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 · ‎Mar 23, 2016

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 · ‎Mar 25, 2016

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