16-Pearl

Solved

Import Data from Excel sheet to Thingworx DATATABLE

Forum|Forum|2 years ago
February 22, 2024
4 replies
2528 views

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.

Best answer by Arun_C

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

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

M

MA8731174

Author

16-Pearl

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

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

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.

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: