Files with Spaces in the Header can't be read in Thingworx

Question

Hi,
 
The files that are getting uploaded from the PC to the Thingworx have Spaces in the headers. The challenge here is Headers with spaces can't be read in Thingworx because DataShape need to be included for the Column mapping and in DataShape Fields with Space characters can't be added.If in Headers spaces are removed, the operators in the Shoop floor are getting confused...is there a way to read the CSV files with Spaces in the headers?Thanks,

pshashipreetham · Accepted Answer

Hello,

So I found a solution to this:

Download Custom extension for parsing CSV file from: parsley/parsley_no_poi.zip at master · jmccuen/parsley · GitHub
Import the Extenstion in to the thingworx
User below code to convert the CSV data to InfoTable

let result = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
 infoTableName: "InfoTable",
 dataShapeName: "Datashape"
});

FileName = "Type1.csv";

let params = {
	path: "/CSV/"+FileName /* STRING {"defaultValue":"/"} */,
	hasHeader: false /* BOOLEAN {"defaultValue":false} */,
	fileRepository: "Repository" /* THINGNAME */,
	fieldDelimiter: ";" /* STRING {"defaultValue":","} */,
};

let data = Resources["Parsley"].ParseCSV(params);

for(let x = 1; x < data.length; x++)
{
 let row = data.rows[1].Value1;
	rowSplit= row.split(",");
 if(!rowSplit[0]) rowSplit[0] = "-";
 if(!rowSplit[1]) rowSplit[1] = "-";
 if(!rowSplit[2]) rowSplit[2] = "-";
 if(!rowSplit[3]) rowSplit[3] = "-";
 if(!rowSplit[4]) rowSplit[4] = "-";
 
 let newEntry = {
 	field1: rowSplit[0], // STRING
 	field2: rowSplit[1], // STRING
 	field3: rowSplit[2], // STRING
 	field4: rowSplit[3], // STRING
 	field5: rowSplit[4] // STRING
 };
 result.AddRow(newEntry);
}

The resulting Output be an InfoTable

Thanks,

nmutter · Answer

I understand the problem but did not have this issue yet.

You have a csv like

my first column;my second column
data1;data2

and if you want to read this into an Infotable (using csv parser), the Datashape would need fields 'my first column' and 'my second column'. But as these contain spaces it is not a valid field name.

Maybe you can use the 'columnMappings' parameter if your (csv columns are static)? It unfortunately is not documented well (or at all)..

I assume it may work by specifying hasHeader :false and providing your custom header like:

hasHeader: false,
columnMappings: "firstColumn;secondColumn"

and it would map the first column in csv to dataShape field "firstColumn".

If this works you have to remove the first row of the infotable, as it will contain the csv-header of the csv

Sign up

Please use your PTC eSupport account.

Welcome to the PTC Community

Please use your PTC eSupport account.

Scanning file for viruses.

This file cannot be downloaded