How to import all types of voucher from excel using tdl

I want to import all types of voucher like sales, purchase, receipt, cash, receipt, journal like all.
how can i do this please help someone.

here is my code, i tried but not work

Code:

[System: Formulae]
    RcptPathxl    : #receiptvoucher
    RcptSeetxl    : #sheetnam
  
  
[#Menu:Gateway of Tally]
    Add:key Item: At Beginning : Import Receipt : H : Execute : receiptvoucher
[Report: receiptvoucher]
    Auto    : Yes
    Form    : receiptvoucher
[Form: receiptvoucher]
    Vertical Alignment     : Center
    Horizontal Alignment : Center
    No Confirm             : Yes
    Option                 : Small Size Form
    Full Height             : No
    Full Width             : No
    Space Bottom         : 1
    Space Left             : 1
    Space Right             : 1
    Space Top             : 1
    Background             : lightblue
    Local                 : Field            : receiptvoucher : Modifies : receiptvoucher : Yes
    Local                 : Field            : receiptvoucher : Variable : receiptvoucher
    On                     : Form Accept      : Yes              : Call     : Function Create
    Part                 : receiptvoucher
[Part: receiptvoucher]
    Line        : receiptvoucherpath,receptsheetname
[Line: receiptvoucherpath]
    Fields    : Medium Prompt, receiptvoucher
    Local    : Field    : Medium Prompt   : Set as : $$LocaleString:"Full path name of excel with extensions :"
    Local    : Field    : Medium Prompt   : Style  : Normal Bold
    Local    : Field    : Medium Prompt   : Width  : 35
    Local    : Field    : Medium Prompt   : Color  : Black
    Local    : Field    : receiptvoucher : Set as : "Insert excel file path here"
    Local    : Field    : receiptvoucher : Width  : 45
    Local    : Field    : receiptvoucher : Style  : Normal Bold
[Field: receiptvoucher]
[Line:receptsheetname]
    Fields    : Medium Prompt, sheetnam
    Local    : Field    : Medium Prompt      : Set as : $$LocaleString:"Enter Sheet Name"
    Local    : Field    : Medium Prompt      : Style  : Normal Bold
    Local    : Field    : Medium Prompt      : Width  : 35
    Local    : Field    : Medium Prompt       : Color  : Black
[Field: sheetnam]
    Use:name field  
    Set as: "Enter sheet name to be Import"
[Function: Function Create]
    Variable : ProgressCount: Number
    Variable : Counter        : Number
    Variable : amountis        : Number
    Variable: receipnum     : Number
    01 : Query Box  : "Import receipt voucher ?" : Yes : No
    02 : Set        : Counter          : 0
    03 : Set        : ProgressCount      : 1
    04 : If    : Not ($$NumItems:TestColl > 0)
    05 :  Return
    06 : End If
    07 : Start Progress    : ($$NumItems:TestColl) : "Importing" : "Receipt Voucher"
    08 : If    : $$LastResult
    09    : Walk Collection    : TestColl
    10    : NEW OBJECT : VOUCHER
    12    : SET : SVVoucherType : 'Receipt'
    12a : Set : amountis      : $receiptamoun
    13a : Set: receipnum : $receipnumber
    13    : SET VALUE    : Date                        : $$Date:$Receiptdate
    14    : SET VALUE    : VoucherTypeName            : 'Receipt' ;$$VchTypeReceipt
    ;aa : Set Value      : VOUCHERNUMBER : $receipnumber
    15  : Set Value        : NARRATION                : $narration
  
    16 : INSERT COLLECTION OBJECT                : AllLedgerEntries
    17 : Set Value        : LedgerName            : $crledname
    18 : Set Value        : AMOUNT                : $$AsAmount:##amountis
    19 : Set Value        : CURRPARTYLEDGERNAME   : $crledname
    aa : Set Value      : VOUCHERNUMBER : ##receipnum
    20 : INSERT COLLECTION OBJECT                : BillAllocations
    21 : Set Value        : BILLTYPE                : "On Account"
    22 : Set Value        : AMOUNT                : $$AsAmount:##amountis
    bb : Set Value      : VOUCHERNUMBER : ##receipnum
    23 : INSERT COLLECTION OBJECT                : AllLedgerEntries
    24 : Set Value        : LedgerName            : $drledname
    25 : Set Value        : AMOUNT                :  -1 * ($$AsAmount:##amountis)
    26 : Set Value        : CURRPARTYLEDGERNAME   : $drledname
    cc : Set Value      : VOUCHERNUMBER : ##receipnum
    27 : Set Target        :..
    1510:    Log Target
    ;1520:     CREATE TARGET
    28 : SET VALUE        : Persisted View: ##SVViewName
    ;29 : Accept Alter
    30 : Show Progress    : ##ProgressCount
    31 : Set            : ProgressCount    : ##ProgressCount + 1
    32 : End Walk
    33 : Increment         : Counter
    34 : MSG BOX         : "Info" : "Receipt Voucher Imported"
    35 : End If
    36 : Return
[Collection: excelledgercollection]
    ODBC        : "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};+
    DBQ=" + @@RcptPathxl + ""
    SQL            : "Select * from [" + @@RcptSeetxl + "$]"
[Collection:TestColl]
    Source Collection: excelledgercollection
    ;Compute: dt            : $_6
    Compute: crledname      : $_3
    Compute: drledname     : $_4
    Compute: Receiptdate : $_1  ; this is date column
    Compute: receiptamoun: $_5  ;this is amount column
    Compute: receipnumber: $_0
    ;Compute: narration      : $_17 
      
;select $receipnumber, $receiptamoun, $Receiptdate from TestColl
;; End-of-File

 

Get VoucherType from Excel,
Set SVVoucherType depending on data from Excel

 

i have changed code but it only import receipt not other

 

For payment you need to credit cash but you are debiting cash

 

1) but error is function create label: 29 - type of voucher not specified. (for payment)
2) and for purchase , sales voucher

 

Use msgbox to view value in $vouchrtyp before creating

 

and for sales and purchase voucher

 

Same process by changing vouchertype you can create any voucher
You need to debit and credit correct ledgers

 

but sales and purchase voucher have more field , is it import only with only debit credit ledger and amount, how to get all insert collection object for sales and purchase voucher

 

Did you written the code yourself, or bought from somewhere?

 

yes i written, but sometime facing problem with insert collection object

 

Then you will know how to add extra columns in excel and add those to your TDL collection

 

ok but which insert collection object should use like ledgerentries, billallocations

 

You need to push ledgers to
AllLedgerEntries
You can achieve this dynamically by wrapping
'INSERT COLLECTION OBJECT : AllLedgerEntries' in For loop

I don't known much TDL ,So I cannot help you with code, I can only tell logic

Note: If you want Ledger to be debited then corresponding amount should be negative

 

i changed payment and journal but error is voucher total do not match.

 

Errors says your voucher total should be zero
For ex: for payment
Party a/c 4000
To cash a/c. 4000
Then excel it should be - party -4000 ,cash 4000
If we total all amounts it will be zero

 

please check my excel file what should i change for payment and journal for this error

 

Excel Data seems ok ,
but are you converting Debit ledger Amount to negative in tdl?

 

but this negative amount works in receipt and contra , how can i put condition if payment or journal then positive

 

For Receipt we will Debit Cash but For payment we will credit cash that is the reason it is not working
instead of converting to negative in tdl you take both credit and debit amount from excel only
when entering amount should be negative for debit ledger

 

i tried as you told but still voucher total mismatch error.

 

post your code and excel

 

25 : Set Value : AMOUNT : -1 * ($$AsAmount:##amountis)
Reeplace 25 : Set Value : AMOUNT : ($$AsAmount:##amountis)

Also You didnt added the colum I added in Excel I shared previously

 

is it really work on your side, i have changed like that you told, but due to no output , i again revert to original code.
can you check complete code