Blog

With this Free Tally Prime TDL code to Import Stock item, we can import from Excel to Tally and also from Text to Tally.

We do business and sales many items. And now Tally is most popular software for business accounting. If you maintain Tally then this post can change your working style.

Sometime there are lot’s of stock items to create in Tally to track stock item records and generate item summary. But sometime doing too many entries in Tally become lengthy process. So today I have something special for your Tally. With this free Tally Prime TDL we can import Stock Items in Tally easily. With this free TDL code we can import stock items into Tally Prime with Item name, Group and unit. It will very helpful to create bulk stock master into Tally. We can use Excel or Text file as our data to import items.

We can modify this TDL code according to our need. We can add some additional fields, we can remove extra fields, and it is very simple to work with Tally Erp9 and Tally Prime. Best simple tdl code to import inventory into Tally Prime.

Also get Invoice wise profit TDL

Tally Prime TDL code to import Stock item :

[#Menu : Gateway of Tally]
Add : Key Item : After : @@LocQuit: Import Stock Items : I : Execute : SMPL Import Config

[Function: SMPL Import Stock Items]

Variable : Row : Number : 1
Variable : Temp Var : String
Variable : Last Status : String
Variable : Counter : Number : 1

Variable : Item Columns
List Variable : item DetailsV, Item Import Status

00 : QUERY BOX : "Import Stock Items" : Yes : No
10 : IF : $$LastResult
20 : IF : ##SMPLSICSource = "Excel"
30 : OPEN FILE: @@SMPLTotFilePath : Excel : READ
40 : ELSE :
50 : OPEN FILE: @@SMPLTotFilePath : Text : READ
60 : ENDIF

70 : SET : ItemColumns.ItemName : @@SMPLItemNameColIdx
80 : SET : ItemColumns.ItemGrp : @@SMPLItemGrpColIdx
90 : SET : ItemColumns.ItemUOM : @@SMPLItemUOMColIdx

100 : IF : ##SMPLSICSource = "Excel"
110 : SET : Row : if ##SMPLSICIncHeader then 2 else 1

;; Reading from Excel File and updating to List
120 : WHILE : NOT $$IsEmpty:($$FileReadCell:##Row:##ItemColumns.ItemName)
130 : LIST ADD EX : item DetailsV
140 : SET : ItemDetailsV[$$LoopIndex].ItemName : $$FileReadCell:##Row:##ItemColumns.ItemName
150 : SET : ItemDetailsV[$$LoopIndex].ItemGrp : $$FileReadCell:##Row:##ItemColumns.ItemGrp
160 : SET : ItemDetailsV[$$LoopIndex].ItemUOM : $$FileReadCell:##Row:##ItemColumns.ItemUOM
170 : INCREMENT : Row
180 : END WHILE

190 : ELSE:

;; Reading from Text File and updating to List
200 : SET : Counter : 1
210 : WHILE : NOT $$FileIsEOF
220 : SET : Temp Var : $$FileRead
230 : IF : NOT $$IsEmpty:##TempVar AND (NOT ##SMPLSICIncHeader OR (##SMPLSICIncHeader AND $$LoopIndex > 1))
240 : LIST ADD EX : item DetailsV
250 : SET : ItemDetailsV[##Counter].ItemName : $$SMPLSICExtractDet:##TempVar:##ItemColumns.ItemName
260 : SET : ItemDetailsV[##Counter].ItemGrp : $$SMPLSICExtractDet:##TempVar:##ItemColumns.ItemGrp
270 : SET : ItemDetailsV[##Counter].ItemUOM : $$SMPLSICExtractDet:##TempVar:##ItemColumns.ItemUOM
280 : INCREMENT : Counter
290 : ENDIF
300 : END WHILE

310 : ENDIF
320 : CLOSE FILE

330 : IF : ##SMPLSICOpenLogFile
340 : OPEN FILE : @@SMPLErrorFilePath : Text : WRITE : ASCII
350 : TRUNCATE FILE
360 : ENDIF

370 : SET : Counter : 1

;; Creating Stock Items from List of Compound Variable
380 : WALK COLLECTION : SMPL Imp StockItem Summ
390 : SET : Last Status : ""

391 : IF : $$IsRemoteCompany
392 : CALL : SMPL Fetch Objects
393 : ENDIF

400 : IF : $$IsEmpty:$Name:StockItem:$SICStockItem
410 : NEW OBJECT : Stock Item
420 : SET VALUE : Name : $SICStockItem

430 : IF : NOT $$IsEmpty:$Name:StockGroup:$SICStockGroup

440 : SET VALUE : Parent : $SICStockGroup
450 : ELSE:
460 : SET : LastStatus: "Group " + $SICStockGroup + " does not exist"
470 : ENDIF

480 : IF : NOT $$IsEmpty:$Symbol:Unit:$SICStockUOM
490 : SET VALUE : Base Units : $SICStockUOM
500 : ELSE:
510 : SET : LastStatus : "Unit " + $SICStockUOM + " does not exist"
520 : ENDIF
530 : IF : $$IsEmpty:##LastStatus
540 : SAVE TARGET
550 : SET : Last Status : "Imported Successfully"
560 : ENDIF
570 : ENDIF
575 : IF : $$IsEmpty:##LastStatus
576 : SET : Last Status : "Item already exists"
577 : ENDIF
;; Writing Import Status to the LOG File if LOG File is to be displayed at the end
580 : IF : ##SMPLSICOpenLogFile
590 : WRITE FILE LINE : $SICStockItem + ##SMPLSICTextSep + ##LastStatus
600 : ENDIF

;; Updating List of Compound Variables is Status is to be displayed in a Report
610 : IF : ##SMPLSICDisplayReport
620 : LIST ADD EX : ItemImportStatus
630 : SET : ItemImportStatus[##Counter].ItemName : $SICStockItem
640 : SET : ItemImportStatus[##Counter].Status : ##LastStatus
650 : INCREMENT : Counter
660 : ENDIF

670 : END WALK
;; 680 : CLOSE TARGET FILE

;; Display Error Report from the List of Compound Variables 'ItemImportStatus'
690 : IF : ##SMPLSICDisplayReport
700 : DISPLAY : SMPL SIC Error Report
710 : ENDIF

;; Open the LOG File
720 : IF : ##SMPLSICOpenLogFile
730 : EXEC COMMAND : @@SMPLErrorFilePath
740 : ENDIF
750 : ENDIF

[Collection: SMPL Imp StockItem] ;; Collection using List of Compound Variable

Data Source : Variable : item DetailsV

[Collection: SMPL Imp StockItem Summ]

Source Collection : SMPLImpStockItem
By : SICStockItem : $ItemName
By : SICStockGroup : $ItemGrp
By : SICStockUOM : $ItemUOM
Filter : SMPL NonEmpty Item
Client Only : Yes

[System: Formula]

SMPL NonEmpty Item : NOT $$IsEmpty:$SICStockItem

SMPL Folder Path : if $$SMPLEndsWithSlash:##SMPLSICDirPath then ##SMPLSICDirPath else ##SMPLSICDirPath + "\"
SMPL File Name : ##SMPLSICFilePath
SMPL File Ext : if $$SMPLSymbolExistsinFileName:##SMPLSICFileExt then ##SMPLSICFileExt else "." + ##SMPLSICFileExt

SMPL TotFilePath : @@SMPLFolderPath + @@SMPLFileName + @@SMPLFileExt

SMPL ItemNameColIdx : if $$Number:##SMPLSICItemName = 0 then $$FileGetColumnIdx:##SMPLSICItemName else ##SMPLSICItemName
SMPL ItemGrpColIdx : if $$Number:##SMPLSICItemGroup = 0 then $$FileGetColumnIdx:##SMPLSICItemGroup else ##SMPLSICItemGroup
SMPL ItemUOMColIdx : if $$Number:##SMPLSICItemUOM = 0 then $$FileGetColumnIdx:##SMPLSICItemUOM else ##SMPLSICItemUOM

SMPL Error File Path: @@SMPLFolderPath + @@SMPLFileName + ".LOG"

;; Functions

[Function: SMPL Fetch Objects]

Fetch Object: Stock Item : $SICStockItem : $Name
Fetch Object: Stock Group : $SICStockGroup: $Name
Fetch Object: Unit : $SICStockUOM : $Symbol

[Function: SMPL SIC Extract Det]

Parameter : pValue : String
Parameter : pIndex : Number

Variable : Counter : Number : 1

00 : FOR TOKEN : TokenVar : ##pValue : ##SMPLSICTextSep
10 : IF : ##Counter = ##pIndex
20 : RETURN : ##TokenVar
30 : ENDIF
40 : INCREMENT : Counter
50 : END FOR

;; Compound Variable Declaration

[Variable: Item Columns]

Variable : Item Name, Item Grp, Item UOM : Number

[Variable: item DetailsV]

Variable : Item Name, Item Grp, Item UOM : String

[Variable: Item Import Status]

Variable : Item Name, Status : String

;; Import Configuration

[Report: SMPL Import Config] ;; Report Definition invoking Import of Stock Items

Form : SMPL Import Config
Title : $$LocaleString:"Import Configuration"

[Form: SMPL Import Config]

Parts : SMPL Import Config Driver Info, SMPL Import Config Column Info, SMPL Import Config Other Info
Full Width : No
Space Right : 2
Space Left : 2
No confirm : Yes
Option : Small Size Form
Background : @@SV_CMPCONFIG

On: Form Accept : Yes : Form Accept
On: Form Accept : Yes : Call : SMPL Import Stock Items

[Part: SMPL Import Config Driver Info]

Lines : Form SubTitle, SMPL SIC Source, SMPL SIC DirInfo, SMPL SIC FileInfo, SMPL SIC FileExtension, SMPL SIC SheetInfo
Local: Field: Form SubTitle : Info : $$LocaleString:"File Information"
Local: Line : Form SubTitle : Space Top : 1
Local: Field: Name Field : Width : 25% Page

[Line: SMPL SIC Source]

Fields : Medium Prompt, Name Field
Local: Field: Medium Prompt : Set As : "Import Source :"
Local: Field: Name Field : Modifies : SMPL SIC Source : Yes
Local: Field: Name Field : Variable : SMPL SIC Source
Local: Field: Name Field : Table : SMPL SIC Source
Local: Field: Name Field : Show Table: Always

[Table: SMPL SIC Source]

List Name : "Text", "Excel"
Title : "Source"

[Line: SMPL SIC DirInfo]

Fields : Medium Prompt, Name Field
Local: Field: Medium Prompt : Set As : "Location of File:"
Local: Field: Name Field : Modifies : SMPL SIC DirPath : Yes
Local: Field: Name Field : Variable : SMPL SIC DirPath
Local: Field: Name Field : Max : 120

[Line: SMPL SIC FileInfo]

Use : SMPL SIC DirInfo
Local: Field: Medium Prompt : Set As : "File Name:"
Local: Field: Medium Prompt : Indent : 1
Local: Field: Name Field : Modifies : SMPL SIC FilePath : Yes
Local: Field: Name Field : Variable : SMPL SIC FilePath
Local: Field: Name Field : Control : SMPL NoExt: $$SMPLSymbolExistsinFileName:$$Value

[Line: SMPL SIC FileExtension]

Use : SMPL SIC DirInfo
Local: Field: Medium Prompt : Set As : "File Extension:"
Local: Field: Medium Prompt : Indent : 1
Local: Field: Name Field : Modifies : SMPL SIC FileExt : Yes
Local: Field: Name Field : Variable : SMPL SIC FileExt
Local: Field: Name Field : Set As : if (##SMPLSICSource = "Excel" AND NOT ##SMPLSICFileExt CONTAINS "xls") then "xls" else if (##SMPLSICSource = "Text" AND ##SMPLSICFileExt CONTAINS "xls") then "txt" else ##SMPLSICFileExt
Local: Field: Name Field : Set Always: Yes
Local: Field: Name Field : Case : Normal

[Line: SMPL SIC SheetInfo]

Use : SMPL SIC DirInfo

Local: Field: Default : Inactive : NOT ##SMPLSICSource = "Excel"

Local: Field: Medium Prompt : Set As : "Sheet Name (Excel):"
Local: Field: Name Field : Set As : if $$IsEmpty:$$Value then "Stock Items" else ##SMPLSICTableName
Local: Field: Name Field : Modifies : SMPL SIC TableName : Yes
Local: Field: Name Field : Variable : SMPL SIC TableName

[Part: SMPL Import Config Column Info]

Lines : Form SubTitle, SMPL SIC Inc Header, SMPL SIC Item Name, SMPL SIC Item Group, SMPL SIC Item UOM, SMPL SIC Text Sep
Local: Field: Form SubTitle : Info : $$LocaleString:"Column for the following (A, B, C or 1, 2, 3)"
Local: Line : Form SubTitle : Space Top : 1
Local: Field: Name Field : Set Always: Yes

[Line: SMPL SIC Inc Header]

Use : SMPL SIC DirInfo
Local: Field: Medium Prompt : Set As : "Includes Header Info :"
Local: Field: Name Field : Modifies : SMPL SIC IncHeader : Yes
Local: Field: Name Field : Variable : SMPL SIC IncHeader
Local: Field: Name Field : Width : @@LogicalWidth
Local: Field: Name Field : Table : YesNoTable

[Line: SMPL SIC Item Name]

Use : SMPL SIC DirInfo
Local: Field: Medium Prompt : Set As : "Stock Item Name :"
Local: Field: Name Field : Modifies : SMPL SIC ItemName : Yes
Local: Field: Name Field : Variable : SMPL SIC ItemName
Local: Field: Name Field : Set As : if ##SMPLSICSource = "Text" AND $$Number:##SMPLSICItemName = 0 then "1" else ##SMPLSICItemName

Option : SMPL SIC Item Name Max

[!Line: SMPL SIC Item Name Max]

Local: Field: Name Field : Maximum : 1
Local: Field: Name Field : Width : 2

[Line: SMPL SIC Item Group]

Use : SMPL SIC Item Name
Local: Field: Medium Prompt : Set As : "Under Group :"
Local: Field: Name Field : Modifies : SMPL SIC ItemGroup : Yes
Local: Field: Name Field : Variable : SMPL SIC ItemGroup
Local: Field: Name Field : Set As : if ##SMPLSICSource = "Text" AND $$Number:##SMPLSICItemGroup = 0 then "2" else ##SMPLSICItemGroup
Local: Field: Name Field : Control : Duplicate Entry : $$Value = ##SMPLSICItemName

Option : SMPL SIC Item Name Max

[Line: SMPL SIC Item UOM]

Use : SMPL SIC Item Name
Local: Field: Medium Prompt : Set As : "Units of Measure :"
Local: Field: Name Field : Modifies : SMPL SIC ItemUOM : Yes
Local: Field: Name Field : Variable : SMPL SIC ItemUOM
Local: Field: Name Field : Set As : if ##SMPLSICSource = "Text" AND $$Number:##SMPLSICItemUOM = 0 then "3" else ##SMPLSICItemUOM
Local: Field: Name Field : Control : Duplicate Entry : $$Value = ##SMPLSICItemName OR $$Value = ##SMPLSICItemGroup

Option : SMPL SIC Item Name Max

[Line: SMPL SIC Text Sep]

Use : SMPL SIC Item Name

Local: Field: Default : Inactive : NOT ##SMPLSICSource = "Text"

Local: Field: Medium Prompt : Set As : "Text Separator Character :"
Local: Field: Name Field : Modifies : SMPL SIC TextSep : Yes
Local: Field: Name Field : Variable : SMPL SIC TextSep
Local: Field: Name Field : Set As : if ##SMPLSICSource = "Text" then ##SMPLSICTextSep else ""

[Part: SMPL Import Config Other Info]

Lines : Form SubTitle, SMPL SIC Display Report, SMPL SIC Open Log File
Local: Field: Form SubTitle : Info : $$LocaleString:"Other Information"
Local: Line : Form SubTitle : Space Top : 1

[Line: SMPL SIC Display Report]

Use : SMPL SIC Dir Info
Local: Field: Medium Prompt : Set As : "Display Error Report, if any :"
Local: Field: Name Field : Modifies : SMPL SIC DisplayReport : Yes
Local: Field: Name Field : Variable : SMPL SIC DisplayReport
Local: Field: Name Field : Table : YesNoTable
Local: Field: Name Field : Width : @@LogicalWidth

[Line: SMPL SIC Open Log File]

Use : SMPL SIC Display Report
Local: Field: Medium Prompt : Set As : "Open Log File Error, if any :"
Local: Field: Name Field : Modifies : SMPL SIC Open Log File : Yes
Local: Field: Name Field : Variable : SMPL SIC Open Log File
Local: Field: Name Field : Table : YesNoTable
Local: Field: Name Field : Width : @@LogicalWidth

;; Function Definition

[Function: SMPL SymbolExistsinFileName]

Parameter : pValue : String
Parameter : pSymbol : String : "."

Variable : TempVar : String

00 : FOR RANGE : IteratorVar : Number : ($$StringLength:##pValue) : 0 : 1
10 : SET : TempVar : $$StringPart:##pValue:##IteratorVar:1
20 : IF : $$ExactMatch:##TempVar:##pSymbol
30 : RETURN : True
40 : ENDIF
50 : END FOR

[Function: SMPL EndsWithSlash]

Parameter : pValue : String

Variable : TempNo : Number
Variable : TempVar : String

00 : SET : TempNo : $$StringLength:##pValue - 1
10 : SET : TempVar : $$StringPart:##pValue:##TempNo:1
20 : IF : ##TempVar = "\"
30 : RETURN : True
40 : ELSE :
50 : RETURN : False
60 : ENDIF

;; Variable and Formulae

[System: Formula]

SMPL NoExt : "Please do not enter \n File Extension"

[System: Variable]

SMPL SICSource : "Excel"
SMPL SICDirPath : "C:\Tally.ERP9\FileIOData"
SMPL SICFilePath : "ListofStockItems"
SMPL SICFileExt : ".xls"
SMPL SICTableName : "Stock Items"

SMPL SICIncHeader : Yes
SMPL SICItemName : "A"
SMPL SICItemGroup : "B"
SMPL SICItemUOM : "C"
SMPL SICTextSep : ","

SMPL SICDisplayReport : Yes
SMPL SICOpen Log File : No

[Variable: SMPL SIC Source]

Type : String
Persistent : Yes

[Variable: SMPL SIC DirPath]

Type : String
Persistent : Yes

[Variable: SMPL SIC FilePath]

Type : String
Persistent : Yes

[Variable: SMPL SIC FileExt]

Type : String
Persistent : Yes

[Variable: SMPL SIC TableName]

Type : String
Persistent : Yes


[Variable: SMPL SIC IncHeader]

Type : String
Persistent : Yes

[Variable: SMPL SIC ItemName]

Type : String
Persistent : Yes

[Variable: SMPL SIC ItemGroup]

Type : String
Persistent : Yes

[Variable: SMPL SIC ItemUOM]

Type : String
Persistent : Yes

[Variable: SMPL SIC TextSep]

Type : String
Persistent : Yes


[Variable: SMPL SIC DisplayReport]

Type : Logical
Persistent : Yes

[Variable: SMPL SIC OpenLogFile]

Type : Logical
Persistent : Yes

;; Import Error Report

[Report: SMPL SIC Error Report]

Form : SMPL SIC Error Report
Title : "Import Status"

[Form: SMPL SIC Error Report]

Parts : Form Subtitle, SMPL SIC Error Report
Local : Field : Form SubTitle : Info : "Status of Stock Item import"

[Part: SMPL SIC Error Report]

Lines : SMPL SIC Error Title, SMPL SIC Error Details
Repeat : SMPL SIC Error Details : SMPL SIC LOGVarColl Summ
Scroll : Vertical
Common Border : Yes

[Line: SMPL SIC Error Title]

Use : SMPL SIC Error Details
Local: Field: Default : Style : Normal Bold
Local: Field: Default : Align : Centre

Local: Field: SMPL SIC Error Item : Set As : "Particulars"
Local: Field: SMPL SIC Error Status : Set As : "Status"
Border : Thin Top Bottom

[Line: SMPL SIC Error Details]

Fields : SMPL SIC Error Item
Right Fields: SMPL SIC Error Status
Option : SMPL SIC Success : $SICStatus CONTAINS "Imported Successfully" OR $SICStatus CONTAINS "Item already exists"

[!Line: SMPL SIC Success]

Local : Field : Default : Style : Normal Italic

[Field: SMPL SIC Error Item]

Use : Name Field
Set As : $SICStockItem
FullWidth : Yes

[Field: SMPL SIC Error Status]

Use : Name Field
Set As : $SICStatus
Border : Thin Left
Width : 50% Page

[Collection: SMPL SIC LOGVarColl]

Data Source : Variable : Item Import Status

[Collection: SMPL SIC LOGVarColl Summ]

Source Collection : SMPL SIC LOGVarColl
By : SICStockItem : $ItemName
By : SICStatus : $Status
Keep Source : ().
Filter : SMPL NonEmpty Item
Client Only : Yes

How to use Stock item import in Tally Prime TDL code:

HOW TO USE TDL CODE:

• Copy above TDL code and make a text file.
• Save text file then load in Tally Prime (Method provided below the code)
• In Main screen (Gateway of Tally) you will find a tab in the name of Item Import.
• With this item import Tab we can import our stock item list in Tally.
• Instructions are given in Tally with this TDL Tab

This is best free TDL to have in your Tally . Must have TDL file for Tally ERP9 and Tally Prime that can use for lifetime. This free TDL Code will perfectly work with Tally Prime.

How to use Item Import TDL video:

For More videos logon to Youtube/Learnwell

Watch above video to learn :

• How Create excel sheet to import items.
• How select file and load in tally to import stock items.

Import stock items and ledgers in Tally Prime and Tally ERP9 with full details

FOLLOW THE BELLOW INSTRUCTIONS TO LOAD TDL FILE IN TALLY:

1. Make a Text file of above code
2. Save it in to your computer
3. Copy the text file path including Name and extension (as – C:\Users\HP\Desktop\rec\Item Import.txt)
4. Open Tally Prime Software
5. Click on Help and click on TDL & Addon  (for Tally Prime)
6. Press F4 (to open a Box)
7. Set Load TDL file on Startup – Yes
8. Paste The path in blank space
9. Press enter and save
10. Now your Tally Prime TDL code to import Stock item is ready to use