I want to delete duplicate data from PI archives for 150 odd tags.
Is there any tool or method to delete duplicate data?
Limitations: I cannot use PI SQL commander in my environment.
I cannot create new tags since the existing tags are configured in reports using their point ID's.
Hi Aniruddha,
You can use RemoveValues method in PI SDK to find those duplicate values via iteration and remove them.
Kindly find the PI SDK Programming Reference here
Hope this helps.
Here is another method to delete duplicate values: 3065OSI8 - How to delete data in PI archives How to delete snapshot or archive values .
Since you are deleting values it is recommended you perform a backup prior to the deletion.
In particular, remember to use drRemoveFirstOnly as the behaviour so you won't delete both values! Something like this:
Pipoint.Data.RemoveValues(Value.TimeStamp.UTCSeconds, Value.TimeStamp.UTCSeconds, DataRemovalConstants.drRemoveFirstOnly)
what if i need to delete more than 1 duplicates? I have same value repeated more than 4 times in some case.
You will need to run RemoveValues multiples times in that case. For example, if you have 3 duplicate values (i.e. you have 4 values), you will need to call the function 3 times. This is because without that flag, if you run the function, you will delete all 4 values which is not what you want.
If you don't fear Excel/VBA and know how to read Excel cells from VBA:
1. Use DataLink Compressed Data to dump all events of one tag (you may iterate over all tags later using VBA) on new worksheet, with timestamps
2. Use Excel formula to compare timestamps in rows (n) and (n+1) and write 1 if they are identical, 0 otherwise
3. Use Excel VBA with PI-SDK to iterate row by row:
- if your flag calculated above is 1 then fetch timestamp from worksheet cell and use PIPoint.Data.RemoveValues as described above (same From and To timestamps and RemoveFirstOnly)
4. End of data mark may be empty cell that is expected to contain a timestamp
You may experience problems during October Daylight Saving Time transition (double hour)
We can use UTC time zone in Datalink to avoid that issue.
Thank You for your inputs i will try the excel/vba stuff.
If you don't fear Excel/VBA and know how to read Excel cells from VBA:
1. Use DataLink Compressed Data to dump all events of one tag (you may iterate over all tags later using VBA) on new worksheet, with timestamps
2. Use Excel formula to compare timestamps in rows (n) and (n+1) and write 1 if they are identical, 0 otherwise
3. Use Excel VBA with PI-SDK to iterate row by row:
- if your flag calculated above is 1 then fetch timestamp from worksheet cell and use PIPoint.Data.RemoveValues as described above (same From and To timestamps and RemoveFirstOnly)
4. End of data mark may be empty cell that is expected to contain a timestamp
You may experience problems during October Daylight Saving Time transition (double hour)