10

Getting csv from sql

Hello, can anyone walk me step by step on how to get csv or raw data from the sql output file? I have zero familiarity with sql.

Specifically, I asked for the zone air temperature hourly (and later daily) in my output variables.

I want to plot this data against my own experimental data.

Thanks!

jeff.lee812's avatar
151
jeff.lee812
asked 2014-12-11 13:26:55 -0500
__AmirRoth__'s avatar
4.4k
__AmirRoth__
updated 2017-06-07 12:03:55 -0500
edit flag offensive 0 remove flag close merge delete

Comments

add a comment see more comments

6 Answers

10

You can download a tool I created using R Shiny to do this here. This tool allows you to choose which variables you want to view and download a corresponding CSV file. Below I show some screen shots of the current capabilities:

image description image description image description

Lincoln's avatar
1.5k
Lincoln
answered 2014-12-11 16:28:07 -0500, updated 2014-12-11 23:32:24 -0500
edit flag offensive 0 remove flag delete link

Comments

1

"To use, install the follwoing R packages:

(shiny), (openair), (plotly), (RSQLite), (reshape2), (ggplot2)"

How do I go about installing those R packages haha further down the rabbit hole

jeff.lee812's avatar jeff.lee812 (2014-12-11 16:54:15 -0500) edit
1

Here is a video that explains how to download packages in R: https://www.youtube.com/watch?v=3RWb5...

Lincoln's avatar Lincoln (2014-12-11 23:58:51 -0500) edit

@Lincoln this looks sweet, definitely going to check it out. @xfang you might be interested in what Lincoln made for plotting/exporting from an eplusout.sql file.

aparker's avatar aparker (2014-12-12 13:42:14 -0500) edit

@Lincoln Hey, I've installed all the packages as described and started the program, but when I try to uplode my sql file, that I got from running EnergyPlus in OpenStudio, I get the message "Maximum upload size exceeded", how can I handle this problem? Thanks :)

cheyenne's avatar cheyenne (2015-05-06 06:29:19 -0500) edit

Thanks @aparker, just saw it. I do want to try it out!

xfang's avatar xfang (2016-02-05 16:27:11 -0500) edit
add a comment see more comments
7

Export the idf from OpenStudio and run the simulation with EP-Launch (on Windows or equivalent). The output variables and meters will be output to the eso output file and converted to csv automatically. Apparently OpenStudio doesn't save the eso output file? If it did, you could run the readvarseso postprocessor on it to convert to csv.

MJWitte's avatar
9.7k
MJWitte
answered 2014-12-12 09:15:38 -0500, updated 2014-12-12 09:16:19 -0500
edit flag offensive 0 remove flag delete link

Comments

1

I haven't used this method, but OpenStudio does create the eplusout.eso file during a run. Here's a slightly different method that I've used:

  1. Run your OSM with the Add Output Variable measure(s)

  2. Open the IDF from the OSM file directory folder > run > 4-EnergyPlusPreProcess-0 > out.idf

  3. Simulate the IDF through EnergyPlus EP-Launch. This will create the out.csv file containing the OutputVariables in the same directory.

MatthewSteen's avatar MatthewSteen (2014-12-12 10:32:13 -0500) edit
add a comment see more comments
5

Open of the SQLite database you created and type:

.tables

Note the following tables:

  • ReportMeterData
  • ReportMeterDataDictionary
  • ReportVariableData
  • ReportVariableDataDictionary

These hold your Meter and Variable csv data. You need the index from the Dictionary tables to query the data.

For example, I'll query the MeterDictionary to see what's in there:

select * from ReportMeterDataDictionary;
8|Sum|Facility:Electricity|Zone||Electricity:Facility|Hourly||J ...

I intentionally trimmed the results. 8 is the dictionary index for the Electricity:Facility meter

To select the data for Variable/Meter in need use:

select VariableValue from ReportMeterData WHERE ReportMeterDataDictionaryIndex=8;

Which returns the Facility Electricity meter. Update your index to what you are looking for. Make sure the variable is added to your output in your IDF. Variable queries are done exactly the same just using the ReportVariableData tables.

Note this is raw SQL commands as you requested. The specifics will change if you are doing this in a programming language.

scottb's avatar
542
scottb
answered 2014-12-11 15:05:44 -0500
edit flag offensive 0 remove flag delete link

Comments

add a comment see more comments
4

You need to run queries against your SQL database and store the output. There are some good example queries and tool tips in this thread.

rpg777's avatar
7k
rpg777
answered 2014-12-11 14:48:39 -0500
edit flag offensive 0 remove flag delete link

Comments

add a comment see more comments
2

In addition to the above solutions, you may use jEPlus as a mediator to extract data from SQLite to CSV. This is particularly suitable if you want to do it repeatedly/programmatically. Here you can find a video on how to do this: http://www.jeplus.org/wiki/doku.php?i...sqliteoutput.

Yi Zhang's avatar
1.2k
Yi Zhang
answered 2014-12-14 05:10:17 -0500
edit flag offensive 0 remove flag delete link

Comments

add a comment see more comments
1

There are a good number of generic scripts and tools for doing this StackOverflow. Some examples:

__AmirRoth__'s avatar
4.4k
__AmirRoth__
answered 2014-12-14 09:54:25 -0500
edit flag offensive 0 remove flag delete link

Comments

add a comment see more comments