Import of Data from Excel- Capabilities
The functionality to import data from Excel introduced in TallyPrime 4.0 offers numerous advantages. It accelerates data entry processes, reducing both time and the likelihood of errors associated with manual data entry. Users can efficiently prepare and organize data in Excel before transferring it into Tally, which enhances the data accuracy. Additionally, it promotes compatibility between different software systems, fostering a cohesive approach to data management across various aspects of business operations.
To achieve this we have introduced some TDL capabilities to make the import of data from Excel to TallyPrime seamless.
Collection Attribute- Data Source
TallyPrime allows you to integrate and exchange data with external systems by defining data sources. In the case of importing data from Excel, various fields are populated based on the data source that is provided. The collection attribute Data Source is used to populate data to a collection dynamically from a variety of data sources. This attribute accepts the Type and Identity of the data source from where the data is to be retrieved. Data Source has been extended to support the feature Import of Excel data. The various ways in which data source have been extended to support Excel Import Capabilities is given below:
Data Source- Excel Sheet Name
A new Collection Data Source, Excel Sheet Name is introduced to populate the list of sheet names within the specified Excel file.
Syntax:
Data Source: Excel Sheet Name: <File Path>
Example:
Data Source: Excel Sheet Name : “D:\AllAccountingMasters.xlsx”
Where will you see the output from this data source on TallyPrime: List of Worksheets table
Data Source- Excel Header Name
Data source to get Excel Header name
Syntax:
Data Source: Excel Header Name: <File Path>
Excel Sheet Info: <Sheet Name> [ : <Start Cell Reference>[ : <End Cell Reference>]]
Example :
Data Source : Excel Header Name: “D:\StockItem.xlsx”
Excel Sheet Info : “Stock Item” : A1 : M1
Here Excel Sheet Info is required so that the system gets to know the sheet name , starting column and ending column from which the Excel Header Name will be populated
Please refer to the below image to check where the start cell reference and end cell references are being provided in TallyPrime
Where will you see the output from this data source on TallyPrime: List of Column Headers table
Data Source- Excel Column Names
Data source to get Excel Column names
Syntax :
Data Source: Excel Column Name: <File Path>
Excel Sheet Info: <Sheet Name> : <Start Column Reference> : <End Column Reference>
Example :
Data Source : Excel Header Name: “D:\StockItem.xlsx”
Excel Sheet Info : “Stock Item” : A : M
Here Excel Sheet Info is required so that the system gets to know the sheet name , starting column and ending column from which the Excel Column Name will be populated
Please refer to the below image to check where the start column reference and end column references are being provided in TallyPrime
Where will you see the output from this data source on TallyPrime: List of Columns table
Data Source- Application Excel Maps & Company Excel Maps
The collections with the above Data Source attribute will return the object “Excel Tally Map” with the methods: MapName, MapFor, IsMasterMap, IsAppMap & FileName
Syntax :
Data Source : Application Excel Maps
Example :
[Collection: Imp AppLevelMap]
Data Source : Application Excel Maps : “”
Format : $Name,25
Format : $MapFor,20
Filter : ImpExlApplicableMaps
Syntax:
Data Source : Company Excel Maps
Example :
[Collection: Imp CompanyLevelMap]
Data Source : Company Excel Maps : “”
Format : $Name,25
Format : $MapFor,20
Filter : ImpExlApplicableMaps
Excel Sheet Info- Attribute
This attribute will be used to specify the starting and ending cell reference when the data source Excel Header Name and Excel Column Names are used.
Syntax :
Excel Sheet Info: <Sheet Name> : <Start Column Reference> : <End Column Reference>
Excel Sheet Info: <Sheet Name> [ : <Start Cell Reference>[ : <End Cell Reference>]]
Objects
New objects have been introduced in Excel Import for persistence and retrieval at various checkpoints.
Object ExcelTallyMap
Syntax:
Object : ExcelTallyMap (EXCEL_TALLY_MAP)
The object ExcelTallyMap stores these values
| RF Name | Method Name | Type | Use |
|---|---|---|---|
| RF_NAME | Name | STR | Used As map name |
| RF_MAP_FOR | MapFor | STR | Supported Master Names, Type of Voucher |
| RF_FILE_NAME | FileName | STR | This info won’t be persisted, But filled while constructing Data source. |
| RF_IS_MASTER_MAP | IsMasterMap | BOOL | To differentiate between master and voucher import |
| RF_IS_APP_MAP | IsAppMap | BOOL | To differentiate storage location |
| RF_HAS_HEADER | HasHeader | BOOL | |
| RF_HEADER_ROW_NUM | HeaderRowNum | LONG | |
| RF_START_HEADER_COLUMN | StartHeaderColumn | STR | This will use to specify starting header cell. It will used to gather Excel Header Collection |
| RF_END_HEADER_COLUMN | EndHeaderColumn | STR | |
| RF_START_DATA_ROW | StartDataRow | LONG | |
| RF_END_DATA_ROW | EndDataRow | LONG | |
| RF_IS_SKIP_ON_SPECIFIED | IsSkipOnSpecified | Bool | |
| RF_SKIP_ON_LIST | SkipOnList | SkipOnColumns(Repeated) | collection of multiple columns skip on (with “And” or “Or” combination) |
| RF_UNIQUE_ON_STR | UniqueOnStr | STR | Every Nth Row, Every Blank Row, These are comma separated Tally Field Names, As User Defined |
| RF_UNIQUE_ON_LIST | UniqueOnList | UniqueOnColumns (Repeated) | When new value – Excel column names |
| RF_UNIQUE_ON_N_ROW | UniqueOnNRow | LONG | |
| RF_FIELD_COLUMN_MAP_LIST | FieldColumnMapList | FieldColumnMap (Repeated) |
Object UniqueOnColumns
Syntax:
Object : UniqueOnColumns (UNIQUE_ON_COLUMNS)
The object UniqueOnColumns stores these values
| RF Name | Method Name | Type | Use |
|---|---|---|---|
| RF_HEADER_NAME | HeaderName | STR | |
| RF_COLUMN_NAME | ColumnName | STR |
Object SkipOnColumns
Syntax:
Object: SkipOnColumns (SKIP_ON_COLUMNS)
The object SkipOnColumns stores these values
| RF Name | Method Name | Type | Use |
|---|---|---|---|
| RF_COMBINE_TYPE | CombineType | STR | “AND” or “OR” |
| RF_HEADER_NAME | HeaderName | STR | |
| RF_COLUMN_NAME | ColumnName | STR | |
| RF_COMPARE_TYPE | CompareType | STR | =, >= , <=…. |
| RF_COLUMN_VALUE | ColumnValue | STR |
Object Compute Value
Syntax:
Object : Compute Value (COMPUTE_VALUE)
The object Compute Value stores these values
| RF Name | Method Name | Type | Use |
|---|---|---|---|
| RF_COMBINE_TYPE | CombineType | STR | It Contains AND/OR (For Future use) |
| RF_HEADER_NAME | HeaderName | STR | |
| RF_COLUMN_NAME | ColumnName | STR | |
| RF_UNIT_HEADER_NAME | UnitHeaderName | STR | TBD Name to be decided… This will be used for Dr/Cr in case of amount.. and other respective units in case of Quantity and rate |
| RF_UNIT_COLUMN_NAME | UnitColumnName | STR | |
| RF_UNIT_COLUMN_FIXED_VALUE | UnitColumnFixedValue | STR | |
| RF_CONDITION | Condition | STR | EqualTo, Not Equal To |
| RF_VALUE | Value | STR |
Object FieldColumnMap
Syntax :
Object: FieldColumnMap (FIELD_COLUMN_MAP)
The object FieldColumnMap stores these values
| RF Name | Method Name | Type | Use |
|---|---|---|---|
| RF_DISPLAY_TEXT | DisplayText | STR | Label of the object displayed in table |
| RF_HEADER_NAME | HeaderName | STR | |
| RF_COLUMN_NAME | ColumnName | STR | |
| RF_IS_MANDATORY | IsMandatory | BOOL | |
| RF_IS_KEY_FIELD | IsKeyField | BOOL | |
| RF_METHOD_NAME | MethodName | STR | Storage Name (Internal). Filled via hidden field. |
| RF_OBJECT_TYPE | ObjectType | STR | |
| RF_DATA_TYPE | DataType | STR | |
| RF_COLLECTION_NAME | CollectionName | STR | |
| RF_PARENT_COLLECTION_NAMES | ParentCollectionNames | STR | |
| RF_INWARD_COLLECTION_NAME | InwardCollectionName | STR | |
| RF_OUTWARD_COLLECTION_NAME | OutwardCollectionName | STR | |
| RF_CREATE_DEFAULT_PARENT_OBJECT | CreateDefaultParentObject | BOOL | |
| RF_IS_QUALIFIER | IsQualifier | BOOL | This will be used on qualifier field. Ex: Dr/Cr, Qty units… |
| RF_IS_INWARD_OUTWARD_TYPE | IsInwardOutwardType | BOOL | This will be used in transfer Type column. |
| RF_IS_UDF | IsUDF | BOOL | |
| RF_FIXED_VALUE | FixedValue | STR | |
| RF_COMPUTE_TYPE | ComputeType | STR | Nett, Concat, Average (Hidden fields…) EqualTo, Not Equal To (Visible to User) |
| RF_COMPUTE_LIST | ComputeList | ComputeValue (Repeated) | Nett, Concat, Average can contain more than one value in list. For Equal to and Not Equal to only first value will be considered. |
| RF_HAS_EXPLODE | <No name> | BOOL | |
| RF_EXPLODE_TYPE | ExplodeType | STR | |
| RF_FIELD_COLUMN_MAP | FieldColumnMap | FieldColumnMap (Repeated) |
Object ImportPreview
Syntax :
Object: ImportPreview
The object ImportPreview stores these values.
|
RF Name |
Method Name |
Type |
Use |
|---|---|---|---|
| RF_FILE_NAME | FileName | String | |
| RF_SHEET_NAME | SheetName | String | |
| RF_HEADER_ROW_NUM | HeaderRowNum | Long | |
| RF_START_HEADER_COL | StartHeaderColumn | String | If Header specified value will be cell names like A1, If No Header specified value will be column Name ‘A’ |
| RF_END_HEADER_COL | EndHeaderColumn | String | If Header specified value will be cell names like A1, If No Header specified value will be column Name ‘A’ |
| RF_START_DATA_ROW | StartDataRow | Long | |
| RF_END_DATA_ROW | EndDataRow | Long | |
| RF_MAP_TYPE | MapType | String | In Case of master Its Ledger, Stock Item… In Case Of Voucher its Accounting Voucher, Inventory Voucher… |
| RF_SKIPPED_ROWS | SkippedRows | LONG | Repeated Field – which will store skipped rows |
| RF_PREVIEW_DETAILS | PreviewDetails | PreviewDetails | Its repeated data, Using which Total No of object created and date range will be displayed in preview. |
These objects will persist values for:-
- Report
- Object Map
- Function
- Events
- XML Requests
These objects will retrieve values when called for :-
- Field Attribute, Table with all the Object methods populated
- Report Part Attribute, Repeat Line over Collection Objects
- Collection Functions, NumItems, CollectionField, FilterValue, CollAmtTotal, FilterAmtTotal
Functions
Functions are a block of code that run only when called. Some new functions have been introduced to support the feature import of data from Excel
Function- IsExcelColumnNameValid
This function takes a string and returns true if the string is a valid Excel column.
Syntax :
$$IsExcelColumnNameValid
Example: Sample Code
Control : InvalidExlCol : NOT $$IsExcelColumnNameValid:$$Value
So in the case below if the user specifies any option other than the List of Columns table, then the function IsExcelColumnNameValid will return false and an Error message is triggered
Function- IsExcelColumnRangeValid
This function takes starting column and an ending column and returns true if starting column is less than the ending columns.
Syntax :
$$IsExcelColumnRangeValid
Example: Sample Code
Control : InvalidRowColRange : NOT $$IsEmpty:$$Value AND NOT + $$IsExcelColumnRangeValid:#IMCStartColumn:$$Value
So in the case below when we enter M before A the function IsExcelColumnRangeValid returns false and hence the Error message is triggered
Function- GlobalAllow, Allow
This function already exists in default tally code but has been extended to control access to create/alter/delete actions on import maps. This function checks if the current user has the right to an operation of the family across all the loaded companies. It will return ‘Yes’ if operation is allowed in at least one company.
Syntax:
$$GlobalAllow
Example: Sample Code
Inactive : NOT ($$GlobalAllow:Display:##DBTileFamilyName:$Name)
So in the below given case Allow function for Creation, alteration and deletion of Mapping template for data entry operators will return false
Function- MakeConfigDataKey
This function will provide the Config Key based on specified parameters
All the Report Views and Excel Maps for This Company are saved in one file i.e CfgRept.1800. If the user goes to Delete a Map saved under This Company, the system would require certain parameters to understand what file is to be deleted.
Syntax:
$$MakeConfigDataKey
Example: Sample Code
ImpExcelMapKey : If ##svIsMstImport Then + $$MakeConfigDataKey:@@ExcelTallyMapSysName:##svImportMappingMstConfig Else + $$MakeConfigDataKey:@@ExcelTallyMapSysName:##svImportMappingVchConfig
Function- IsObjectExists
This function returns a logical value. This function can be used to check if object of given type and name is present in current company.
Syntax:
$$IsobjectExists
Example: Sample Code
[Function: TSPL Create StockItems]
01 : NEW OBJECT : StockItem
02 : SET VALUE : Name : ##StockItem
03 : Do If: NOT ($$IsobjectExists:Unit:##Unit): Call: TSPL Create Units
04 : SET VALUE : Base Units : ##Unit
05 : CREATE TARGET
Actions
Action- Delete All Excel Maps
This action is used to delete all the user-defined Excel maps for the company
Syntax:
Delete All Excel Maps : <Yes/No>:<Company Name>
Example: Default Code
[!Field: IMP DelExlMappedAllTemplates]
Table : Imp AppExcelMaps, DeleteAll
ActOn Table Element : @IsDeleteAll : Delete All Excel Maps : No
ActOn Table Element : NOT @IsDeleteAll : Execute Obj Actions : TableAction
Sysnames
These new Sysnames have been introduced to support the feature Import of data from Excel in TallyPrime
| Sys names | Defined in Platform? | Where will it come? | |
|---|---|---|---|
| 1 | Default Template |
Yes |
Predefined mapping template |
| 2 | Every Nth Row | Yes | Unique On |
| 3 | Every Blank Row | Yes | Unique On |
| 4 | Specify Column Headers | Yes | Mapping |
| 5 | Specify Columns | Yes | Mapping |
| 6 | Compute Value | Yes | Compute Value |
| 7 | Specify Fixed Field Value | Yes | Fixed Field Value |
| 8 | Specify Column | Yes | Specify Column |
| 9 | Top Ledger | Yes | Fixed Top Ledger |
| 10 | Bottom Ledger | Yes | Fixed Bottom Ledger |
| 11 | Fixed Object | Yes | Fixed Object |
| 12 | Invoice Nett Amount | Yes | Nett amount |
| 13 | Application | Yes | Application level map |
| 14 | Master | Yes | master map |
| 15 | AnyVoucher | Yes | Any voucher type |
| 16 | As per Column Header | Yes | In computed value |
| 17 | As per Cell Value | Yes | In computed value |
| 18 | First Column | Yes | To give first column of file |
| 19 | Last Column | Yes | To give last column of file |
| 20 | New Name | Yes | To add a fixed field value |
