-
Notifications
You must be signed in to change notification settings - Fork 0
/
spreadsheet-database-vignette.qmd
130 lines (76 loc) · 7.23 KB
/
spreadsheet-database-vignette.qmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
---
title: "Spreadsheet with Database Connection Comparison"
editor: visual
date: '2023-12-11'
description: Compares different spreadsheet apps and how well they support direct database reading and writing, and any restrictions
tags:
- Google Sheets
- numbers
- excel
- LibreOffice Calc
- spreadsheet
- database
- comparison
---
December 11, 2023
### Overview
This document contains information on what is needed to connect to databases from various Spreadsheets, to read and write data, and lists any restrictions where they exist
In general, it's much easier to get data put directly into a spreadsheet from a database than to update a database from a spreadsheet. In all cases, using CSV files for reading and/o writing is a valid option
The use of CSV files instead of directly reading/writing to the database is supported by all spreadsheets
| Spreadsheet | Reading DB | Writing DB | Notes |
|----------------|:--------------:|:--------------:|------------------------|
| [Google Sheets](#google-sheets) [✔]{style="color:green"} | [✔✔✔]{style="color:black"} | [✔✔]{style="color:black"} | Write scripts to move data; programming skills help |
| [Numbers](#numbers) [✖]{style="color:red"} | [➖]{style="color:darkgrey"} | [➖]{style="color:darkgrey"} | |
| [Excel (Windows)](#excel-windows) [✔]{style="color:green"} | [✔✔✔]{style="color:black"} | [✔]{style="color:black"} | Update DB with CSV is easiest; need ODBC driver |
| [Excel (Mac)](#excel-mac) [✔]{style="color:green"} | [✔]{style="color:black"} | [✔]{style="color:black"} | SQLServer is supported; buy other ODBC drivers |
| [LibreOffice Calc](#libre-office-calc) [≈]{style="color:grey"} | [✔✔]{style="color:black"} | [➖]{style="color:darkgrey"} | |
^*Spreadsheet\ Rating:\ [✖\ ]{style="color:red"}\ Not\ supported\ [✔]{style="color:green"}\ Supported\ [≈]{style="color:darkgrey"}\ Mixed\ support*^\
^*Reading/Writing\ Difficulty:\ ✔\ Hard\ ✔✔\ Medium\ ✔✔✔\ Easy\ ➖\ Not\ supported*^
### How Tested
A sample MySQL database was started on the OpenStack instance at the University of Arizona, and made publicly accessible (password protected)
LibreOffice Calc was tested on an Ubuntu 22.04 system
Different database instances may have different requirements. For example, needing to be on a particular VPN, or using two-factor authentication
### Google Sheets {#google-sheets}
Scripts are written to read and write data from a database using Google provided connectors
**Restrictions**
Postgres and its derivatives (PostGIS, et. al.) are not supported
**Reading**
A generic [Google Apps Script](https://developers.google.com/apps-script/overview#:~:text=Google%20Apps%20Script%20is%20a,Calendar%2C%20Drive%2C%20and%20more.) can be used to pull data into a spreadsheet and examples are easy to find. For example, <https://www.lido.app/tutorials/google-sheets-connect-to-database>. Adjust the SQL statement to meet your needs and be sure to specify what the sheet name is in the script(s)
**Writing**
Writing to the database can be easy if all the data in a spreadsheet is to be sent to a table in the database. Google documentation provides a simple way of doing that: <https://developers.google.com/apps-script/guides/jdbc>. If data filtering is needed, or if data is from multiple sheets, then it's necessary to accumulate each row of data to insert into the database before writing it. This can become complicated, so it might be easier to have a special sheet with the data to push to the database and use a generic script (with necessary modifications, such as table and column names)
**Conclusion**
Most flexible solution for directly reading and writing to a database. However, some programming skills come in handy, and having a script separate from the spreadsheet seems a little weird
### Numbers {#numbers}
This Apple spreadsheet application doesn't connect to databases. Use other means to use CSV files for fetching data fo the spreadsheet and updating the database
**Conclusion**
No database connections supported
### Excel (Windows) {#excel-windows}
Setting up database connectivity can be painful and complicated, and may require a system administrator.
**Reading**
Connect to the database using the ODBC connector that matches the database you are pulling data from. You can select tables and columns, or write SQL commands to fetch the data into your spreadsheet. Microsoft has a [web page](https://support.microsoft.com/en-us/office/import-data-from-data-sources-power-query-be4330b3-5356-486c-a168-b68e9e616f5a?redirectSourcePath=%252farticle%252f8760c647-88b9-409d-b312-6ea8f84a269b) containing instructions for multiple data sources; scroll down to see the different sources and their instructions.
The following download links may help:
- Visual Studio redistributables: <https://my.visualstudio.com/Downloads>
- ODBC drivers: <https://dev.mysql.com/downloads/connector/odbc/>
**Writing**
It's much easier to upload a CSV file into the database than to use other methods. Depending upon the database, a search online can provide you with the means for directly writing to the database (or as directly as possible)
For example, MySQL methods for writing to the database can be found at <https://nanonets.com/blog/import-excel-into-mysql/>
**Conclusion**
While it can be a pain getting the needed software onto the system, you only need to do it once for each database type. Excel provides a direct read/write solution to databases
### Excel (Mac) {#excel-mac}
**Restrictions**
Only SQLServer is supported "out of the box". For other databases it's necessary to pay for an ODBC driver to connect
**Reading**
Use the SQLServer connectors to read from the database, if that's an option. Otherwise, generate a CSV file with the data wanted and load that into the spreadsheet
**Writing**
Use the SQLServer connectors to write to the database, if that's an option. Otherwise, generate a CSV file with the generated data and upload that into the database
**Conclusion**
If you aren't connecting to SQLServer, you need to use CSV files
### Libre Office Calc {#libre-office-calc}
Install the libreoffice-base package to add the *Database* option to Calc; this may require an administrator to perform and a reboot after it's installed. Install the appropriate JDBC driver somewhere accessible on the system (you will navigate to the driver when establishing a connection in the next step)
The following instructions are written for the MySQL database, but can be easily adjusted for other databases: <https://techsparx.com/blog/2016/08/libreoffice-mysql.html>
**Reading**
Perform a SQL Query to import the data into a spreadsheet
**Writing**
Unable to find a documented way to put data back into a modern relational database. Generate a CSV file and update the database with that
**Conclusion**
Easier to set up on Linux-based systems than Excel on Windows is (see above for Excel on Mac). Pulling data directly is on par with other spreadsheet applications, but direct writing appears to be missing.