Code: /* Objective(s) - - This code demonstrates the usage of Procedural File IO - This code imports the Stock Item Data i.e., Name, Group and Units from either Excel or Text using File IO capability and subsequently displays an error report as well as opens a logged file on request Capabilities Used - - List/Compound Variables - User Defined Functions - Collections with Variables as its Data Source - File IO (Read/Write) Capability Dependencies - - Data files 'Text' &/or 'Excel' */ [#Menu : Gateway of Tally] Add : Key Item : After : @@LocDisplay: 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 Details, 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 Details 140 : SET : ItemDetails[$$LoopIndex].ItemName : $$FileReadCell:##Row:##ItemColumns.ItemName 150 : SET : ItemDetails[$$LoopIndex].ItemGrp : $$FileReadCell:##Row:##ItemColumns.ItemGrp 160 : SET : ItemDetails[$$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 Details 250 : SET : ItemDetails[##Counter].ItemName : $$SMPLSICExtractDet:##TempVar:##ItemColumns.ItemName 260 : SET : ItemDetails[##Counter].ItemGrp : $$SMPLSICExtractDet:##TempVar:##ItemColumns.ItemGrp 270 : SET : ItemDetails[##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 Details [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 Details] 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 ;; End-of-File
Dear sir, we need to import Std rate from excel. how it is possiple. al other fields are import correctly. but std rate only not import.
Dear Sir, If I want to fetch alias,Opening Quntity and Part no also along with Name ,Group and UOM then what changes shoul I Make in the same code.Please help
HI this is import stockitem from excel to Tally all fields in stockItem... also create a templates also i think it's so useful... we can import stockitem totals details from excel to tally.... using this TDL
Can We update Stock item Name of particular stock item by Part Number (which is already entered for that stock item).
Thank you Mr. Narasimhulu Devalam i try to this tdl but i am not get any import. how to do this tdl kindly help me i attached main.tdl in my tally and in tdl file i changed xls path also but i am not getting anything (rel 4.9)
[#Menu: Gatewayoftally] Add:Itemasswordisplay:TallyPass [Report:Tallypass] Form:Tallypass Object:COmpany [Form:Tallypass] Part:Tallypass Height:40% page Width:30% page Background:Black [Part:Tallypass] Line:Tallypass_Title,Admin_Details,User_Details Repeat:User_Details:CmpUser List Scroll:Vertical Float:Yes [Line:Tallypass_Title] Field:U_Name,U_pass Border:Totals Space Bottom:2 [Field:U_name] Use:Name Field Set as: "USER NAME " Align:Center Color:Red [Field:U_Pass] Use:Name Field Set as:"PASSWORD " Align:Center Color:Red [Line:Admin_Details] Field:Admin_Name,Admin_Pwd [Field:Admin_Name] Use:Name Field Set as:$$cmpusername Color:Green [Field:Admin_Pwd] Use:Name Field Set as:$OwnerPassword:Company:##SVCurrentCompany Color:Green Align:Right [Line:User_Details] Field:V_Username,V_password [Field:V_UserName] Use:Name Field Set as:$Name Color:White [Field:V_Password] Use:Name Field Set as:$password Color:White Align:Right [#Collection:Cmp User list] Fetch:*.* Is ODBC Table:Yes
Hello sir, It is possible to display password without entering into the company, And can you tell me how to get lost tally vault password
how can we find vault password? and normal security admin username and password without entering into company
Dear All the tdl maste,, i want to import simple excel to tally,( master) , please sir give me a excel sheet with source code ..
