cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Learn all about the Community Ranking System, a fun gamification element of the PTC Community. X

Import Data from Excel sheet to Thingworx DATATABLE

Jamal8548
10-Marble

Import Data from Excel sheet to Thingworx DATATABLE

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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

View solution in original post

8 REPLIES 8

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

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

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

Hello @Jamal8548 

 

Pls follow the article for the same:

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

 

Regards

Bhawna

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
17-Peridot
(To:Jamal8548)

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
5-Regular Member
(To:Jamal8548)

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:

ptc6295935_4-1708633641692.png

And gives you the opportunity to convert the xlsx files to Datatables afterwards. 

ptc6295935_5-1708633717730.png

 

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

ptc6295935_2-1708633121054.png

 

Step 2: extract the "FRM-Entites.twx.zip"

Step 3: Import the "FRM-Entities.twx" as Entities (From File->

ptc6295935_0-1708633078394.png

ptc6295935_1-1708633098535.png

Step 4: Find the FRM.ExcelImporter_MU Mashup and open it. 

ptc6295935_3-1708633391370.png

 

 

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

Top Tags