Steps to Import Data from Spreadsheet

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

Click File Import on the top pane.
Click Browse Files to open up file wizard and choose the spreadsheet that you want to import.

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 spreadsheet. 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.
You can choose to import or not import any column from the spreadsheet by enabling or disabling the import checkbox. By default, the Import check box is selected.
The File Column Name displays the column name from the spreadsheet. 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, key and other details can be specified by clicking the plus icon next to the column name.
In the Target Table field you can specify the database table name to which you want to import the data to. By default, Canvas 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.
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 spreadsheet, 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 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 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 overwrite the records in the existing table with the data from the spreadsheet.
  2. Click to merge the new data from the spreadsheet with the 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 append the new data from the spreadsheet with the records in the existing table. Note that this creates duplicate records.
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 spreadsheet import will be saved automatically by the Canvas Studio.

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.

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

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:

{
  "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
*/

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.

/**
*  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:

{
	"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
*/

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.

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.
Click Publish once your data is ready to be loaded into the database and make your data live.