1 Reply Latest reply on Jun 27, 2018 6:50 PM by tramachandran

    Export big amount of data from PI data archive to csv file

    Saken

      Hi,

      looking for the way to export big amount of data from PI data archive to csv file in format: tag,time,value

       

      I found following link https://pisquare.osisoft.com/message/106803-re-pi-sql-query-results-to-file#comment-106803 where piconfig used.

       

      piconfig -node PiServerName -port 5450 -username UserNameHere -password PasswordHere < c:\test\PiExtract.txt

       

      How can I put list of tags and start time and end time to the line above, I need data for 5000 tags for about two year?

       

      thanks,

      S

        • Re: Export big amount of data from PI data archive to csv file
          tramachandran

          First, would it be possible to provide the reason for this export? Are you moving data between machines? Is it to create a data set for analytics?

          There might be other solutions like PI Integrator for Business Analytics that might help.

           

          Conservatively assuming there are a 1000 events/year for each tag, this would give us 1000*2*5000 = 10,000,000 events. This is way more that the limit the PI DA can process for a query under standard conditions.

          You will have to break up your query into reasonable chunks.

          (Refer:  3224OSI8 - How to limit expensive queries to the PI Server archive)

           

          You should also look at the possibility of using other clients like PI Datalink or 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).

           

          For a custom application, check out the blog Extracting large event counts from the PI Data Archive (This has not been tested for 5000 tags)

           

          Having said all this, here is what you can do if you want to use piconfig.

          The post you are referring to contains the following script. You can supply different tag names in each of the lines after output to make it work for multiple tags.

          (NOTE: count in the script has an astronomical value. This is a Int32 parameter, so it cannot exceed  2,147,483,647, and even this number is beyond standard limits)

          @table piarc

          @mode list

          @stype delimited

          @istr tag,starttime,endtime,count

          @ostr ...

          @ostr tag,time,value,

          @output C:\test\Results.csv

          tagename1,*-1h,*,99999999999999

          tagename2,*-1h,*,99999999999999

          tagename3,*-1h,*,99999999999999

          @exit