The relational database is best for capturing, storing, querying, and sharing data, and spreadsheets are best for calculating, analyzing and visualizing data. But making changes to this data is not very useful unless you can commit those updates to the database.
Fortunately, Kloudio has you covered with simple template tools to assist you with uploading your changes to the database.
This article shows you how to move your data from Google Sheets to your relational tables in your database so that you can use spreadsheets to validate, transform and ultimately write data back into your database.
Note: A template requires at least one key column from the table for Kloudio to distinguish between new record uploads vs. existing record updates. Kloudio will always create new records if there is no key column chosen.
Let’s get started
Step 1: Select the "Templates" option from the dashboard
Step 2: Click on "New Template"
Step 3: From the Builder screen, complete the following actions:
- Choose an existing connection from the dropdown list.
- Name your template.
- Search and select a data source/table from the right sidebar.
- Select columns to include in the template.
Step 4: From the Properties screen, complete the following actions for each column:
- Review the data type (String/Number/Date)
- Assign a display name
- Optionally, specify whether it should be visible in the template by using the "visible" flag.
- Optionally, specify whether it should be required for user to enter by using the "required" flag
note: If this is checked, the template will fail validation even before uploading in case the user has not entered a value for this column in any row
- You can further add or delete a column using the "plus" or "cross" buttons as shown below.
- Finally, you can also change the order of columns by clicking "down arrow" or "up arrow" buttons as shown below.
Step 5: (Optional) Add custom validations to your data before uploading
Note: The validation type can be in the form of Expression, SQL or function. If you choose an expression, then you can validate any of the columns using any of the allowable operators such as shown above. If you choose SQL, you can call a custom PL/SQL function that will accept any of the columns passed to it and return Y or N depending on whether the data is validated or not.
Step 6: (Optional) From the settings screen, complete the following actions:
Step 7: Specify key column
Note: Kloudio can conduct both inserts and updates to your database. If the primary key column is specified Kloudio will update an existing row and if it is not found a new record will be created. If the primary key column is not specified Kloudio will create new records in your database. It is recommended to choose the primary key to avoid duplicate records.
Save the template
Step 8: Uploading records to database from Google Sheet
Assuming the Kloudio Google Sheets add-on is installed, click on the "Add-ons" tab and select Kloudio.
step 9: Select Login to Kloudio
Step 10: Once you are logged in successfully, select Add-ons -> Kloudio -> My Templates
step 11: Select the template you want to use to upload data
step 12: Click on "Insert Template"
This will insert the required columns for your template plus 2 additional columns that are used for you to monitor the status of upload and view any messages related to validation errors. Go ahead and paste the data you want to upload to the database in all columns of the sheet except the status and message columns as shown below.
Step 13: Enter the records in Google Sheet and then click on
Add-ons -> Kloudio -> Upload -> All
Note: You can also choose to upload only selected records by clicking on
Add-ons -> Kloudio -> Upload -> Selected
The status column will show pending for all records for a moment before changing to either Success/Invalid/Error.