Skip to main content
H
herveDelhalle1-Visitor
1-Visitor
September 28, 2020
Solved

Service to read excel file, manipulate data and write to SQL database

  • September 28, 2020
  • 3 replies
  • 3191 views

Hello, 

 

I would like to read excel file content in order to manipulate data and then write modified data to SQL Server. 

Unfortunately, I am not able to read excel file using javascript in Thingworx.

I have tried to find answers in the community but the answers I rode do not help me so much ... 

Could you please help me to do it ? 

 

Best regards 

 

Hervé 

Best answer by nmilleson

@herveDelhalle ,

 

I don't believe ThingWorx has any resources to parse Excel files.  They can be a bit complicated since they don't only store data.  They can have multiple sheets, formulas, macros, etc. associated with them.  That said, however, there are solutions for bulk conversion that can take your excel data and do a save-as as CSV.  

3 replies

N
nmilleson17-Peridot
17-Peridot
September 28, 2020

If you save the Excel file as a CSV, you can parse it into ThingWorx using the CSV parser extension.  Look for it here:

 

https://support.ptc.com/appserver/auth/it/esd/product.jsp?prodFamily=TWX

 

You can parse it right into a data-shape and do whatever manipulation/inserts you'd like

H
herveDelhalle1-VisitorAuthor
1-Visitor
September 29, 2020

Dear nmileson, 

 

Thank you for your reply. Is there a way to convert excel file to csv file using javascript services ? 

 

In industrial, excel files are a very common data format. Consequently, I consider that Thingworx should be able to process this data source whithout modifying the source format. 

 

Best regards 

 

Hervé 

N
nmilleson17-PeridotAnswer
17-Peridot
September 29, 2020

@herveDelhalle ,

 

I don't believe ThingWorx has any resources to parse Excel files.  They can be a bit complicated since they don't only store data.  They can have multiple sheets, formulas, macros, etc. associated with them.  That said, however, there are solutions for bulk conversion that can take your excel data and do a save-as as CSV.  

A
abhiramk1-Visitor
1-Visitor
September 30, 2020

@herveDelhalle  Whatever milleson  mentioned above is correct, you can achieve it using csv functions. As there is no way to parse or convert excel files in Thingworx. You have to open and save excel files in csv format and import it to Thingworx.  Use read/parse csv file service which will give output in infotable format, then insert those entries into respective database.

 

 

S
slangleyCommunity Manager
Community Manager
October 13, 2020

Hi @herveDelhalle.

 

If one of the previous posts allowed you to find a solution, please mark the appropriate one as the Accepted Solution for the benefit of others with the same question.

 

Thank you for your participation in our community.

 

Regards.

 

--Sharon

Terms & ConditionsCookie settingsAccessibility statement