Spreadsheet migration is a very common type of migration, as there are many limitations that prevent spreadsheet from being scalable and they often need to upscale very quickly. In most cases, spreadsheets consist of a way for adding, modifying or removing of data, with certain fields being dynamic and calculated through the use of formulas.
Before you start
You will need to be confident on how to design a database schema. You will be required to identify the entities, attributes, and relationships of the data found in the spreadsheet and create an entity diagram to match.
Details
Level of difficulty
Hard
Stage
Migrate
Suggested time
Around 60 mins for basic spreadsheet and two days for complex spreadsheets. It could be longer though, depending on how complex or messy the spreadsheet is.
Participants
- Product owner
- Business analyst
- Web engineer
Materials
- The Codebots Platform
- A way of viewing the spreadsheet(s)
Steps
- Start a new empty project on the platform.
- Open a blank Entity Diagram
- It is time to identify the first entity from your spreadsheet. Pick a sheet. On that sheet, you will have some data where each row is an item, and each column is information about that item. Whatever term you use to classify those items is the entity name - it very well may be the name of the sheet. Create an entity in the Entity Diagram and give it that name.
- For each column within that sheet, we will need to create a new attribute in the entity. Be mindful that you should use the cell formatting to inform what attribute types or validation pattern are used.
- Repeat steps 3 and 4 for as many entities you can identify in your spreadsheet.
- Identify and add the relationships between the entities. A rule of thumb for identifying relationships is the existence of a column value against one item corresponding to an item from a different sheet.
- Once you have completed the Entity Diagram and gone through all sheets in your spreadsheet(s), setup the CRUD administration pages for the application using the Interface Diagram.
- Complete the build process
-
On the CRUD page you made, you will be able to import data. Click the import button and you will be given an option to download a blank spreadsheet containing the columns and some explanations. You will need to manually copy and paste from the original spreadsheet into this spreadsheet to upload the data.
- For any complex formulas and functions found in the spreadsheet, we recommend following the Activity: Reverse Engineering Requirements migration kit activity. Once you have worked out what the true intention of the complex behaviour is, then you can move forward and create a great user experience.
Justification
Spreadsheets are a common tool that many organisations use for all sorts of various processes. They are great because you can get a quick win and solve relatively complex problems, without needing knowledge of how to program. However, to create more complex spreadsheets you need to be able to use formulas and functions, which is a type of basic programming. There are a number of problems that organisations run into when you spreadsheets have out grown their usefulness.
One of the weaknesses of spreadsheets is getting concurrent users working on them. In the old days, the spreadsheets would be emailed around, and it would be easy to lose track of which was the latest or to struggle to merge in changes from different users. More recently, there are now online spreadsheets such as Google Sheets that provide multi-user access.
However, the biggest problem that still remains is the UI. Asking the people of your organisation to update a spreadsheet for a business function ends up being a very poor user experience. Furthermore, spreadsheets become unmaintainable quickly once formulas and functions are added, due to the openness and changes that can be made. It is pretty much a free-for-all.
Written by Tessa Mylonas
Head of Support and Education
If Tessa isn’t busy answering support questions or educating you about the Codebots products, she is typically found putting together flat-pack furniture, playing with her chocolate Labradors or hoarding houseplants.
