neilg

Exporting a large amount of data out of PI using VBA and retaining milliseconds

Discussion created by neilg on Jun 19, 2014
Latest reply on Jun 22, 2014 by neilg

Hello people from the world, I have an interesting challenge and wanted to know if anyone has done anything similar before.

 

We need to export out sampled data from PI at 300 milliseconds interval for approximately 2 years. At this resolution there is a large amount of data to be exported.

 

I am using VBA and PI SDK to get the sampled data via the point.data.timedvalues method. I have improved performance considerably by using the IPIValues2 interface and calling GetValuesArray method on it instead of using the PIValues collection.

 

With this call I have an array of UTC second timestamps sampled at 300 milliseconds. We need to export out the millisecond component of the time as well. There is a limitation in the underlying VBA date object which does not support milliseconds.

In order to export out the milliseconds I am using "Application.Worksheet.Text" excel function applied to an Excel Range object to get the proper formatted date with milliseconds for exporting out to a file.

 

The problem with this approach is that it is extremely slow. One months data export takes roughly an hour to complete. For two years worth of data we will be waiting a long while.

 

Does any one know of a better approach?

 

I know it may be better to go down the .Net route as the Date object does support milliseconds but if someone has a better approach in VBA it will be appreciated.

 

 

Outcomes