Question-and-Answer Resource for the Building Energy Modeling Community
Get started with the Help page
Ask Your Question
10

Getting csv from sql

asked 2014-12-11 13:26:55 -0500

jeff.lee812 gravatar image

updated 2017-06-07 12:03:55 -0500

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!

edit retag flag offensive close merge delete

6 Answers

Sort by ยป oldest newest most voted
10

answered 2014-12-11 16:28:07 -0500

updated 2014-12-11 23:32:24 -0500

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

edit flag offensive delete link more

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 gravatar imagejeff.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 gravatar imageLincoln ( 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 gravatar imageaparker ( 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 gravatar imagecheyenne ( 2015-05-06 06:29:19 -0500 )edit

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

xfang gravatar imagexfang ( 2016-02-05 16:27:11 -0500 )edit
7

answered 2014-12-12 09:15:38 -0500

updated 2014-12-12 09:16:19 -0500

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.

edit flag offensive delete link more

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 gravatar imageMatthewSteen ( 2014-12-12 10:32:13 -0500 )edit
5

answered 2014-12-11 15:05:44 -0500

scottb gravatar image

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.

edit flag offensive delete link more
3

answered 2014-12-11 14:48:39 -0500

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.

edit flag offensive delete link more
2

answered 2014-12-14 05:10:17 -0500

Yi Zhang gravatar image

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.

edit flag offensive delete link more
1

answered 2014-12-14 09:54:25 -0500

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

edit flag offensive delete link more

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account.

Add Answer

 



Question Tools

4 followers

Stats

Asked: 2014-12-11 13:26:55 -0500

Seen: 1,583 times

Last updated: May 05 '15