Import vouchers from Excel to Tally - New method

The benefits of preparing vouchers in Excel and importing into Tally are many. This saves a lot of time and can be convenient as you can copy-paste, fill down, use formulas and do lot more in Excel. This can be immensely helpful while :

• Preparing year-end entries like depreciation
• Huge volume of entries in sales, purchases, etc.
• Entries repeating in a particular frequency
• Import bank statements
• And most important, import transactions from another software.

See Video Tutorial :

There are many software and add-ons available for this purpose, but the method explained here does not use any third-party software or tools. The user must be familiar with MS Excel and Tally to do this. The old method used mail-merge feature of MS Word. This new method uses only MS Excel and is more simple. Please refer to the old method here. Tested on Tally.ERP 9. Though this seems a bit complex, you can be sure - it works, and it's free.

● Create necessary ledgers

Create all the necessary masters (ledgers) in Tally first. Here I've created two ledgers - "ABC Wholesalers" and "XYZ Distributors" under the group "Sundry Creditors". Also, I've created a "Medicine Purchase" ledger under the group "Purchase Accounts".  Note that while creating masters by copy-pasting the names from excel, invisible characters can join to the end of the text, which can create errors while importing. In such cases, save the excel as text/csv and open in Notepad++ and copy-paste from there. You can also copy-paste from excel to Notepadd++ first and then from there to Tally.

 

● Make a sample entry in Tally

Create a sample voucher exactly of the nature of the vouchers to be imported. Here I've created a Journal Voucher with Medicine Purchase Dr. Rs.2000 and ABC Wholesalers Cr. Rs.2000. In case of vouchers involving multiple ledgers, create similar one as sample.

● Export the sample voucher from Tally

Open day book (Display > Day Book) and press Alt+E to export the day book to XML format. Note that there shall be only a single voucher that we created above on this day. This step is to create a template for import. The exported file will be saved to Tally folder as "DayBook.xml"

● Open the XML file exported from Tally

You can use any advanced editors like Notepad++ with syntax highlighting or simply use notepad. Take note of the data fields.

● Prepare data to import in Excel file

• Please note that the columns shall be in the exact order as the data fields appear in the xml file above. 
• The date field shall be in YYYYMMDD text format. This shall be either entered into a text formatted column or use formulas like =CONCATENATE (TEXT (YEAR (A1),"0000"), TEXT(MONTH(A1),"00"), TEXT(DAY(A1),"00")) where A1 contains the date. 
• Decimals must be limited to two. Use =ROUND(Number, 2) function to achieve this. The decimals may appear in the display to be two due to the formatting of the cells and be different internally. In such case, it may lead to an error while importing. 
• Numbers shall not be comma separated or currency formatted, but formatted as numbers.
  
• The debit value shall be negative. Both debit and credit should tally. Use some function to check this. Else, it may cause error while importing.
• Since tally uses the ledger name to match while importing, it is very important that the ledger names used in excel is exactly the same as that in Tally. To avoid typos you may export the ledger masters from tally (Gateway > Display > List of Accounts > Alt+E) as a text file and use Copy+Paste.
• Special characters in the data including those in ledger master names and narrations can cause errors while importing to tally.  To find this or replace, open the data as text/csv in Notepadd++ and use the regular expression [^a-zA-Z0-9\,\.\-\r\n\s]+ which matches all characters other than alphabets, digits, comma, dot, hyphen, linefeed, carriagereturn, and space. This can be removed or replaced with some acceptable character. This step can be done in the generated final xml also.
  
• Special characters in ledger master names will have to be corrected manually in Tally, and if there are a large number of masters, it can be exported as xml, corrected, and imported back.
  

● Copy XML portion to edit

Copy the portion of the XML from  <TALLYMESSAGE xmlns:UDF="TallyUDF">  to </TALLYMESSAGE> to a new file. This is the portion we need to replace. Note this position in the original xml file, since we have to paste the final output here. The header and footer XML codes shall remain unchanged.

● Remove identifiers

• In the copied XML, remove the values (highlighted in yellow) from the tags <VOUCHER> and <GUID> which are unique identifiers inserted by Tally. Remove the values only, and not the tags. 
• Also remove the entire tag <EFFECTIVEDATE>xxxx</EFFECTIVEDATE>, (highlighted in yellow).
• Optionally you may remove everything inside the <VOUCHER> tag (underlined in red). This is optional.

● Format the XML to single lines

Separate the static data (the XML tags) and the variable data (the values) into separate lines. Remove the linefeeds and ensure that all the XML tags before and after the data are on a single line. This can be done easily in Notepad++ by selecting the required text and pressing Ctrl+J.

● Insert columns in Excel

Insert one column each between the columns of data in Excel.

● Copy-paste the XML static data lines

Copy each static data line of the XML (highlighted in yellow) and paste it into the first row in the respective columns in the Excel sheet.

● Drag the cells to auto-fill into all rows

Use fill-handle and drag to auto-fill the data to all rows in the column.

● Concatenate / Copy data

• Use concatenate function  =CONCATENATE(A2,B2,C2,...,N2,O2) to combine data of all the columns in a row. Use CTRL+Click inside the function to select cells one by one. Drag using fill-handle to apply the formula to all rows. In case of very lengthy xml tags, concatenate function can trigger errors.
• You can also simply select and copy the entire cells directly and paste in Notepadd++. The concatenate step can be avoided altogether. 
• But in such case, remove tab characters after pasting. Use regex \t in Notepad++ to find and replace tab characters.
  

● Paste the XML data to original file

Copy the concatenated data or from the cells directly and paste it into the original XML file in the same position to replace the text. Do not modify the xml tags in the beginning and at the end. Save the XML file.

● Import into Tally

Before doing import, ensure to configure the following: Load the company > Press F12 (configure) > General > Ignore errors & continue during data import - set this to YES

In Tally, go to Import of Data > Vouchers. Enter the xml filename with path and press enter to import. In case of any error, see the import log file tally.imp in the Tally folder. Analyzing this file can identify why the error occurred.

● Complete

The vouchers would be available in the day book.

The same method can be used to import masters (ledgers) also. For this, go to Display > List of Accounts and export as XML for schema. Then use the same methods to generate an XML and go to Import of Data > Masters to import it to Tally.