Import from Excel

Discussion in 'Tally Integration' started by Partha Mondal, Mar 29, 2021.

    
  1. Partha Mondal

    Partha Mondal Member


    Dear all,
    I am new in TDL. I create a TDL for Import Ledger master from Excel file.
    But here I get some error. When I run my TDL then get this error. But my Excel driver is ok.
    Can anyone help me. I also attached my TDL code.

    ;;;; Shri Ganeshji

    [#Menu : GateWay of Tally]

    Key Item : Stock Item Import : K : Execute : PM Sales Import

    [Report : PM Sales Import] ;; Auto Report

    Form : PM Sales Import
    Title : $$LocaleString:"Stock Item Import"
    Auto : Yes

    [Form: PM Sales Import]

    No Confirm : Yes
    Space Top : 1
    Space Left : 1
    Space Right : 1
    Space Bottom : 1
    Option : Small Size Form
    Full Width : No
    Full Height : No
    Vertical Align : Centre
    Parts : PM Sales Import Title, PM Sales Import Details
    On : Form Accept : Yes : CALL : ImportSalesFunction


    Local : Field : PM SalesImport FileName : Modifies : PM SVExcelPath : Yes
    Local : Field : PM SalesImport FileName : Variable : PM SVExcelPath

    [Part: PM Sales Import Title]

    Lines : PM SalesTrans Imports, PM SalesTrans Imports Info

    [Line: PM SalesTrans Imports]

    Fields : Form Sub Title
    Space Bottom : 0.25
    Local : Field : Form Sub Title : Info : $$LocaleString:"Sales Transaction Import"

    [Line: PM SalesTrans Imports Info]

    Field : Simple Field
    Space Bottom : 0.75
    Local : Field : Simple Field : Info : $$LocaleString:"(Only Tally (XLS) Format is Supported)"
    Local : Field : Simple Field : Full Width : Yes
    Local : Field : Simple Field : Align : Centre

    [Part: PM Sales Import Details]

    Lines : PM SaleImport FileName

    [Line: PM SaleImport FileName]

    Field : Medium Prompt, PM SaleImport FileName
    Local : Field : Medium Prompt : Info : $$LocaleString:"Import File Name (XLS) :"

    [Field: PM SaleImport FileName]

    Use : Name Field
    Width : @@NarrWidth
    Set as : "F:\Ledmaster.xls"
    Full Width : Yes


    [Variable: PM SVExcelPath]

    Type : String
    Default : ""
    Persist : Yes

    [System: Variable]

    PMSVExcelPath : ""

    [System: Formula]

    PMExcelFilePath : ##PMSVExcelPath

    [Collection : PMExcelFileColl]

    ODBC : "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + @@PMExcelFilePath
    SQL : "Select * from [Sheet1$]"

    SQLObject : PM SalesTran Object
    Client Only : Yes

    [Object : PM SalesTran Object]

    PmLedName : $_1
    PmLedParent : $_2
    PmLedOpBal : $_3
    PmLedAdd1 : $_4
    PmLedAdd2 : $_5
    PmState : $_6
    PmPincode : $_7


    [Collection : PMSalesMasterColl]

    Source Collection : PMExcelFileColl
    Fetch : *.*

    compute : PmLedName : $_1
    compute : PmLedParent : $_2
    compute : PmLedOpBal : $_3
    compute : PmLedAdd1 : $_4
    compute : PmLedAdd2 : $_5
    compute : PmState : $_6
    compute : PmPincode : $_7


    [Function: ImportSalesFunction]
    ;; Procedural Block
    Variable : Counter : Number

    ;; Definition Block
    01 : SET FILE LOG ON
    02 : SET : Counter : 0
    03 : START PROGRESS : ($$NumItems:pMSalesMasterColl) : "Sales Trans Import" : @@CmpMailName : "Importing SalesBill..."

    05 : WALK COLLECTION : PMSalesMasterColl

    20 : NEW OBJECT : Ledger

    30 : SET VALUE : Name : $PmLedName
    40 : SET VALUE : Parent : $PmLedParent

    41 : Insert Collection Object : Address
    42 : If:NOT $$isempty:$PmLedAdd1
    43 : SET VALUE : Address : $PmLedAdd1
    44 : END IF
    45 : Set Target:..

    46 : If:NOT $$isempty:$PmLedAdd2
    47 : SET VALUE : Address : $PmLedAdd2
    48 : END IF
    49 : Set Target:..

    80 : Create Target
    90 : Increment : Counter
    100 : Show Progress : ##Counter
    110 : End Walk
    120 : End Progress
    130 : MSGBOX: "Status" :"Sales Transaction Imported \n successfully!!! "
    140 : SET FILE LOG OFF
    150 : RETURN




    Thanks.


    ERROR-1.jpg
     


  2. Himanshu-2002

    Himanshu-2002 Active Member


    Try to hardcode the excel path and check
     


  3. Neeru

    Neeru Active Member


    Hi..

    I Think Driver and Excel bit not match..check Excel (Office ) and Drive are the same bit. (32bit or 64Bit)


     


  4. SN Ramkumar MCA

    SN Ramkumar MCA New Member


    Myself also received the error message when I used Ms Office 2007.
    After using Ms Office 2019 no Error Message.

    Code from yesterday webinar

    S.N.Ramkumar MCA.

    Attached Zip file for your ready reference.


    [#Menu: Gateway Of Tally]

    Add : Item: LedgerImport : Call : LedgerImpFn
    Add : Item: StockItemIMport : Call : StkItemImpFn
    Add : Item: SalesVchImport : Call : SalesVchImpFn

    ;; select $VoucherNumber, $Date, $PartLedgerName, $LedgerName, $PartyAmt from TestSaleVchsVno
    [Function: SalesVchImpFn]

    Variable : VchNo : String
    Variable : vSaleLed : String
    Variable : vTotAmt : Amount
    Variable : vRate : Number
    Variable : vBilledQty: Number

    10: Set : SVViewName : $$SysName:InvVchView

    11 : Log: $$NumItems:TestSaleVchs
    20: Walk Collection : TestSaleVchsVno

    30: Set : VchNo : $VoucherNumber
    40: Set : vSaleLed : $SaleLedger
    41: Set : vTotAmt : $PartyAmt
    42: Log: ##vTotAmt
    50: New Object : Voucher : "ID:10" : Yes


    ; 26: ModifyObject: (Voucher, "Id:10").Narration : "Test123"

    60: Set Value : VoucherTypeName : "Sales"
    70: Set Value : PersistedView : ##SVViewName
    80: Set Value : PartyLedgerName : $PartyLedgerName
    90: Set Value : Date : $Date
    100: Set Value : IsInvoice : Yes

    110: Insert Collection Object : Ledger Entries

    120: Set Value : LedgerName : $PartyLedgerName
    130: Set Value : IsDeemedPositive : Yes
    140: Set Value : IsLastDeemedPositive : Yes
    150: Set value : IsPartyLedger : Yes
    160: Set Value : Amount : $$AsAmount:##vTotAmt *-1

    170: Insert Collection Object : Bill Allocations

    180: Set Value : Name : ##VchNo
    190: Set Value : BillType : $$SysName:NewRef
    200: Set Value : Amount : $$AsAmount:##vTotAmt * -1
    210: Set Target : ...

    220: Walk Collection : TestSaleVchsInv
    221: Set : vRate : $$Number:$Rate
    222: Set : vBilledQty: $$Number:$Quantity

    225: Log : $StockItemName
    223: Log : ##vRate
    224: Log : ##vBilledQty
    226: Log : $InvAmount
    227: If: Not $$IsEmpty:$StockItemName
    230: Insert Collection Object : InventoryEntries

    240: Set Value : StockItemName : $StockItemName

    241: Set Value : IsDeemedPositive : No
    242: Set Value : IsLastDeemedPositive : No

    250: Set Value : BilledQty : $$TgtObject:$$AsQty:##vBilledQty *-1
    260: Set Value : ActualQty : $$TgtObject:$$AsQty:##vBilledQty *-1
    270: Set Value : Rate : $$TgtObject:$$AsRate:##vRate
    280: Set Value : Amount : $$AsAmount:$InvAmount

    290: Insert Collection Object : BatchAllocations

    300: Set Value : GodownName : "Main Location"
    310: Set Value : BatchName : "PrimaryBatch"
    320: Set Value : BilledQty : $$TgtObject:$$AsQty:##vBilledQty *-1
    321: Set Value : ActualQty : $$TgtObject:$$AsQty:##vBilledQty *-1
    330: Set Value : Rate : $$TgtObject:$$AsRate:##vRate
    340: Set Value : Amount : $$AsAmount:$InvAmount
    350: Set Target : ..

    360: Insert Collection Object : AccountingAllocations

    361: Log: ##vSaleLed
    362: Log: $InvAmount

    370: Set Value : LedgerName : ##vSaleLed
    380: Set Value : IsDeemedPositive : No
    390: Set Value : IsLastDeemedPositive : No
    400: Set value : IsPartyLedger : No
    410: Set Value : Amount : $$AsAmount:$InvAmount
    420: Set Target : ...

    421: End If
    430: End Walk

    440: Walk Collection : TestSaleVchsLed

    441: If: Not $$IsEmpty:$LedgerName

    442: Insert Collection Object : Ledger Entries

    450: Set Value : LedgerName : $LedgerName
    460: Set Value : IsDeemedPositive : No
    470: Set Value : IsLastDeemedPositive : No
    480: Set value : IsPartyLedger : No
    490: Set Value : Amount : $$AsAmount:$LedgerAmt
    500: Set Target : ..

    510: End If

    520: End Walk

    530: Accept Alter

    540: End Walk


    [Collection: TestSaleVchs]

    ODBC : "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=D:\TDE\Webinar\27012022\ExcelImport\InvoiceList.xlsx" ;;+ ##SVExcelSourcePath
    SQL : "Select * From [Sheet1$]"
    Fetch : *.*
    SQL Object : TestSqlObj

    [Object: TestSqlObj]

    VoucherNumber : $_1
    Date : $_2
    PartyLedgerName : $_3
    StockItemName : $_4
    Quantity : $_5
    Rate : $_6
    InvAmount : $_7
    SaleLedger : $_8
    LedgerName : $_9
    LedgerAmt : $_10
    TotalAmt : $_11

    ;; select $VoucherNumber, $Date, $PartyLedgerName, $StockItemName, $Quantity, $Rate, $InvAmount, $LedgerName, $LedgerAmt, $TotalAmt from TestSaleVchs





    [Collection: TestSaleVchsVno]

    Source Collection : TestSaleVchs

    By : VoucherNumber : $VoucherNumber

    Compute : PartyLedgerName : $PartyLedgerName
    Compute : Date : $$Date:$Date
    Compute : PartyAmt : $$AsAmount:$TotalAmt
    Compute : SaleLedger : $SaleLedger

    ;; select $VoucherNumber, $PartyLedgerName from TestSaleVchsVno

    [Collection: TestSaleVchsInv]

    Source Collection : TestSaleVchs

    By : VoucherNumber : $VoucherNumber
    By : StockItemName : $StockItemName

    Compute : Quantity : $Quantity
    Compute : Rate : $Rate
    Compute : InvAmount : $InvAmount

    Filter : SelVch

    [System: Formulae]

    SelVch : $VoucherNumber = ##VchNo

    [Collection: TestSaleVchsLed]

    Source Collection : TestSaleVchs

    By : VoucherNumber : $VoucherNumber
    By : LedgerName : $LedgerName

    Compute : LedgerAmt : $$AsAmount:$LedgerAmt

    Filter : SelVch


    [Function: LedgerImpFn]

    01: Walk Collection : LedOdbcCol
    10: New Object : Ledger : $Name : Yes
    20: Set Value : Name : $Name
    30: Set Value : Parent : $Parent
    40: Set Value : OpeningBalance : $OpBalance
    50: Accept Alter
    60: End Walk

    [Collection: LedOdbcCol]

    ODBC : "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=D:\TDE\Webinar\27012022\ExcelImport\LedgerImport\LedgerList.xlsx" ;;+ ##SVExcelSourcePath
    SQL : "Select * From [Sheet1$]"
    Fetch : *.*
    SQL Object : TestLedSqlObj

    [Object: TestLedSqlObj]

    Name : $_1
    Parent : $_2
    OpBalance : $_3

    ;; select $Name, $Parent, $OpBalance from LedOdbcCol


    ;; Stock Item Import


    [Function: StkItemImpFn]

    Variable : vOpBal : Number : 0

    00 : Log: $$NumItems:StkItemOdbcCol
    01: Walk Collection : StkItemOdbcCol
    02: Set : vOpBal : $$Number:$OpBal
    10: New Object : StockItem : $Name : Yes
    20: Set Value : Name : $Name
    30: Set Value : Parent : $Parent
    40: Set Value : BaseUnits : $Units
    41: Set Value : OpeningBalance : $$TgtObject:$$AsQty:##vOpBal
    50: Accept Alter
    60: End Walk

    [Collection: StkItemOdbcCol]

    ODBC : "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=D:\TDE\Webinar\27012022\ExcelImport\ItemList.xlsx" ;;+ ##SVExcelSourcePath
    SQL : "Select * From [Sheet1$]"
    Fetch : *.*
    SQL Object : StkItemSqlObj

    [Object: StkItemSqlObj]

    Name : $_1
    Parent : $_2
    Units : $_3
    OpBal : $_4
     

    Attached Files:



  5. sharat

    sharat New Member


    Sir, Excel part is not showing please guide me Sir...
     


  6. SN Ramkumar MCA

    SN Ramkumar MCA New Member


    Dear Sharat,

    Download the txt file and excel file.


    upload_2022-2-22_21-44-11.png
     

    Attached Files:



  7. ASHOK

    ASHOK Member


    Thanks sir
     


Share This Page