more quick actions
minimize quick actions
Topic starter
Since PcVue 12, Universal Data Connector is able to read/write/update data with a free OLEDB provider (not CDATA provider).
> Microsoft.ACE.OLEDB.12.0
This provider allow you to :
- read a XLSX file like a database with SELECT queries
- write a XLSX file (append mode only) with INSERT queries
- update a XLSX file with UPDATE queries.
DOWNLOAD
If necessary you can download it here (take x86 version) : https://www.microsoft.com/fr-fr/download/details.aspx?id=13255
CONFIGURE PCVUE
Create a new SqlConnection with following parameters.
General tab
- Data source => Other
- Data provider => .Net Framework Data Provider for OleDb
Other tab
-Connection string => provider=Microsoft.ACE.OLEDB.12.0;data source=E:status.xlsx;extended properties="Excel 12.0 Xml;HDR=YES"
Apply this configuration and test the connection
You should have the message "Connection successfuly established"
QUERY SAMPLES
In following samples I use a file named "status.xlsx" with one sheet named "feuil1"
Chrono | Value 04/12/2018 13:10:00 | 47 04/12/2018 13:11:00 | 10.1 04/12/2018 13:12:00 | 9.14 04/12/2018 13:13:00 | 50.15
Ex: to select all the fields where the value is bigger than 10 and sort the result by Value
SELECT * FROM [Feuil1$] where Value > 10 order by Value DESC
Ex: to insert a new line in the sheet
INSERT INTO [Feuil1$] VALUES('04/12/2018 15:52:00',336.47)
Ex: to update a cell in the sheet
UPDATE [Feuil1$] SET [Value] = 400.00 WHERE [Value] = 336.47
Enjoy !!!!
Posted : 05/12/2018 5:08 pm
Topic starter
It is now preferable to use the ADO.NET provider offered by Arc Informatique.
Posted : 02/05/2022 7:02 pm