Pandas tally connect without prompt

[admin]

Code: Select all

import pyodbc
import pandas as pd

# =========================
# SETTINGS
# =========================
TALLY_DSN = "TallyODBC64_9000"  # ODBC DSN Name for Tally (adjust as per your ODBC setup)
OUTPUT_FILE = "Trial_Balance.xlsx"

# =========================
# CONNECT TO TALLY
# =========================
try:
    conn = pyodbc.connect(f"DSN={TALLY_DSN}")
    print("✅ Connected to Tally ODBC")
except Exception as e:
    print("❌ Connection failed:", e)
    exit()
#
# =========================
# QUERY TRIAL BALANCE
# =========================
query = """Select $name, $_PrimaryGroup, $Parent, $OpeningBalance, $Debittotals, $CreditTotals, $_ClosingBalance from Ledger"""
# The above is a simple Ledger query — Tally auto-calculates TB when date range is set.

# =========================
# EXECUTE QUERY
# =========================
try:
    df = pd.read_sql(query, conn)
    print(f"✅ Retrieved {len(df)} ledger records")
except Exception as e:
    print("❌ Query failed:", e)
    conn.close()
    exit()

conn.close()

# =========================
# EXPORT TO EXCEL
# =========================
try:
    df.to_csv("Tb.csv")
except Exception as e:
    print("❌ Excel export failed:", e)

[admin]

Code: Select all

import streamlit as st
import pandas as pd
import pyodbc
from io import BytesIO

# =========================
# SETTINGS
# =========================
TALLY_DSN = "TallyODBC64_9000"  # Change as per your DSN

# =========================
# STREAMLIT UI
# =========================
st.set_page_config(page_title="Tally Trial Balance", page_icon="📊", layout="wide")
st.title("📊 Tally Trial Balance Exporter")
st.markdown("Connect to **Tally ODBC**, run the ledger query, preview results, and download Excel/CSV.")

if st.button("🚀 Fetch Trial Balance"):
    try:
        with st.spinner("Connecting to Tally ODBC..."):
            conn = pyodbc.connect(f"DSN={TALLY_DSN}")
        st.success("✅ Connected to Tally ODBC")

        query = """Select $name, $_PrimaryGroup, $Parent, 
                          $OpeningBalance, $Debittotals, $CreditTotals, $_ClosingBalance 
                   from Ledger"""

        with st.spinner("Fetching Trial Balance..."):
            df = pd.read_sql(query, conn)
        conn.close()

        st.success(f"✅ Retrieved {len(df)} ledger records")

        # Show preview with style
        st.subheader("📋 Trial Balance Preview")
        st.dataframe(df.head(20), use_container_width=True)

        # Export options
        buffer = BytesIO()
        with pd.ExcelWriter(buffer, engine="xlsxwriter") as writer:
            df.to_excel(writer, index=False, sheet_name="Trial Balance")
        st.download_button(
            label="⬇️ Download as Excel",
            data=buffer.getvalue(),
            file_name="Trial_Balance.xlsx",
            mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        )

        st.download_button(
            label="⬇️ Download as CSV",
            data=df.to_csv(index=False).encode("utf-8"),
            file_name="Trial_Balance.csv",
            mime="text/csv",
        )

        # Chart for quick visuals
        st.subheader("📈 Ledger Balances Overview")
        try:
            st.bar_chart(df.set_index("$name")[["_ClosingBalance"]].head(20))
        except Exception:
            st.warning("Couldn’t generate chart — data may not be numeric.")

    except Exception as e:
        st.error(f"❌ Error: {e}")