Page Comparison

After you log into Canvas Studio, perform the following steps to import a spread sheetspreadsheet.

1. Click File Import on the top pane.
   
    

2. Click Browse Files to open up file wizard and choose the spread sheet spreadsheet that you want to import.
   
   

   Note
   Canvas allows you to select only one file at a time. You can also drag and drop files in the file upload panel directly.

   You will see the data appear in a similar row column format in the Canvas Studio as that of in the spread sheetspreadsheet. Canvas renders the first five records for display.
   
   
   Check the data in the screen and their corresponding column headers. Perform the following steps for more customization.
   
   

3. You can choose to import or not import any column from the spread sheet spreadsheet by enabling or disabling the import checkbox. By default, the Import check box is selected.
   
   
4. The File Column Name  displays the column name from the spread sheetspreadsheet. Canvas trims any special characters (including spaces) present in the column name before feeding into the database. Therefore, you can edit the column name appropriately that should be created in the database. The DB column name, column data type, data length, index and other details can be specified by clicking the plus icon next to the column name.
   
    
   
   
5. In the Target Table field  you can specify the database table name to which you want to import the data to. By default, Canvas prepopulates the spread sheet pre-populates the spreadsheet file name prefixed with IMP_ in the target Table field. However you can edit and modify the name based on requirement.
   
   
6. Canvas allows you to import the data to either a new table or to an existing table. You can do so by the choice of Target Table name that you specify in the field.
   
   
   1. Import data in a new table: To import the data to a new table in the database, specify a new table name in the Target Table field that is not available in the database.
   2. Import data in an existing table: To import the data to an existing table, specify the name of the existing table available in your database in the Target Table field.
      While importing data to an existing table, if there are new columns imported from the spread sheetspreadsheet, those are automatically created in the database during the merge. 
      
      Mapping of Spreadsheet Column and Database Table Column: You can choose the database column that you want to import the spread sheet spreadsheet column into. The column names are auto populated in the dropdown with respect to the existing database table name that you enter. Select the table column appropriately and map the corresponding imported spread sheet spreadsheet column.
      
      
      Also, if you want to merge, replace or append the imported data with the existing data, Canvas helps you to do that with the following buttons.
      
      
      1. Click  to replace overwrite the existing entries records in the table when you modify the existing databaseexisting table with the data from the spreadsheet.
      2. Click  to merge the new data from the spreadsheet with the old one when you modify the existing databasethe existing records in the table. Note that, based on the primary key the existing records are updated and news entries are added to the table.
      3. Click  to add append the new data from the spreadsheet with the old one when you modify records in the existing databasetable. Note that this creates duplicate records.
         
         

7. Click the Select Template lookup to find an existing template of the database table and apply the structure to the current data that you import. The template of your spread sheet spreadsheet import will be saved automatically by the Canvas Studio.

   Note
   Selecting a template cannot be done for the first time that you import a spreadsheet file since there will be no template available in the Canvas Studio. For the consecutive imports, you can choose the templates that were created previously.

   
   

8. Click Advanced tab to modify, process and compute the data before feeding it into the database by applying rules to the data. 
   
   

   #	Component	Description
   1	File Description	Specify a description to the file import.
   2	Column Level Post Processing Rule	Add column level rules to process the data.
   3	Row Level Post Processing Rule	Add row level rules to process the data.
   4	Post Processor Class	Add the class name that contains the implementation of the rule.

   In the Advanced section, you can apply rules for the data. For example, if you want to change the data type of the data, you can do so by applying rules to it.
   
   Sample Scenario: Consider a spread sheet spreadsheet which has cells that contain hyphens. You do not want to feed hyphens into the database and replace it by null, so you implement a rule in a processor class.

   Code Block
   language java
   public class FileDataPostProcessor { /** * This method returns null for -. * This method is used while formatting and importing data from Excel. * @param value to be formatted * @return string */ public static final String hyphenConvert(String value) { if (StringUtils.isNotEmpty(value) && "-".equals(value)) return null; else return value; } }

   Refer the following example to add column level and row level rules for the data:
   
   Column level post processing rule:
   To invoke hyphenConvert method, add the following rule in the Column Level Post Processing Rule field:

   Code Block
   language java
   { "EMPLOYEE_ID" : "i.hyphenConvert(val)" } /** * where, * 'EMPLOYEE_ID' is the database column * 'i' is the object to invoke user-defined methods * 'hypernConvert' is the user-defined method in the processor class * 'val' is the output that the method returns */

   
   

   Note
   The object "ct" should be used to invoke canvas default methods such as hyphen, toInt, toDate and merge. Use the object "i" to invoke any other user-defined methods. Any method will always return the object "val".

   Similarly to add Row level post processing rule, consider the following method to merge the values from the columns specified in the rule.

   Code Block
   language java
   /** * This method merges the columns specified in * fields_name of JSON config and returns the value. * In the sample, ct.mergeColumn(rowData,jsonConfig) is the * expression for merge and "JSON_CONFIG" provides the * required data for it. * @param dataMap Map of columns in excel and value * @param jsonConfig JSON configuration provided in row processor. * @return The processed value */ public static final String mergeColumn(Map dataMap, JSONObject jsonConfig) { String returnValue = ""; if(dataMap == null || dataMap.isEmpty()) return null; String fieldNames = jsonConfig.optString("FIELDS_NAME",null); String fieldsArr[] = fieldNames.split(","); for(String field : fieldsArr) returnValue = returnValue + ("".equals(returnValue)?"":" ")+ getValueOf(field, dataMap); return returnValue; }

   
   Row level post processing rule:
   To invoke mergeColumn method, add the following rule in the Row Level Post Processing Rule field:

   Code Block
   language java
   { "PROJECT" : { "EXP" : "i.mergeColumn(rowData,jsonConfig)", "JSON_CONFIG" : { "FIELDS_NAME" : "Company,Project" } } } /** * where, * 'PROJECT' is the database column * 'EXP' is the expression key for the rule * 'i' is the object to invoke user-defined methods * 'mergeColumn' is the user-defined method in the processor class * 'JSON_CONFIG' is the value from the database columns */

   
   

9. Click Preview Mode once the data is ready to publish and preview the final data that will go into the database. In the Preview mode, check your data, columns that you selected to be rendered, modifications that you made to be data and the configurations that you enabled.
   
   

   Note
   If your final data does not show up in Preview mode, it could be an issue with the data or the file itself such as duplication of columns, grouping of headers etc. In that case, correct the spreadsheet and import again.

   The data in the Preview mode appears as shown in the screenshot.
   
   

10. Click Publish once your data is ready to be loaded into the database and make your data live.