Welcome to our ultimate video guide on turning spreadsheets into apps. This video series will cover everything you need to learn to create web applications using your Excel files, from importing the file online to deploying dynamic forms and reports to your website.

SUMMARIZED VIDEO TRANSCRIPT

The Excel file Sales.xlsx used in this tutorial is available for download. All you need to do now is set up a 14-day Caspio free trial and follow along. It’s the best way to learn how to use Caspio.

Expect to have an Excel-based application you can showcase to your co-workers and friends at the end of the entire series. But for now, let’s learn how to import an Excel file into Caspio to begin developing the application.

Reviewing the Sample Worksheets

Before you start uploading Excel files to the Caspio Platform, try to review the sample spreadsheet you’ll be using.

How to Convert Excel to a Web App | Part 2: Importing Excel Files SALES DATA

The Sales.xlsx file consists of 5 tables:

  1. demo_tbl_sales – This table contains Product Name, Representative ID, City, State, Sale Amount and Sale Date. Each sale entry contains its own unique identifier, the primary key: Sale_ID.

  2. demo_tbl_products – This table is used as a lookup table and contains a list of products and states. Imagine a drop-down field in a web submission form where you can select the product — that’s exactly what this lookup table is going to serve.

  3. demo_tbl_states – Same as above.

  4. demo_tbl_reps – This table consists of the Name, Email and Password fields, with Rep_ID as the primary key. Notice that Rep_ID is also used in the demo_tbl_sales table. A primary key that uniquely identifies a row of another table is called a foreign key, and is used to connect data between tables.

    In other words, Rep_ID is used to create a relationship between reps and sales tables.

    How to Convert Excel to a Web App | Part 2: Importing Excel Files REPS DATA
  5. demo_tbl_admin – And finally, we have a table that contains the list of all the administrators. There is only one admin listed there, but you can have multiple administrators. In the app we are building, admin has the highest level permissions, which allow them to edit products and sales information, reassign a sale to a different rep, etc.

Now, log in to your Caspio trial account and learn how to import data from an Excel file.

Uploading the Excel File

There are two ways to create an app with Caspio:

  1. By importing your existing data
  2. With a blank app

In this exercise, you’ll create an app from an existing Excel file. To do so, log in to your account, click New App, then click Start by importing data. Next, you’ll be asked to select an app name (input Sales Management) and upload a file (other supported files extensions are Access and ZIP).

Once you’ve uploaded the sample Excel workbook called Sales.xlsx, click Next.

On the next screen, you’re going to see a list of all the tables that you’re importing from Excel to web. You can also pick and choose which table you want to bring in through checkboxes in the column Source Object. In this case, bring in all five tables.

How to Convert Excel to a Web App | Part 2: Importing Excel Files

The second column, Action, allows you to specify how you want to bring this data into Caspio. Since new database tables will be created from the worksheets, Create New should be selected. Other options include Replace, Append, Update and Update Design.

You can also change the table names to something else in the third column, Destination Object. Use the default names in this tutorial for now. After making sure that everything is in order, click Next.

This screen will give you the ability to toggle back and forth between your in-transition worksheets under the column Tables.

How to Convert Excel to a Web App | Part 2: Importing Excel Files

And under Configuration Options, you can:

  • View the original field names within your selected table in Field Name (Source)
  • Edit the names into something else in Field name (Destination)
  • Decide to include or exclude fields through checkboxes in Include

In the next section, you’ll learn how to associate the correct data type with the fields you’ll be importing.

Configuring the Data Types

The modifications you’ll be making next are essential to keep the integrity of the app. Note that these changes can also be done later after you import your data.

How to Convert Excel to a Web App | Part 2: Importing Excel Files
  • Table 1: demo_tbl_admin (New)

    Change the User_ID field from Number to Autonumber. This will automatically assign a new ID that is numerically incrementing for each admin user. Then change the Password data type from Text (255) to Password. Now if anyone opens your table, they will find that the password column is encrypted.

  • Table 2: demo_tbl_products (New)

    Change the Product_ID data type from Number to Autonumber. This, in turn, will assign a new ID with every new product.

  • Table 3: demo_tbl_reps (New)

    Change the Rep_ID data type from Number to Autonumber; and change the Password data type from Text (255) to Password.

  • Table 4: demo_tbl_sales (New)

    For this table, which serves as the main table responsible for keeping track of all the sales, change the Sale_ID data type from Text (255) to Autonumber as well.

    But with the Rep_ID field, change the data type from Number to Integer. That’s because you’ve previously configured each new rep to have Autonumber values in its parent table, demo_tbl_reps. And to correctly stamp its primary key (which are whole numbers) on the Rep_ID field here, the data type must be Integer. The Number data type, on the other hand, should only be used when dealing with decimal numbers.

    For the last two columns — change the Sale_Amount data type from Number to Currency, and the Date_of_Sale data type from Text (255) to Date/Time.

  • Table 5: demo_tbl_states (New)

    Change the State_ID data type from Number to Autonumber.

You may have noticed a similar pattern when you look at all your tables — each of them has an Autonumber. That’s because each table in a database environment should always have a unique, automatically generated number called a primary key. If you have a table and you’re not automatically generating a number, then you’re not in accordance with best practices.

However, you don’t have to use Autonumber every single time. You can also implement the following:

  • GUID – a long, secure and randomly generated ID
  • Prefixed ID – where the first characters are custom set to go with incrementing numbers
  • Composite Key – an ID that concatenates multiple fields into one using a formula field (remember to flag it as unique).

Once you’re done, go ahead and click Import.

How to Convert Excel to a Web App | Part 2: Importing Excel Files

As soon as you hit the Close button, Caspio immediately takes you inside the framework of your Excel to web application.

Introducing Your App Framework

Before going over your application framework, click the Home link on Caspio’s top menu bar. You should see a list of all your apps.

Caspio enables you to build multiple applications fast, and these apps can be used in different departments. You can even become a reseller if you want. In short, there are a lot of ways to leverage the Caspio platform.

How to Convert Excel to a Web App | Part 2: Importing Excel Files

Now, hover your cursor on the app container labeled Sales Management, and click Open to go back inside its application framework. The objects on the left-hand side, namely Overview, DataPages, Tables, Views, Authentications, Connections, Styles, Localizations and Tasks, include some of the tabs you will use to build your Excel web app.

How to Convert Excel to a Web App | Part 2: Importing Excel Files

You’ll notice that the first object highlighted by default is the Overview tab. This screen is mostly informational to help you keep track of your progress.

Here are the seven metric boxes you should take note of:

  1. Tables – Monitor how many tables you currently have.
  2. Deployed DataPages – Monitor how many DataPages or interfaces you’ve deployed.
  3. Views – Monitor how many views or virtual tables you’ve created.
  4. Users – Monitor how many users and roles you have in the app.
  5. Records – Monitor the total records and record size of your tables.
  6. Files – Monitor all the attachments or files within the app.
  7. Storage – Monitor the running size of your application.

Aside from that, you also have the option to add Notes on the lower right side as you develop your application.

Revisiting Your Tables

The most important place where you need to begin is always the tables object. Go ahead and click the Tables tab on the left.

How to Convert Excel to a Web App | Part 2: Importing Excel Files

Tables are the foundation of any app you develop inside Caspio. This is where all your data is going to reside.

After making sure that your tables from Excel are already imported, hover your cursor on demo_tbl_sales and click Open. You’ll see that all the sales data were also transferred. They are now sitting inside an online database.

How to Convert Excel to a Web App | Part 2: Importing Excel Files

Once you have the table opened, you can click on each cell to make modifications. But to edit your fields, you need to click on Table Design.

How to Convert Excel to a Web App | Part 2: Importing Excel Files

If you’ll recall back in the import screen, it was mentioned that changes can still be made even after importing your data. That’s exactly what you can do under table design. Here, you can change data types, rename fields or introduce new ones to your tables.

There are two more changes you need to make that the import screen doesn’t allow you to do:

  • Revision 1: demo_tbl_admin

    Go back out to the tables menu by clicking the Tables tab, then hover your cursor on demo_tbl_admin and click on Design, then find the Email field and tick the checkbox under the column Unique. When you’re done, Save the admin table to keep your changes.

    Given that no two users will have the same email address, the Email field should be unique to function as your login username (more on that in Part 3 of the video series).

    How to Convert Excel to a Web App | Part 2: Importing Excel Files
  • Revision 2: demo_tbl_reps

    You’ll also need to do the same to your rep table. Click the Tables tab to go back to the table menu, then hover your cursor over demo_tbl_reps and click Design, then find the Email field and tick the checkbox under the column Unique. When you’re done, Save the table.

See You in the Next Excel to Web Class!

This concludes Part 2 of our ultimate video series that teaches you to make a web app from excel data.

The tutorial covered how you can make modifications to your data types, while going through the process of importing an Excel file into Caspio.

Be sure to watch the next video on how to build a login screen for both the admins and the reps.

Next Steps

DOWNLOAD FREE REPORT
See why Forrester ranks Caspio a 'Leader' in low-code platforms for business developers in this 2019 report.
REQUEST FREE CONSULTATION
Got a vision for an application? Get free consultation to see how we can help you achieve it.
SCHEDULE A DEMO
Choose the most convenient date and time for you to get a demo from our team.