From Excel Hell to Cloud Database Heaven
From Excel Hell to Cloud Database Heaven
Migrating from Spreadsheet Hell to Cloud NoSQL Database Heaven is doable, if not easy — and it will pay back in increased productivity for your business.
Join the DZone community and get the full member experience.Join For Free
Databases are better when they can run themselves. CockroachDB is a SQL database that automates scaling and recovery. Check it out here.
Most well-known database technologies have some or all of the following features:
- Data quality and consistency:
- A data schema with a detailed description of all data resources and properties.
- Automatic data validation according to the data schema.
- Row/document locking to prevent data collision.
- Access control:
- Define access roles to allow/prevent read, write, or delete on resources.
- Allow users to have private data views of shared resources.
- Data relations
- Query language
- A REST API for platform-agnostic data access and integration.
- A platform-specific SDK.
The rest of this blog post is a step-by-step tutorial on how you can migrate from spreadsheets to a fast and consistent NoSQL cloud database using RestDB.io.
This tutorial will learn you how to:
- Prepare and export spreadsheet data.
- Import data.
- Map data relations and formula/logic.
- Set up users and access roles.
- Publish some data to the web.
1. Prepare and Export Spreadsheet Data
Imagine that we have a spreadsheet called Database for sales of vintage Apple products. The spreadsheet has three sheets: Customers, Products, and Sales. We see that the Sales sheet mimics database functionality by referring to a CustomerID and a ProductID.
Before exporting the data, make sure that the first row of each sheet has a valid name. This will be the field names in our new database. We export each sheet as a separate CSV (comma-separated values) or Excel file.
For example, the content of our exported
Sales.csv file looks like this:
CustomerID,ProductID,Quantity,Purchase Date,Price 999662,2,2,2017/02/01,"$1,120.00" 997093,7,1,2017/02/02,$700.00 995248,8,2,2017/02/03,"$2,400.00"
Note that the important first row has valid column names, but the Price column doesn't seem correct.
Pitfall 1: Currency Formats
Exports to CSV with currency formats can often result in a string like
"$1,120.00" instead of a valid number
1120.00. Change column format to Automatic before exporting.
# changed currency format to automatic number format 999662,2,2,2017/02/01,1120.00 …
Pitfall 2: Line Breaks
Data cells with line breaks will not be removed in the exported CSV file, hence potentially breaking the import. See the example below, where the line break in the address field gives a potential erroneous CSV file. Fix this manually or with a script before exporting to CSV again.
CustomerID,name,address 999662,Ward and Sons,"328 Swaniawski Hills, Port Alview, IN 88649" 998406,Feil and Sons,"96 Rosalee Ford Suite 495, Hagenesburgh, OH 24769-1364"
2. Import Data
We are now ready to import our three spreadsheet files:
Let's create a new database first. Click the Create New button and give the database a unique name; for example, spreadsheetdb.
Our new empty database now has the following URL endpoint: https://spreadsheetdb-9145.restdb.io. We can now start to import our data files. Click the Import button.
Upload the first file by dragging or clicking on the big upload area.
For each file, you must specify a unique collection name and column names and data types. For example, for the Customer file, we change the second column name from name to capitalized Name. We also get a handy preview of some data rows before starting the import.
Note that we can also specify the data type for each column by clicking on the dropdown list under each column name.
Importing data to our database runs as a background job, and for large files, this can take some time. Therefore, you will receive an email with a report after each data import is completed. In this case, we only imported a small dataset and all data was imported without errors.
After doing the same operation on all three files, we have our first version of the database ready. We have all our data from the spreadsheet, and inspecting the Products collection shows that the data import was a success.
We're all set to start mapping relations and some logic.
3. Map Data Relations and Formula/Logic
Let's change the ID fields in the Sales collection from numbers to actual objects instead. In Development Mode, we navigate to the settings of Sales. We can see that the CustomerID and ProductID have a data type of number. We click on the CustomerID field and change the data type to customers and specify Select one customer.
Important: Select the Advanced option and choose the mapping column from the target collection.
We repeat the operation for the ProductID field, as well. The only difference is that we pick the Products collection and the ID column as a foreign key.
We also want to automate the calculation of sales price. We will add a small formula to the Price field for this. Click on the field and open the Calculate value option. We add a formula to multiply the first (relations are always arrays) product price with the quantity.
This concludes our mapping of data and formulas. If we open a Salesi record we can see the finished result. Instead of number IDs, we have "live" objects, and instead of manual data entry of prices, it's automatic.
The Sales collection list view shows that we have a consistent data view of sales, products, and customers.
One of the benefits of a database is the search capabilities. We can do a global search, i.e. for a customer named ward*, and the system instantly finds any related item for us. This example search shows that Ward and Sons has two purchases in the Sales collection and one entry in the Customers collection.
4. Set Up Users and Access Roles
Role-based access control is particularly useful when you need to restrict access to what users can see and do in the data manager in your database (documentation).
The database owner/creator can invite and manage external users to the database. There are three types of admin levels for users:
Administrators and Developers can change the data schema, invite/delete users, etc. Users can only work with data. After inviting a new user, we can assign them different roles. Roles are tags that we can assign to a particular "access right" later. We have invited two external users to our database. Let's assign two different roles to our users: manager and apprentice.
Invited users get an email with a link to their account. Accepting an invite grants access to the database with the given roles managed by the database owner.
Setting access right for users/roles is done in the Collection Settings menu. In our database, we wish to restrict access to the Sales collection. We grant full access to managers but only read access to apprentices.
When logging in as a user with the apprentice role, the Sales collection will appear as "read-only" due to the access restriction. The screen shot below shows that all the input controls are disabled and there is no Save button.
6. Publish Data to the Web
restdb.io lets you create Pages that can be accessed from the external web (documentation). Pages can be public or protected with authentication. In this simple example, we'll create a public product list. A Page is just HTML with HandlebarsJS support. It can run database queries, which provides a data context for the Page. This makes it extremely easy to publish content from the database. The screenshot below shows the HTML code for our product list.
To publish this Page, we navigate to the Settings tab and check the Publish option.
After updating the settings for the page, we can publish our URL to anyone on the world wide web.
You can watch the live demo here!
A screen shot of our product list page is shown below:
I hope this article has shown you that migrating from Spreadsheet Hell to Cloud NoSQL Database Heaven is doable, if not easy. Yes, it involves investing some time on preparation and implementation, but this investment will hopefully pay back in increased productivity for your business.
Please feel free to share this article if you enjoyed it!
Published at DZone with permission of Jon Erik Solheim , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.