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!

<img src="img/excel-postgresql-db/finish-4.PNG" width="100%" />

---

class: biker

# user names

... and passwords will be sent separately

Notes for current slide

Notes for next slide

Evidenca DB to Excel

Eric Stemmler

28. June 2022

1 / 9

What this is about

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/

... or scan this!

2 / 9

Step One

Installations

This requires to install PostgreSQL ODBC Driver (most current version as of today (2022-06-28).

3 / 9

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.

In the tab "User DSN", click on button "Add".
Select "PostgresSQL Unicode", click "Next"
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]
(You can try if the connection works by clicking on "Test"

4 / 9

Step Three

Start Excel
Go to Tab Data → Get Data → From Other Sources → From ODBC

5 / 9

Step Three

Start Excel
Go to Tab Data → Get Data → From Other Sources → From ODBC
From the dropdown menu, select the previously created Data source name (e.g., "evidenca_db")

6 / 9

Step Three

Start Excel
Go to Tab Data → Get Data → From Other Sources → From ODBC
From the dropdown menu, select the previously created Data source name (e.g., "evidenca_db")
You see a folder tree, all tables are listed under folder public
You have only access to evidenca_bcmt_view, click on it, and then Load.
(It's possible to use SQL and apply filters etc. as well)

7 / 9

Done!

8 / 9

user names

... and passwords will be sent separately

9 / 9

What this is about

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/

... or scan this!

2 / 9

Paused

Help

Keyboard shortcuts

↑, ←, Pg Up, k	Go to previous slide
↓, →, Pg Dn, Space, j	Go to next slide
Home	Go to first slide
End	Go to last slide
Number + Return	Go to specific slide
b / m / f	Toggle blackout / mirrored / fullscreen mode
c	Clone slideshow
p	Toggle presenter mode
t	Restart the presentation timer
?, h	Toggle this help

Esc	Back to slideshow