->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; } } }
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
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
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:
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
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:
To configure a project to target a X86 platform On the menu bar, choose Build > Configuration Manager. In the Active solution platform list, choose New The New Solution Platform dialog box appears. In the Type or select the new platform list, choose X86.
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
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
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
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
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.
I can't tell without seeing the code if possible attach your code snippet and excel data which you are importing.
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; } }
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...
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.
i have one tdl file which import sale from excel to tally but some error occur pls give me solution giveing file both