How can I export data to CSV file

GPA Products openHistorian
1

Hello, I am new to openHistorian. The Data Trending Tool can export archive.d file to CSV file. Is there any tool to export .d2 to CSV file?

2

The web UI screen called “Trend / Export Data” that comes with the openHistorian 2.0 exports to a CSV file.

3

Hi Ritchie,

Thanks for your reply. Could you please guide me through how I should do the conversion? I have loaded the d2 file by using the load button in the left panel, but nothing showed up. Do I have to set up a device instance and include dbase.dat, dbase.dat2, startup.dat, scratch.dat files?

My data is about 10TB in size and will be feed in to Matlab for analysis, what would be the best way to convert to CSV file?

Thanks,
Cindy

4

Sorry - perhaps I am a little confused. Can you provide a little more context? I presume you already have some source data. The “.d” files are for openHistorian 1.0, the “.d2” files are for openHistorian 2.0 - which do you have?

5

The archive data I have are in .d format. I am thinking to convert the data to CSV files, so I can develop Matlab/ Python codes to read and analyze the data. The data size is around 10 TB. I read in one of the articles that CSV conversion is more efficient with d2 files, so the .d2 files are converted from .d files using “Archive Upgrade Utility”.

6

I see - that helps. So I also presume that you don’t have access to the original configuration database for the source data? If not, the only metadata for the points you have is in the “dbase.dat*” files.

Without the original source configuration and only access to the dbase files for metadata will mean that importing into openHistorian 2.0 (.d2 format) will not be so useful. As a result, you may need to use the openHistorian 1.0 tools to access the data.

If you already have the openHistorian 2.0 installed, click on the start menu and run the “Statistics Trending Tool” - this is basically the trending tool for 1.0 archives (i.e., those files that end in “.d”).

Once the tool is running click File / Open archives.... On this screen go ahead and close the openHistorian statistics archive by clicking the button with the red x that is next to the archive labeled stat_archive.

Next click the Open button at the bottom left of the dialog and browse to the folder with your .D files and associated dbase.dat files - select an existing .D file and then click `Finish’.

If everything goes well you will see the metadata for the data in question and you can now trend and export the data in CSV or COMTRADE formats.

7

Hi Ritchie,

I followed your steps and was able to create CSV file, but the data are all “NaN” in the CSV file.

In my folder, I have the following files:
ppa_archive.d
ppa_dbase.dat
ppa_dbase.dat2
ppa_startup.dat
scratch.dat

Before I clicked Export, I have also defined by UTC start and end time as shown in the photo attached.
https://app.box.com/s/ipeadgpfzbfh72v46ym9thlqf2sizinu
Am I missing any files or steps in the process?

8

You need to make sure there is data for the selected time range. have you successfully trended data for that time range?

9

I believe the time range is correct. I wasn’t able to get trended data as well…

10

Try zooming out until you see data.

11

I am not getting anything. Is there anything I have to enable in the software or specific files I will need in order to get values?

12

Can you show me the listing of the files in your data folder?

13

Hello Ritchie,

Please find the photo in the link below.
https://app.box.com/s/px2lg2u9vlfhc7ma2a6hi5wki46y15fa

I renamed .d files that I received from “ppa_archive_2016-12-31 22!05!59.066_to_2016-12-31 22!19!11.066.d” to “ppa_archive.d” in order for Statistics Trending Tool to read.

For the time range, I believe the time on the file name is the UTC time, so, I set a 3 minute windows: 2016-12-31 22:05:60.000 to 2016-12-31 22:08:60.000

Thanks,
Cindy

14

Just in case there were time zone issues when the data was archived, widen the trend window +/- 24 hours on either side of data - then, make sure to select all data points and try to trend again.

15

How can I zoom out y-axis only? I still don’t see anything in Trend…

16

Make sure “Choose an interval relative to current time” is not checked on main screen, then, on trending screen click the magnifying glass with a minus in it (zoom out) several times until the date range is wide enough. Once the date range is good click the “Zoom Fit” button (right next to zoom out button). Now you should see some trends, perhaps just a vertical line - select a region around these lines and keep repeating to zoom into data.

Make a note of the current time range and retry export…

17

image
image1110×919 107 KB

18

Hello Ritchie,

I adjusted the time range to wide enough - a few days before and after the data time. But when I click “Zoom Fit”, the time range is changed from 1/1/2017 to 1/1/2018. It seems to me that my software is not recognizing the data in .d file…

19

Hi Ritchie,

Happy new year!

CSV export is working now! It turned out to be some issues with the .dat2 file.

I have 10,000 .d files to get exported. Each file has about 12 minutes of data and they are named as “ppa_archive_2016-12-31 21!39!34.066_to_2016-12-31 21!52!46.066”. So, for each export, I have to rename the file to “ppa_archive.d” and select the appropriate time range. What is the best way you recommend to automate the export process?

Thanks,

Cindy