Uploading CSV with all fields in separate columns which will then store data in Grid

Question

I have a service which parses the CSV data which I am uploading and creates Thingworx entity (Thing) from it. Currently the issue I am facing I have to enter all the data in a single cell before uploading using ";"(semicolon) separater

Is there any way I can upload a CSV which will have data in different columns

If I enter data in different columns (,) comma is getting appended automatically

I did some research got below information :
ThingWorx currently uses CSVParserFunctions.ReadCSVFile(), which only supports .csv, not .xlsx.
Excel’s “CSV” exports vary by locale (using , or ;), so data works only with ;.
Users must fill all values in one cell, separated by ;, before uploading.
This process is confusing and not user-friendly.

as mentioned above it is not user friendly to enter all data in a single cell with ; semicolon separated

MA8731174 · Accepted Answer

Hi ,A community member @lschier has created a little Framework. It might be helpful to you. Before I proceed explaining it please be aware: This is not official PTC software and do not offer any support if you use this approach. If you face issues I am glad to try and help you but it comes without any testing, warranty or security checks. It might work unexpectedly.I did not find the time yet to write a documentation so I created a little video walkthrough for you. You can find it here: https://youtu.be/RlMXVaWE-PY I recommend to watch the video but as a very brief abstract, the framework allows you to first browse your repository and upload files: And gives you the opportunity to convert the xlsx files to Datatables afterwards.   The entry point for this framework is the "FRM.ExcelImporter_MU" Mashup.Have fun!LukasIn order to use this framework you'll first have to import the excel extension and afterwards the framework entities. Step 1: import the "excel extension.zip" as extension to ThingWorx  Step 2: extract the "FRM-Entites.twx.zip"Step 3: Import the "FRM-Entities.twx" as Entities (From File->  Step 4: Find the FRM.ExcelImporter_MU Mashup and open it.

nmutter · Answer

There is no 'great' solution due to excel behavior deciding on users locale.

We solved it by using the fieldDelimiter parameter of the CSVParser:

1. First try parse .csv with fieldDelimiter: ","

2. If parsing fails we try again with fieldDelimiter: ";"

3. If fails - show issue to user

I'm not 100% sure if it fails with wrong delimiter. Maybe after parsing you can validate result and see if it makes sense (e.g. 2nd field is emtpy which should have a value). If it does not make sense try different delimiter.

This way users can use normal CSV and have no trouble.

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