Skip to main content
pshashipreetham
pshashipreetham19-Tanzanite
19-Tanzanite
Mar 30, 2023 3:30 PM UTC
Solved

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

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
pshashipreethamAnswer
19-Tanzanite
May 11, 2023 6:03 PM UTC

Hello, 

So I found a solution to this:

 

  1. Download Custom extension for parsing CSV file from: parsley/parsley_no_poi.zip at master · jmccuen/parsley · GitHub
  2. Import the Extenstion in to the thingworx
  3. 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,

Shashi Preetham || +91 8099838801 || shashi@psptechhub.com
View in thread

12 replies

jensc17-Peridot
17-Peridot
Mar 31, 2023 6:02 AM UTC

Hello,

 

I'm confused by what the operators are getting confused by...

How are you showing this data to them? In a grid?

 

If this is the case, then you can just change the column headers in the grid configuration to include the spaces.

 

Or do they first upload something and then they export it to CSV?

 

Regards,

Jens

Translate
pshashipreetham
pshashipreetham19-TanzaniteAuthorAnswer
19-Tanzanite
May 11, 2023 6:03 PM UTC

Hello, 

So I found a solution to this:

 

  1. Download Custom extension for parsing CSV file from: parsley/parsley_no_poi.zip at master · jmccuen/parsley · GitHub
  2. Import the Extenstion in to the thingworx
  3. 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,

Translate
Shashi Preetham || +91 8099838801 || shashi@psptechhub.com
nmutter17-Peridot
17-Peridot
Mar 31, 2023 5:01 PM UTC

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

Translate
    pshashipreetham
    pshashipreetham19-TanzaniteAuthor
    19-Tanzanite
    Apr 14, 2023 5:00 AM UTC

    Hi @nmutter ,

    This is a workaround, It's working in this way, but ended up services running more 20 secs (Since the CSV uploaded has 1,000,000 rows of data), I am still trouble shooting this issue for a optimized way.

     

    Thanks,

    Translate
    Shashi Preetham || +91 8099838801 || shashi@psptechhub.com
    nmutter17-Peridot
    17-Peridot
    Apr 14, 2023 3:37 PM UTC

    Happy to hear.

    I guess the performance won't go up with a different mapping option as the 'issue' will be the size of the file. But good luck, let us know if you find something!

    Translate
    Terms & ConditionsAccessibility statement