In Worksheet Systems, you can import your data from an Excel file, map the columns, and make easy modifications to the data and structure. The data import tool lets you quickly search through all your data and gives such advantages as data availability and enhanced reporting.

To import your data from an Excel file, follow these steps:

  1. In the navigation panel on the left, click Import Data > From Excel File.

  1. In the wizard that opens, click Select XLS File, and then choose your XLS file.

  1. The wizard will display all tabs, rows, and columns of your XLS file. Select the tab that you are going to import, and then click Next.

Mapping the Columns

  1. On the next page of the wizard, you should map your Excel columns to the table. Fill in the following data:

  • Main Sheet: Select the worksheet that you want to map.
  • Header Row: Look at your source file and find the row that contains the column headings. On the sheet in the example, this comes eight rows down. Enter this row number into the header row box. It is vitally important that you specify the correct header row, because it determines the data type for each field.
  • Bottom Row: Select one of the following options:
    • Last Row: Import all rows to the bottom of the worksheet.
    • Until Column is empty: Import all rows until the specified column contains an empty cell.
    • Until Column value equal: Import all rows until the specified column has a certain value. For example, Column A (the ID No.) is equal to 551.

After you enter all parameters, click Refresh to select the correct data types.

Correcting the Data

  1. On the right of the screen, there are two columns: Preprocess Action and Preprocess Args. The Preprocess Action column contains a drop-down with the following options:
  • RemoveSymbols: This is useful if you want to quickly go through the data in a column and delete a specific character or string.  For example, enter ‘S’ in the Preprocess Args column to remove all occurrences of the letter s.  Please note that this is not case sensitive.
  • Trim: You can also trim empty spaces from text fields by selecting Trim and leaving the Preprocess Args column blank.

Exporting the Mappings

  1. To save time in future, you can save the mappings to a file. Just click the blue down arrow to export or the green up arrow to import.

Adding or Removing the Columns

  1. The next step is to add or remove the columns. If you are familiar with Microsoft Access, you know how it works. Just scroll down to the bottom of the table, click in the empty row at the bottom, and begin to type.  Enter the field name, data type, source sheet, and source column. For more information, see Microsoft Access help arcticles.
  2. Click the arrows on the right of the screen to move the new fields up or down the list.

  1. The source field can also be a cell reference.  For example, if you have a heading that you want to use as a column in cell C2, just type C2 into the source field.  In the example below, we added fields for Store Name and Reported by.

  1. If you are creating a new table, leave the New checkbox selected and enter the table name in the Data Table Name field. If you want to add data from the imported table to an already existing table, then deselect the New checkbox and choose the main table from the Data Table Name drop-down list.

  1. When you are ready with the data structure, click Next.

Exporting the Data

  1. Click the Export button to export the data to either CSV or JSON.

Adding a Primary Key

  1. Finally, you have an option to add a unique key. It is a good idea if you do not have a column with unique values, for example, if your Order ID has duplicate values. Please note that the ID field is an integer. It means that even if your Order ID is unique, you should add a unique key if the data type is text. With large volumes of data, searching the ID column as opposed to text will significantly improve the performance.

  1. Click Finish & Save to complete the import process and save your file under Data Tables.  Start the process again if you have another file to import.

Please also see our video that describes the Excel import process:

In Worksheet Systems, you can also extract data from CSV files, in addition to XLS files. For more information, see How to Import CSV Data.