Evidenca DB to Excel

class: title-slide

.title[
# Evidenca DB to Excel
]
.author[
### Eric Stemmler
]
.date[
### 28. June 2022
]

---

# What this is about

.pull-left[
How to get data directly from Evidenca Portal Database (PostgreSQL) into Excel?

This presentation can be found under https://rcst.netlify.app/doc/presentations/excel-to-postgres/
]

.pull-right[
<img src="img/qr.svg" width="80%" />

... or scan this!
]

---

# Step One

## Installations

This requires to install [PostgreSQL ODBC Driver](https://ftp.postgresql.org/pub/odbc/versions/msi/psqlodbc_13_02_0000.zip) (most current version as of today (2022-06-28).

![](https://www.postgresql.org/media/img/about/press/slonik_with_black_text_and_tagline.gif)

---

# Step Two: Data Source Name

## Creating a datasource for evidenca DB

After the PostgreSQL ODBC driver has been installed, one can run the program `ODBC Data Sources (64-bit)` from the start menu.

1. In the tab "User DSN", click on button "Add".

1. Select "PostgresSQL Unicode", click "Next"

1. Fill in:

- Data Source: `evidenca db` (as you like)
	- Database: `mot` (!)
	- SSL Mode: `require` (!)
	- Server: `pt.tirana.al` (!)
	- Port: 5432 (!)
	- User Name: `[john_doe]`
	- Password: [password]

1. (You can try if the connection works by clicking on "Test"

---

# Step Three

.pull-left[
1. Start Excel

1. Go to Tab `Data` &rarr; `Get Data` &rarr; `From Other Sources` &rarr; `From ODBC`
]

.pull-right[
<img src="img/excel-postgresql-db/get-data-1.PNG" width="60%" />
]

---

# Step Three

.pull-left[
1. Start Excel

1. Go to Tab `Data` &rarr; `Get Data` &rarr; `From Other Sources` &rarr; `From ODBC`

1. From the dropdown menu, select the previously created Data source name (e.g., "evidenca_db")
]

.pull-right[
<img src="img/excel-postgresql-db/select-dsn-2.PNG" width="100%" />
]

---

# Step Three

.pull-left[
1. Start Excel

1. Go to Tab `Data` &rarr; `Get Data` &rarr; `From Other Sources` &rarr; `From ODBC`

1. From the dropdown menu, select the previously created Data source name (e.g., "evidenca_db")

1. You see a folder tree, all tables are listed under folder `public`

1. You have only access to `evidenca_bcmt_view`, click on it, and then `Load`.

1. (It's possible to use SQL and apply filters etc. as well) 
]

.pull-right[
<img src="img/excel-postgresql-db/select-view-3.PNG" width="100%" />
]

---

# Done!

---

class: biker

# user names

... and passwords will be sent separately