C# code to import Ledgers from excel to Tally

Discussion in 'Tally Integration' started by teja varma, Sep 20, 2019.

    
  1. teja varma

    teja varma Active Member


    ->this is the sample code to import ledgers from excel to tally
    -> while importing Tally should be running with a company opened
    ->make sure tally is acting as server on port 9000
    ->Use the below attached Excel sheet for import

    Code:
    using System;
    using System.Collections.Generic;
    using System.Data.OleDb;
    using System.IO;
    using System.Linq;
    using System.Net;
    using System.Net.Cache;
    using System.Text;
    using System.Xml.Linq;
    
    namespace Excel_to_Tally
    {
        
        public class ExcelToTally
        {
          
            List<ledadapter> ledholder = new List<ledadapter>(); // to store ledgers
            StringBuilder Req_builder = new StringBuilder(); // to build xml request
            XDocument xdoc; // to construct xml String
            String Response;  // to hold response from tally
    
            public void read_excel()
            {
                try{
    
                    String File_path = @"C:\Users\lenovo\Documents\Ledmaster.xlsx";         // file path of the excel file
                    String Extension = System.IO.Path.GetExtension(File_path);
                    String Xls_conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + File_path + ";Extended Properties='Excel 8.0;HDR=YES;'";  // oled driver for xls
                    String Xlsx_conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + File_path + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'"; // oled driver for xlsx
                    using (OleDbConnection conn = new OleDbConnection())
                    {
                        conn.ConnectionString = Extension.CompareTo(".xlsx") == 0 ? Xlsx_conn : Xls_conn;
                        conn.Open();
                        OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", conn);
                        ledholder.Clear();
                        OleDbDataReader reader = cmd.ExecuteReader();
                        // reading values in to list
                        while (reader.Read())
                        {
                            ledholder.Add(new ledadapter { ledname = reader.GetValue(0).ToString(), ledparent = reader.GetValue(1).ToString(), ledbillwise = reader.GetValue(2).ToString(), ledadd1 = reader.GetValue(3).ToString(), ledadd2 =reader.GetValue(4).ToString(),
                                           ledadd3 =reader.GetValue(5).ToString(),ledcountry = reader.GetValue(6).ToString(),ledstate = reader.GetValue(7).ToString(),
                                           ledregtype = reader.GetValue(8).ToString(),ledgstin = reader.GetValue(9).ToString()
                            });
                        }
                    }
                    Req_builder.Clear();
    
                    // init http request
    
                    Create_led();
                }
                catch(Exception ex)
                {
                    Console.Write(ex.Message);
                }
            }
    
    
            private void Create_led()
            {
                try
                {
                    // building xml string
                    Req_builder.Append("<ENVELOPE><HEADER><TALLYREQUEST>Import Data</TALLYREQUEST></HEADER><BODY><IMPORTDATA><REQUESTDESC><REPORTNAME>All Masters</REPORTNAME></REQUESTDESC><REQUESTDATA><TALLYMESSAGE xmlns:UDF ='TallyUDF'>");
                    if (ledholder.Count() != 0)
                    {
                        foreach (ledadapter led in ledholder)
                        {
                            // appending ledger data
                            xdoc = new XDocument(new XElement("LEDGER", new XAttribute("Action", "Create"),
                                                           new XElement("NAME", led.ledname), new XElement("PARENT", led.ledparent),
                                                           new XElement("ISBILLWISEON", led.ledbillwise), new XElement("ADDRESS.LIST", new XAttribute("TYPE", "String"), new XElement("ADDRESS", led.ledadd1),
                                                           new XElement("ADDRESS", led.ledadd2), new XElement("ADDRESS", led.ledadd3)), new XElement("COUNTRYNAME", led.ledcountry), new XElement("LEDSTATENAME", led.ledstate),
                                                           new XElement("GSTREGISTRATIONTYPE", led.ledregtype), new XElement("PARTYGSTIN", led.ledgstin)));
                            Req_builder.Append(xdoc.ToString());
    
                        }
                        Req_builder.Append("</TALLYMESSAGE></REQUESTDATA></IMPORTDATA></BODY></ENVELOPE>");
                        byte[] tally_req = Encoding.UTF8.GetBytes(Req_builder.ToString());
                        var http = WebRequest.Create("http://localhost:9000") as HttpWebRequest;
                        var cache_policy = new RequestCachePolicy(RequestCacheLevel.NoCacheNoStore);
                        http.CachePolicy = cache_policy;
                        http.Method = "POST";
                        http.ContentLength = tally_req.Length;
                        http.ContentType = "text/xml ; encoding ='UTF-8";
                        using(var write_request_stream = http.GetRequestStream())
                        {
                          write_request_stream.Write(tally_req, 0, tally_req.Length);
                        }
                          using (var tally_reponse= http.GetResponse() as HttpWebResponse)
                        {
                          using (var tally_response_reader = new StreamReader(tally_reponse.GetResponseStream()))
                        {
                          Response = tally_response_reader.ReadToEnd();
                        }
                        }
                         // response from tally
                        Console.Write(Response);
                    }
                }catch(Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
    
    
    
    
    
        }
    
    
        public class ledadapter
        {
    
            public String ledname { set; get; }
            public String  ledparent { set; get; }
            public String ledbillwise { set; get; }
            public String ledadd1 { set; get; }
            public String ledadd2 { set; get; }
            public String ledadd3 { set; get; }
            public String ledcountry { set; get; }
            public String ledstate { set; get; }
            public String ledregtype { set; get; }
            public String ledgstin { set; get; }
        }
        
    
    
    
    
    
    }
    
     

    Attached Files:

    santosh swami likes this.


  2. Varadha

    Varadha Member


    Dear Sir,
    First of All, Let me thank for providing C# code.
    I Have gone through your code I have little knowledge in C#. I tried to attach this code to a button click but compiler showing an error. please provide me full code or explain to me how to call this method and functions in it
    Thank and regards
    Varadha Raju
     


  3. teja varma

    teja varma Active Member


    post you code here i will correct it and explain you the things
     


  4. Varadha

    Varadha Member


    Thanks for reply. Please help me to learn import through C#.
    I am sending my entire Project Code for your review and necessary corrections. I just added one button to form and on click of button excel data should import into tally. based on this code I further develop codes of voucher import and other things
     

    Attached Files:



  5. teja varma

    teja varma Active Member


    try this code and go through it
    you didn't properly call the method to initiate import from the button click!
    ** **Change the file path to your file path in your System
    you will a success message after importing like this:
    ledcreateres.PNG
     

    Attached Files:

    santosh swami likes this.


  6. Varadha

    Varadha Member


    I am Glad to you that you have corrected my code and promptly sent to me however when I ran the code in my system I am getting Microsoft Ole OLEDB not registered in a local system error. why I am getting this error and how to rectify it. The same logic can we use for importing data from oracle or MySQL also, If not please tell me how can we import data from Oracle and MYSQL also. I am attaching the error screenshot for your observation
     

    Attached Files:



  7. teja varma

    teja varma Active Member


    you have to set the Platform to X86 because oled driver only works for 32bit operating system
    so you have to change your Platform to X86 which works for both 64bit and 32bit
    like this:

    x86 build.PNG
     


  8. teja varma

    teja varma Active Member


    To configure a project to target a X86 platform
    1. On the menu bar, choose Build > Configuration Manager.

    2. In the Active solution platform list, choose New
      1. The New Solution Platform dialog box appears.


      2. In the Type or select the new platform list, choose X86.
     
    Varadha likes this.


  9. Varadha

    Varadha Member


    Thank you for your helpful Guidance. Can I ask one more help. Can we use same method with different connection strung to import data from Oracle or MySQL. If not please guide me on importing from data from Oracle and My SQL
     


  10. teja varma

    teja varma Active Member


    yes you can import from any data base using proper connection Strings
     


  11. Varadha

    Varadha Member


    Ok thank you. I will adopt this methodology and try to import data from other databases. Kindly help me if any problem arises or in case of any need. Thanks once again for guiding me in proper way
     


  12. Varadha

    Varadha Member


    I set the configuration manager as per your guidelines but still, I am getting the same error I am attaching the screenshot for your observation
     

    Attached Files:



  13. Varadha

    Varadha Member


    is a licensed version of MS office required? my MS Office is on trial version
     


  14. teja varma

    teja varma Active Member


    Connect your system through anydesk i will check once
     


  15. Varadha

    Varadha Member


    My Any desk Id Is 748 510 156 i am available now
     


  16. teja varma

    teja varma Active Member


    request will com from lenovo
     


  17. Varadha

    Varadha Member


    Dear Teja Varma can you guide me in TDL one issue I have relating to the Stock report. I developed a stock report and in that Stock Closing Balance, Stock Sales and Stock Outstanding Purchase orders. All fields are showing values correctly but my customer requires total outstanding purchase orders till date he views the report and not based on the period specified in the report rest items should be as per period specified. How can I achieve this for only one field? I am attaching the code for your observation
    To replicate the issue at your end please follow the path
    Gateway of Tally - Display - Stock Report.

    Kindly Help me, sir
     

    Attached Files:



  18. diwakar

    diwakar New Member


    Hello sir, first i want to say thanq for your code. By using your code i implemented revenue export and purchase export to tally. It i working fine. Now i tried the same thing in one of my friend system. While exporting the data , first voucher was exported perfectly. When attempt the next one tally erp 9 was closed automatically. I tested by generating individual xml files and imported by using tally import option. Files were imported successfully. As per my knowledge i the tally was closed when debugger comes at ---- write_request_stream.Write(tally_req, 0, tally_req.Length); this line. If you available i can provide my anydesk id sir.
     


  19. teja varma

    teja varma Active Member


    I can't tell without seeing the code
    if possible attach your code snippet and excel data which you are importing.
     


  20. diwakar

    diwakar New Member


    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    //using System;
    //using System.Collections.Generic;
    using System.Data.OleDb;
    using System.IO;
    //using System.Linq;
    using System.Net;
    using System.Net.Cache;
    //using System.Text;
    using System.Xml.Linq;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;


    namespace WindowsFormsApp1
    {
    public partial class VBPosting : Form
    {

    public VBPosting()
    {
    InitializeComponent();
    SOMECLASS();
    }
    List<ledadapter> ledholder = new List<ledadapter>(); // to store ledgers
    StringBuilder Req_builder = new StringBuilder(); // to build xml request
    XDocument xdoc; // to construct xml String
    String Response; // to hold response from tally

    public void SOMECLASS()
    {
    SqlConnection con = new SqlConnection(@"Data source=VASAVI-PC\VASAVIDB;Initial Catalog=TALLY; User ID=SA;Password=VASAVI@123");
    con.Open();

    SqlCommand cmd1 = new SqlCommand("SELECT SLNO,RECORDTYPE,TRANDATE,VOUCHERTYPE,NARRATION,VOUCHERNO,REFNUMBER,PARTYLEDGER,LEDGERNAME,AMOUNT,TDS " +
    " FROM TALLY_SINGLEVOUCHER WHERE POSTED='N' ", con);

    cmd1.CommandType = CommandType.Text;

    SqlDataAdapter da = new SqlDataAdapter(cmd1);

    DataSet ds = new DataSet();

    da.Fill(ds, "ss");

    dataGridView1.DataSource = ds.Tables["ss"];
    }



    private void Button1_Click(object sender, EventArgs e)
    {
    read_excel();
    }


    public void read_excel()
    {
    try
    {


    string connetionString;
    string OutPut = "";
    string SLNO = "";
    SqlConnection cnn;
    connetionString = @"Data source=VASAVI-PC\VASAVIDB;Initial Catalog=TALLY; User ID=SA;Password=VASAVI@123";
    using (SqlConnection conn = new SqlConnection())
    {
    conn.ConnectionString = connetionString;
    conn.Open();
    string Query1 = "SELECT SLNO FROM TALLY_SINGLEVOUCHER WHERE POSTED='N' ORDER BY SLNO";
    SqlCommand cmd = new SqlCommand(Query1, conn);
    cmd.CommandType = CommandType.Text;
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;
    DataTable dt = new DataTable();
    da.Fill(dt);
    if (dt.Rows.Count > 0)
    {
    for (int i = 0; i < dt.Rows.Count; i++)
    {
    SLNO = dt.Rows["SLNO"].ToString();
    using (cmd = new SqlCommand("TALLY_BULK_XML_POSTING", conn))
    {
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@SLNO", SLNO);

    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {

    OutPut = reader.GetValue(0).ToString();

    }
    Req_builder.Clear();


    // init http request

    Create_led(OutPut);
    reader.Close();
    }

    }
    MessageBox.Show("ALL RECORDS EXPORTED");
    }
    }
    }
    catch (Exception ex)
    {

    }
    }


    private void Create_led(string Data)
    {
    try
    {

    ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls;
    System.Net.ServicePointManager.MaxServicePointIdleTime = 10000;


    Req_builder.Append(Data);
    byte[] tally_req = Encoding.UTF8.GetBytes(Req_builder.ToString());
    var http = WebRequest.Create("http://localhost:9000") as HttpWebRequest;
    ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls;
    var cache_policy = new RequestCachePolicy(RequestCacheLevel.NoCacheNoStore);
    http.CachePolicy = cache_policy;
    http.Method = "POST";
    http.ContentLength = tally_req.Length;
    http.ContentType = "text/xml ; encoding ='UTF-8";


    using (var write_request_stream = http.GetRequestStream())
    {


    System.Net.ServicePointManager.MaxServicePointIdleTime = 10000;
    //write_request_stream.WriteTimeout = 600000;

    ////////// AFTER BELLOW LINE ,TALLY WAS CLOSED AUTOMETICALLY
    write_request_stream.Write(tally_req, 0, tally_req.Length);


    System.Net.ServicePointManager.Expect100Continue = false;
    write_request_stream.Close();
    HttpWebResponse response = (HttpWebResponse)http.GetResponse();
    Stream dataStream = response.GetResponseStream();
    StreamReader reader = new StreamReader(dataStream);
    WebResponse myWebResponse = http.GetResponse();

    }



    using (var tally_reponse = http.GetResponse() as HttpWebResponse)
    {
    using (var tally_response_reader = new StreamReader(tally_reponse.GetResponseStream()))
    {
    Response = tally_response_reader.ReadToEnd();
    //System.Net.ServicePointManager.MaxServicePointIdleTime = 10000;
    }
    }

    // response from tally
    MessageBox.Show(Response);
    System.Net.ServicePointManager.MaxServicePointIdleTime = 10000;
    }
    }
    catch (Exception ex)
    {
    MessageBox.Show(ex.Message);

    }
    }

    private void when(bool p)
    {
    throw new NotImplementedException();
    }

    private void button2_Click(object sender, EventArgs e)
    {


    }

    private void label3_Click(object sender, EventArgs e)
    {

    }



    }



    }



    public class ledadapter
    {

    public String ledname { set; get; }
    public String ledparent { set; get; }
    public String ledbillwise { set; get; }
    public String ledadd1 { set; get; }
    public String ledadd2 { set; get; }
    public String ledadd3 { set; get; }
    public String ledcountry { set; get; }
    public String ledstate { set; get; }
    public String ledregtype { set; get; }
    public String ledgstin { set; get; }
    }
     


  21. Himanshu-2002

    Himanshu-2002 Active Member


    I don't Know Much About C# But I can suggest you few things
    Here is the list
    1. Run Tally With Admin Rights
    2. Make Sure a Company is open while importing Or Exporting Data
    3. Run Tally as a Server Or Both ( Server and Client )

    Use Proper XML Code....If required You can tell me I will send them All... Because I prepared it for a Project

    I can help you with Python...If needed Message me...I will try...
     


  22. diwakar

    diwakar New Member


    ThankQ Himanshu garu for your suggestions . I used the same coding for another project, And still it is working good. i checked and followed the 3 points you suggested. This is my second project. Same options and same rules i followed. But here the tally was closed automatically for second voucher.
     


  23. Himanshu-2002

    Himanshu-2002 Active Member


    It is closing directly without showing any Error?
     


  24. shaikh kd

    shaikh kd New Member


    i have one tdl file which import sale from excel to tally but some error occur pls give me solution
    giveing file both
     

    Attached Files:



  25. shaikh kd

    shaikh kd New Member


    pls give me as soon as possible
     


Share This Page