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

[pshashipreetham]

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,

[jensc]

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

[pshashipreetham · Accepted solution]

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,

[nmutter]

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

[pshashipreetham]

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,

[nmutter]

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!

[slangley]

Hi @pshashipreetham.

How often are you running this process?  Is this something that you need to run periodically or a one-time load?  Why is 20 seconds an issue for 1,000,000 rows?

Maybe if you can provide more detail of your use case, we can provide another solution.

Regards.

--Sharon

[pshashipreetham]

Hi @slangley ,

I have a CSV file with headers with spaces in it, I have been struggling to upload a CSV file with spaces in the header, and Thingworx has limitations on it.

I raised a Support ticket, but as per the Support person, this is a Limitation from thingworx that it Can't upload a CSV file with headers having spaces in it.

Thanks

[slangley]

Hi @pshashipreetham.

Is this a one-time or infrequent load?  If not, you might want to consider other options for loading that much data on a regular basis.  If you can provide more details regarding where the data is coming from and how the CSV file is being generated, we may be able to offer a more efficient solution.

Regards.

--Sharon

[pshashipreetham]

Hi @slangley ,

The Files I am receiving from different ERP Systems like SAP, PADS, Windchill, and more... This is a frequent upload, and the operator may upload once or thrice a day.

What is the other option we can consider loading the data regularly?

Thanks,

[nmutter]

Do you need to parse the data from the CSV? Do you do anything with the 1million rows? Just asking if you even need to parse it (maybe just need to offer to download the original .csv again to the user).

I suggest to also open a new topic for the performance issue as the initial question is resolved I assume (with the columnMappings option). Or does a csv file without spaces in headers with this many rows load faster?

Would help for traceability if someone has the same issue.

[slangley]

Hi @pshashipreetham.

ThingWorx has tools for integrating with SAP, Windchill, etc. more efficiently than importing CSV's.  It sounds like the current process is user-driven and with that many records being imported multiple times a day, there is certainly the concern for performance problems.

Check out these resources for alternatives that should be more efficient:

Using ODataConnector or SAPODataConnector

ThingWorx Navigate

Please let us know if you have further questions.

Regards.

--Sharon

[pshashipreetham]

Hi @slangley ,

The End solution for this post would be the code or way to upload the CSV file where in headers there are spaces.

Still this is a blocker for me to close a POC.

Thanks,