Basic Integration of Tally with Python

[admin]

Code.zip

This code demonstrates the basic POC of Tally Tables converted as data frames in pandas then transformed to Excel Table


Link to Basic Query

https://drive.google.com/open?id=1-vYCQ ... p=drive_fs


Link to TDL

https://drive.google.com/open?id=11FJAt ... p=drive_fs

Code: Select all

SELECT $Key, $MasterId, $AlterID, $VoucherNumber, $Date, $VoucherTypeName, $VchTypeParent, $Led_Lineno, $Type, $Particulars, $Amount, $Debit, $Credit, $Led_Parent, $Led_Group, $Party_LedName, $Vch_GSTIN, $Led_GSTIN, $GSTIN_Check, $Led_PAN, $Party_GST_Type, $GST_Classification, $Narration, $Vendor_Inv_Number, $Vendor_Inv_Date, $EnteredBy, $AlteredBy, $AlteredOn, $UpdatedDate, $UpdatedTime, $Nature_Led, $Is_Debit, $HasBankEntry, $CashBank_Credited, $CashBank_Debited, $HasCashFlow, $IsCashBankAcct, $AffectsInventory, $Led_MID, $CompanyName, $Year_from, $Year_to, $Company_number, $Path FROM A__T_Vch10

Python code

Code: Select all

import pyodbc
import warnings

warnings.filterwarnings('ignore')
pyodbc.drivers()
import pandas as pd

server = 'localhost,9400'
#database = 'database_name' # enter database name
cnxn = pyodbc.connect('DRIVER={Tally ODBC Driver64};SERVER='+server+';Trusted_Connection=yes;')
cursor = cnxn.cursor()

import time
start_time = time.time()
query2 = '''SELECT $Name from Ledger'''
df1 = pd.read_sql(query2, cnxn)
print(df1)
query1 = '''SELECT $Key, $MasterId, $AlterID, $VoucherNumber, $Date, $VoucherTypeName, $VchTypeParent, $Led_Lineno, $Type, $Particulars, $Amount, $Debit, $Credit, $Led_Parent, $Led_Group, $Party_LedName, $Vch_GSTIN, $Led_GSTIN, $GSTIN_Check, $Led_PAN, $Party_GST_Type, $GST_Classification, $Narration, $Vendor_Inv_Number, $Vendor_Inv_Date, $EnteredBy, $AlteredBy, $AlteredOn, $UpdatedDate, $UpdatedTime, $Nature_Led, $Is_Debit, $HasBankEntry, $CashBank_Credited, $CashBank_Debited, $HasCashFlow, $IsCashBankAcct, $AffectsInventory, $Led_MID, $CompanyName, $Year_from, $Year_to, $Company_number, $Path
FROM A__T_Vch10'''
df2 = pd.read_sql(query1, cnxn)

print(df2)
print("--- %s seconds for reading---" % (time.time() - start_time))

start_time2 = time.time()

  #  df2.to_excel(engine='xlsxwriter', sheet_name = 2)
    
print("--- %s seconds for writing ---" % (time.time() - start_time2))    
df1.to_csv('Ledger Master.csv')
df2.to_csv('Transactions.csv')

[kppaniraj]

Sir

Could you please post the steps to be followed for integrating python with tally and import the data into CSV or excel

Regards