16-Pearl

Solved

Import Data from Excel sheet to Thingworx DATATABLE

8 replies
2638 views
Translate All

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.

A

Arun_CAnswer

16-Pearl

Hi @MA8731174 ,

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

B

bchaudhary_448008

5-Regular Member

Hello @MA8731174

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

Translate

A

Arun_C

Answer

16-Pearl

Hi @MA8731174 ,

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

Translate

Jamal Ashraf

Author

16-Pearl

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

Translate

B

bchaudhary_448008

5-Regular Member

Hello @MA8731174

Pls follow the article for the same:

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

Regards

Bhawna

Translate

Rocko

19-Tanzanite

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.

Translate

L

lschier

5-Regular Member

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: