14 Replies Latest reply on Aug 30, 2018 2:47 AM by LindseyHatfield

    What is the best way to get all Tag Values out to a csv file for a date range

    LindseyHatfield

      We are extracting data from our PI Archive for a month (when there was a major event) to use as a feed into a simulator.

      We don't need all points but as we need 77,000 points of the 80,000 we have we may as well get  them all for that data source.

      I am a newbie programmer although have been working with PI for 20+ years doing Support and administration. I have started this by using a program that extracts the data for a set of points for a entered time range and it uses

      We actually don't even require the TAG name for the output but do need the Extended Description which we use to link data system values

      I can do this point by point using the call

       

      targetMemberPoint As PISDK.PIPoint = GetPoint(iTargetServer, pointName)

      values = sdkPoint.Data.RecordedValues(startTime, endTime, BoundaryTypeConstants.btInside, "", FilteredViewConstants.fvShowFilteredState, Nothing)

       

      ptAtt = sdkPoint.PointAttributes("exdesc")

                      then some formatting stuff.

      If value.IsGood Then

          sb.AppendLine(ptAtt.Value.ToString & "," & value.TimeStamp.UTCSeconds.ToString & "," & sValue.ToString)

      End If

      Then write to the file, which works fine point by point, but how do you get all points as a stream of records in time order not point order ?

       

      The output is currently like

      ExDescTime (UTCSeconds)Value
      AR_1.F7ST1514764800Closed
      AR_1.F7ST1514851200

      Closed

      AR_1.F7ST1514937600Closed
      AR_1.F7ST1515024000Closed

       

      Can I still use the _Data.RecordedValues call without a point defined ?

        • Re: What is the best way to get all Tag Values out to a csv file for a date range
          Lal_Babu_Shaik

          Hi Lindsey

           

          I would suggest to use PI config or PI Powershell to extract large amount of data from PI system. Also request you to check Rick Davin's blog on GetLargeRecordedValues - working around ArcMaxCollect

           

          Thanks

          Lal

          • Re: What is the best way to get all Tag Values out to a csv file for a date range
            tramachandran

            Getting data for 77,000 PI tags is not a straight forward operation and you might be required to sub-divide them into batches for data extraction.  Do you already have a strategy in place for partitioning them? If so, could you let us know what it is?

            Before trying a programmatic solution, you could explore out of the box solutions like PI Datalink or PI OLEDB to get the data into csv format. PI Poweshell is another option that has been suggested which might be easy to incorporate into a script.

             

            I noticed that you are using PI SDK in your code snippet. Please note that we are Deprecating the PI SDK  and for newer projects the recommended developer technology to replace it is PI AF SDK.  You can give the code in GetLargeRecordedValues or the utility PI DataPipe Events Subscription and Data Access Utility using AF SDK - PIEventsNovo a try with very minor modification to include the extended descriptor. These are mainly to extract large number of events rather than getting data from a large number of tags.

             

            However, your issue seems like lot of tags than lot of data, so I would suggest checking out the above mentioned options and respond with your thoughts on them. Maybe there is an easier way to accomplish what you are looking for.

              • Re: What is the best way to get all Tag Values out to a csv file for a date range
                LindseyHatfield

                I am now trying to use the AF SDK for this and using C# as the language.

                I will extract the data into day files , so I have a list of points based on the exdesc and point source, 78,625 points. The dates they are interested in are between Jan-1 and Apr-1 this year.

                I was hoping to go to the start date, read record after record and if exdesc exists, write the value to the cvs file.

                I was also considering OLEDB as an option, datalink is no good as it will not handle the volume of data.

                 

                I will read the information suggested by Lal Babu Shaik as well, thank you.

                PI config    hmmm.

                 

                Thanks for your thoughts

                  • Re: What is the best way to get all Tag Values out to a csv file for a date range
                    Dan Fishman

                    I wrote this in VB.Net since it appears you might have some experience with VB.Net.  You can easily convert the code using one of the online VB.Net to C# code converters.  This is a great project to learn .NET, AF SDK, and work with PI.

                     

                    Below, when I perform a search for the PI Points, I also retrieve the extendedDescriptor to avoid retrieving it again. This code uses a bulk query to request 100 tags values in one chunk to reduce the number of network calls. Once I have the results, I arrange all of them by timestamp.  I am sure the code I quickly wrote can be improved and optimized even more.

                     

                    You did ask for the best way, and there are a lot of considerations such as which tools do you have, how large are your queries, what skills do you have, how much time it takes to support and more!  If you want learn more programming, then the AF SDK is a great way.

                     

                    In VB you will need these two lines added once you add the AFSDK.dll.  In C# it will be "using XXX;"

                    Imports OSIsoft.AF.Asset

                    Imports OSIsoft.AF.PI

                     

                     

                            Dim servers As New PIServers()
                            Dim server As PIServer = servers("serverName")
                    
                            ' search can be tuned, but just an example.  Loads the extended descriptor to cache it upfront
                            Dim points As IEnumerable(Of PIPoint) = PIPoint.FindPIPoints(server, {New PIPointQuery(PICommonPointAttributes.Tag, OSIsoft.AF.Search.AFSearchOperator.Equal, "*")}, {PICommonPointAttributes.ExtendedDescriptor})
                            Dim pointList As New PIPointList(points)
                            ' can tune paging config for performance as required
                            Dim pagingConfig As PIPagingConfiguration = New PIPagingConfiguration(PIPageType.TagCount, 100)
                            Dim resultList As New List(Of AFValues)
                    
                            Dim values As IEnumerable(Of AFValues) = pointList.RecordedValues(New AFTimeRange("*-15D", "*"), OSIsoft.AF.Data.AFBoundaryType.Inside, "", True, pagingConfig)
                    
                            For Each result As AFValues In values
                                result.RemoveAll(Function(r) Not r.IsGood)
                                If result.Count > 0 Then
                                    resultList.Add(result)
                                End If
                            Next
                    
                            ' flatten the AFvalues into AFValue and order by timestamp
                            Dim orderedValues As IEnumerable(Of AFValue) = resultList.SelectMany(Function(r) r).OrderBy(Function(v) v.Timestamp)
                    
                            For Each orderedResult In orderedValues
                                Console.WriteLine($"{orderedResult.PIPoint.GetAttribute(PICommonPointAttributes.ExtendedDescriptor)} , {orderedResult.Timestamp.UtcSeconds} ,{orderedResult.Value.ToString()}")
                            Next
                    

                     

                    Let us know if you have any questions.

                     

                    Regards,

                    Dan

                     

                    Engineer

                    Exele Information Systems, Inc.

                    1 of 1 people found this helpful
                      • Re: What is the best way to get all Tag Values out to a csv file for a date range
                        rschmitz

                        Something worth of note, in this code Dan queries for all 15 days worth of data at once, which may or may not work depending on how fast your data updates and what your system specs are. I've found that these types of queries can time out or exceed the number of events that can be queried from the Archive. So you may want to iterate through in smaller intervals and append all of the data together in a CSV file after the fact.

                         

                        One other consideration, queries this large will almost definitely lock up your archive from other users trying to make requests, so run this during off hours when you know other users won't be needing the archive. Just my $0.02

                         

                        --Rob

                  • Re: What is the best way to get all Tag Values out to a csv file for a date range
                    LindseyHatfield

                    OK, I have finished my little task and thank you for all the pointers and help. I took all the information in and used some of the strategies from Dans code and wrote it as follows.

                     

                    the config file has these app settings

                    Capture.JPG

                    I then brought them into the code

                    Capture2.JPG

                    Built the elements for the PI Point search

                    Capture3.JPG

                    Built the point list

                    Capture4.JPG

                    Set the initial time range

                    Capture5.JPG

                    And then ran through the days and points writing the output to the outfile

                    Capture6.JPG


                    The format is, RAW data

                    43101,AR_1.F12WA,101.4

                     

                    43101.080985544,AR_1.F12WA,93.2

                    43101.0834339352,AR_1.F12WA,95.6

                    43101.0843698958,AR_1.F12WA,99

                    43101.0846039468,AR_1.F12WA,96.4

                    43101.085304375,AR_1.F12WA,94

                    43101.0858879398,AR_1.F12WA,96.6

                    43101.0860981713,AR_1.F12WA,92.2

                    43101.0869173611,AR_1.F12WA,94.4

                    43101.087851875,AR_1.F12WA,96.8


                    In excel

                    UTC  DateTime with ms

                    Exdesc

                    Value

                    01/01/2018 0:00:00.000

                    AR_1.F12WA

                    1. 101.4

                    01/01/2018 1:56:37.151

                    AR_1.F12WA

                    1. 93.2

                    01/01/2018 2:00:08.692

                    AR_1.F12WA

                    1. 95.6

                    01/01/2018 2:01:29.559

                    AR_1.F12WA

                    99

                    01/01/2018 2:01:49.781

                    AR_1.F12WA

                    1. 96.4

                    01/01/2018 2:02:50.298

                    AR_1.F12WA

                    94

                    01/01/2018 2:03:40.718

                    AR_1.F12WA

                    1. 96.6

                    01/01/2018 2:03:58.882

                    AR_1.F12WA

                    1. 92.2

                    01/01/2018 2:05:09.660

                    AR_1.F12WA

                    1. 94.4

                    01/01/2018 2:06:30.402

                    AR_1.F12WA

                    1. 96.8
                    1 of 1 people found this helpful
                      • Re: What is the best way to get all Tag Values out to a csv file for a date range
                        LindseyHatfield

                        Sorry for all the pictures in the above post, I could not work out how to get the code in as text from Visual Studio, of course until after posting the first one.

                         

                        Here is the code in total in case someone needs it in the future or similar.

                         

                        using System;
                        using System.Collections.Generic;
                        using System.Configuration;
                        using System.IO;
                        using OSIsoft.AF.Time;
                        using OSIsoft.AF.Data;
                        using OSIsoft.AF.PI;
                        using OSIsoft.AF.Asset;
                        using OSIsoft.AF.Search;
                        namespace ExamplesLibrary
                        {
                            /// <summary> 
                            /// This example finds PI Points based on the search patterns and extracts the data into daily files for a period of time between dates. 
                            /// </summary> 
                         
                            public class ExportToCSV
                            {
                                static void Main()
                                {
                                    // Setting Variables for execution from the configuration file 
                                    string myPIserver = ConfigurationManager.AppSettings["MyPIServer"];
                                    string exDescPattern = ConfigurationManager.AppSettings["ExdescPattern"];
                                    string srcDescPattern = ConfigurationManager.AppSettings["SourcePattern"];
                                    string nameDescPattern = ConfigurationManager.AppSettings["NamePattern"];
                                    string exportDIR = ConfigurationManager.AppSettings["ExportDIR"];
                                    string beginD = ConfigurationManager.AppSettings["FirstDate"];
                                    string endD = ConfigurationManager.AppSettings["LastDate"];
                                    // At this time the data block Timespan is fixed at 1 day.
                                    AFTimeSpan tSpan = new AFTimeSpan(days: 1);  
                                    AFTime firstDate = new AFTime(beginD);
                                    AFTime lastDate = new AFTime(endD);
                                    // Define the implicit connection to the PI Server to collect data from
                                    PIServers piServers = new PIServers();
                                    PIServer piServer = piServers[myPIserver];
                                    // Use PICommonPointAttributes to define the search criteria for the point search. 
                                    PIPointQuery ExDescFilter = new PIPointQuery
                                    {
                                        AttributeName = PICommonPointAttributes.ExtendedDescriptor,
                                        AttributeValue = exDescPattern,
                                        Operator = AFSearchOperator.Equal
                                    };
                                    PIPointQuery SourceFilter = new PIPointQuery
                                    {
                                        AttributeName = PICommonPointAttributes.PointSource,
                                        AttributeValue = srcDescPattern,
                                        Operator = AFSearchOperator.Equal
                                    };
                                    PIPointQuery nameFilter = new PIPointQuery
                                    {
                                        AttributeName = PICommonPointAttributes.Tag,
                                        AttributeValue = nameDescPattern,
                                        Operator = AFSearchOperator.Equal
                                    };
                                    // Use PICommonPointAttributes to define the point attributes that we will return with the point reference. 
                                    IEnumerable<string> attributesToLoad = new[]
                                    {
                                       PICommonPointAttributes.ExtendedDescriptor,
                                       PICommonPointAttributes.Descriptor,
                                       PICommonPointAttributes.PointSource
                                    };
                                    // Use PIPagingConfiguration to set the size of the search blocks to 100 points at a time, this helps to stop response timeouts. 
                                    PIPagingConfiguration pagingConfig = new PIPagingConfiguration(PIPageType.TagCount, 100);
                                    
                                    // using the PIPointQuery attributes find all the points to be referenced in the value search later.
                                    IEnumerable<PIPoint> pList = PIPoint.FindPIPoints(piServer, new[] { SourceFilter, ExDescFilter, nameFilter }, attributesToLoad);
                                    PIPointList pointList = new PIPointList(pList);
                                    //Initialize the text log file, in the directory for the export, named ExDesc_START-END_dbg.txt
                                    string saveFILE = exportDIR + "ExDesc_" + beginD + " - " + endD + "_dbg.txt";
                                    var outfile = new StreamWriter(saveFILE);
                                    //Write in the basic configuration details to the log file for refereence later.
                                    outfile.WriteLine("Using the search pattern;");
                                    outfile.WriteLine("           Point  Name  : {0}  ", nameDescPattern);
                                    outfile.WriteLine("           Point Source : {0}  ", srcDescPattern);
                                    outfile.WriteLine("           Exdesc       : {0}  ", exDescPattern);
                                    outfile.WriteLine("Number of points found  : {0}", pointList.Count );
                                    outfile.WriteLine("For the Period of Start : {0}  to  End: {1}", beginD, endD);
                                    outfile.WriteLine("------------------------------------------------------------");
                                    //Set the initial AF Time range for the first extract
                                    AFTimeRange tRange = new AFTimeRange();
                                    tRange.StartTime = new AFTime(firstDate);
                                    tRange.EndTime = tSpan.Add(tRange.StartTime);
                                    Console.WriteLine("Start: {0}, End: {1}", tRange.StartTime, tRange.EndTime);
                                    while (tRange.StartTime <= lastDate)
                                    {
                                        Console.WriteLine("Start: {0}, End: {1}", tRange.StartTime.ToString("dd-MM-yyyy"), tRange.EndTime.ToString("dd-MM-yyyy"));
                                        //string valueFILE = "C:\\Temp\\ExDesc\\" + tRange.StartTime.ToString("dd-MM-yyyy") + "_VList.csv";
                                        string valueFILE = exportDIR + tRange.StartTime.ToString("dd-MM-yyyy") + "_VList.csv";
                                        var datafile = new StreamWriter(valueFILE);
                                        // Write a line to the log for the date file change
                                        outfile.WriteLine("Next Date File Start: {0},   End: {1}", tRange.StartTime.ToString("dd-MM-yyyy"), tRange.EndTime.ToString("dd-MM-yyyy"));
                                        // IList<PIPoint> points = PIPoint.FindPIPoints(piServer, new[] { "sinusoid", "sinusoidu", "cdt158", "cdm158" });
                                        foreach (PIPoint pt in pointList)
                                    {
                                        AFValues valueList = pt.RecordedValues(
                                        tRange,
                                        AFBoundaryType.Inside,
                                        "",
                                        true,
                                        0);
                                        foreach (AFValue vl in valueList)
                                        {
                                            if (vl.IsGood == true) datafile.WriteLine("{0},{1},{2}", vl.Timestamp.UtcTime.ToOADate(), pt.GetAttribute(PICommonPointAttributes.ExtendedDescriptor), vl.Value );
                                        }
                                            // Write a line to the Log File for the Point level debugging
                                            outfile.WriteLine("{0},{1}", pt.GetAttribute(PICommonPointAttributes.Tag), pt.GetAttribute(PICommonPointAttributes.ExtendedDescriptor));
                                            
                                        // add line to console
                                        // Console.WriteLine("Point: {0}, ExDesc: {1}", pt.GetAttribute(PICommonPointAttributes.Tag), pt.GetAttribute(PICommonPointAttributes.Descriptor));
                                    }
                                        // flush data from buffers then close the file
                                            datafile.Flush();
                                            datafile.Close();
                                        // Increment the start and end dates by the day span
                                        tRange.StartTime = tSpan.Add(tRange.StartTime);
                                        tRange.EndTime = tSpan.Add(tRange.StartTime);
                                    }
                                outfile.Flush();
                                outfile.Close();
                                }
                            }
                        }
                        
                      • Re: What is the best way to get all Tag Values out to a csv file for a date range
                        felczantom

                        Hello, I wrote my own windows app.

                        I need enter name of the PI Server, destination directory for saving csv files, start time, end time, time step (for splitting big data blocks to a smaller), separator char and input list of the pi tags (here for example only cdt158).
                        Next push check data button for checking domain user rights to pi tags data, validate input information (date, pi server name, ...).

                        if success you can push button start.

                        Application then generate csv files according the pi tags list

                         

                        And this is result:

                         

                        BR Tom