Import Data from Excel sheet to Thingworx DATATABLE

[Jamal8548]

I have my project running on thingworx and we are using DATATABLES to store the data. Now the requirement is that i have some old data in excel sheet with same fields exactly and i would like to import that data from excel into the DATATABLE. how can i achieve this task? does anyone has an idea about it? Please let me know.

[bchaudhary]

Hello @Jamal8548 

We can NOT import excel data into ThingWorx. Pls check the article for your reference:

https://www.ptc.com/en/support/article/CS294337

Thanks

Bhawna

[Arun_C · Accepted solution]

Hi @Jamal8548 ,

Have you tried with Resources → CSVParserFunction → ReadCSVFile(Services) to extract the comma separated data into infotable. Then import that info with your datatable based on AddorUpdate services. I hope it may helps you. If not please let me know the details.

Thanks & Regards,

Arun C

[Jamal8548]

Do you have some example about it. how can i use it. I want to ReadCSVFile but i have some confusions.

[bchaudhary]

Hello @Jamal8548 

Pls follow the article for the same:

https://www.ptc.com/en/support/article/CS240648

Regards

Bhawna

[Jamal8548]

Thank you everyone for their inputs. I have used CSVParserFunction for this purpose and have done with my task. Keep helping others and together we will make a PTC community strong and helpful.

[Rocko]

You can use this Building Block: https://github.com/ptcfield-share-blocks/PTCSC.DataImport

Or, if that is too complex, import just the Excel Extension that is packaged with it. It comes with an ExcelExtension_TT thing template which has a service getSheetValuesAsInfotable.

Upload the Excel to a TWX file repo, read it with the service into an infotable, transfer the infotable to your DataTable.

Note this is not fast as you can't do bulk insert. Each row will be inserted indivually. You might want to split the rows into smaller batches so you don't run into a timeout.

Note also this will not preserve "types" or column formatting you have done in Excel. Especially look out for date columns.

[lschier]

Hi Jamal,

I have created a little Framework exactly for this purpose. It might be helpful to you. Before I proceed explaining it please be aware: This is not official PTC software. We 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. 

[Jamal8548]

Thank you for your insights. I have used CSVParserFunction for my task and its done. But still thanks again for your help.