Is there an option to write a script against piarchive and output the results to a csv file from the command line.
Appreciate your advise.
I just noticed we can also use PiCOnfig. Below is a sample script.
piconfig -node PiServerName -port 5450 -username UserNameHere -password PasswordHere < c:\test\PiExtract.txt
Contents of c:\test\PiExtract.txt
You can use PI SQL Commander which is an application that provides a relational view of your PI System by using SQL queries that are exposed by PI OLEDB.
You could then have the query results saved to a csv file (which is one of the options).
Thanks for the feedback. However, the reason why I want to use the command line is to enable me automate the process of running the scripts and sending the output to file. To the best of my knowledge PI SQL Commander does not give a DOS command line option. Your help will be appreciated,
There is nothing out of the box that does this. However, you can use our providers in a programmatic way through other Microsoft technologies like ADO.NET to automate this. Similarly, the same can be said for our SQL client drivers like PI JDBC and PI ODBC.
Could you provide more details on what you are trying to accomplish? There might be other tools we could suggest that may produce the required results.
There is an option to use ipisql.exe which executes SQL statements directed at the PI Data Archive server.The utility uses PI API to connect to PI Data Archive.
However I would not recommend this and would use it as the very last option since it relies on the PI SQL Subsystem, which is deprecated. Using it may result in poor performance.
I have trying to pull data from piarchive.picomp at regular intervals and save to a CSV file on an FTP server so our partners can pick the file. I tried using MyPISQL however I noticed it does not have an option to output to a file. See example below:
MyPISQL -s XXX -q "SELECT * FROM piarchive.picomp WHERE tag='XXX and time between '*-1month' and '*'" -u XX -p XX
I put this in a .bat file so that I can use windows scheduler to set the frequency to run.
Have your tried using the command line utility from Patrice Thivierge?
Here is the link:
GitHub - pthivierge/data-reader-cmdline: A command line application to read high volumes of data from the PI Data Archiv…
I will check it out and revert please.
The link look promising, however when I downloaded the attachment I could not find the 'Datareader.exe' that was illustrated to be used for extraction. Kindly advise.
Source code with the build files ('Datareader.exe') are available for download @
Microsoft OneDrive - Access files anywhere. Create docs with free Office Online.
It may work on your machine if you have the latest version of PI System Explorer and .NET Framework 4.5+ installed.
Retrieving data ...