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

[VV_9240359]

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 

[nmutter]

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.

[MA8731174 · Accepted solution]

Hi ,

A community member https://community.ptc.com/t5/user/viewprofilepage/user-id/144760

 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!

Lukas

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

[VV_9240359]

Thank you so much for in detail explanation and video tutorial really appreciate the hardwork