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.

Part 2 will get you started on importing files from Excel to the Caspio platform.

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 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

Let’s quickly review the sample Excel file you’ll be importing inside Caspio.

The Sales.xlsx file consists of 5 different spreadsheets:

  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 called the primary key (Sale_ID).

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

    SALES DATA

  2. demo_tbl_products – This table is used as a lookup table and contains a list of products. 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, only this table contains your list of states.

  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 at the momement, but you can have multiple administrators who have highest level permissions. They can log in and have the ability to edit products and sales, reassign a sale to a different rep and more.

Now, let’s go inside Caspio and learn how to import this Excel file.

Uploading the Excel File

Once you’re logged inside Caspio, click the New App link on top. You’ll then be given two ways to begin:

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

Since you’ll be creating an app from an existing Excel file, click Start by importing data. Next, you’ll be asked to name your app. Input Sales Management for this exercise, although you’re free to use your own naming convention.

Next, upload the sample Excel workbook called Sales.xlsx and click Next.

On following 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 spreadsheets, 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. Click Next to proceed.

This following screen will give you the ability to toggle back and forth between your 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: Field Name (Source)
  • Edit the names into something else in: Field name (Destination)
  • Include or exclude fields to be imported into Caspio: Include

In the next section, you’ll learn how to associate the correct data type with each of your fields.

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 data type from Number to Autonumber. This will automatically assign a new ID that is numerically incrementing for each admin user.

    • Change the Password data type from Text (255) to Password. This will encrypt the password column for anyone who opens your table.

  • 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.

    • Change the Password data type from Text (255) to Password.

  • Table 4: demo_tbl_sales (New)

    • Change the Sale_ID data type from Text (255) to Autonumber. This will serve as the main table keeping track of all your sales

    • Change the Rep_ID data type from Number to Integer. That’s because you’ve previously configured each new rep to have Autonumber values. And to correctly stamp the unique IDs from demo_tbl_reps (which are whole numbers), the data type must be Integer. This is how you’ll recognize the sales reps on this table.

    • Change the Sale_Amount data type from Number to Currency,

    • Change 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, you are not in accordance with best practices.

However, you don’t need to use Autonumber every time. You can also implement the following ID types:

  • 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.

Revisiting Your Tables

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.

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

Caspio enables you to build multiple applications fast, to 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.

Now, hover your cursor on the app container labeled Sales Management, and click Open. This will take you back inside its app framework. You’ll notice that the first object highlighted by default is the Overview tab.

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

This screen is mostly informational to help you keep track of your progress. You can even add notes as you begin to develop your application.

But 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, we mentioned that changes can still be made 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.

    • Hover your cursor on demo_tbl_admin and click on Design.

    • Find the Email field and check the box under the column Unique. We do this because no two users should have the same email address. The email field will also be used to log into the application later on.

    • When you’re done, Save the admin table to keep your changes.

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

    • Follow the same procedure as Revision 1.

See You in the Next Video!

This concludes Part 2 of our ultimate video series that teaches you how 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

NEW FORRESTER WAVE REPORT
See why Forrester ranks Caspio a 'Leader' in low-code platforms in this 2019 report.
TALK TO AN EXPERT
Have a vision for an application? Talk to a Caspio product expert and we’ll help you achieve it.
SEE CASPIO IN ACTION
Want to see if Caspio is a good fit for your needs? Choose a date and time for a personalized demo.