There are several advantages to creating vouchers in Excel and importing them into Tally. As you can copy-paste, fill in the blanks, utilize formulas, and do many other things in Excel, this can save you a tonne of time. This can be a quite beneficial while:
Getting year-end entries ready, such as depreciation
A large number of sales, purchases, etc. entries and be convenient.
Entries that repeat at a specific frequency
bank statements import
import transactions from another software, which is the most crucial step.
- Make the required ledgers
In Tally, first, create the necessary masters (ledgers). Under the heading “Sundry Creditors,” I’ve generated two ledgers called “ABC Wholesalers” and “XYZ Distributors.” Additionally, I’ve added a “Medicine Purchase” ledger to the “Purchase Accounts” category. Keep in mind that if you build masters by pasting the names from Excel, invisible characters may join after the text and cause import issues. Save the Excel file as text/CSV in such circumstances, then open it in Notepad++ and copy-paste from there. Additionally, you can copy-paste data from Excel to Notepad++ and then from that application to Tally.
- Create a test entry in Tally.
Make an example coupon that perfectly matches the type of imported vouchers. Here, I’ve made a journal voucher with the amounts ABC Wholesalers Cr and Medicine Purchase Dr. Rs.2000. Create a similar voucher as a model in cases when vouchers involve numerous ledgers.
- From Tally, export the sample voucher.
To export the day book to XML format, open it (Display > Day Book) and press Alt+E. Please take note that on this day, there will only be one of the vouchers we made above. The goal of this step is to construct an import template. As “DayBook.xml,” the exported file will be placed in the Tally folder.
- Open the Tally export XML file.
You can use notepad or more sophisticated editors like Notepad++ with syntax highlighting. The data fields should be noted.
- Get data ready to import into an Excel file.
Note that the columns must be in the same order as the data fields in the aforementioned XML file.
The date field must be in the text format YYYYMMDD. Use formulas like =CONCATENATE (TEXT (YEAR (A1),”0000″), TEXT (MONTH(A1),”00″), TEXT (DAY(A1),”00″)) to insert this information into a text-formatted column, where A1 includes the date.
A maximum of two decimals must be used. To do this, use the =ROUND(Number, 2) method. Due to the layout of the cells and the internal differences, the decimals may appear to be two in the display. In this situation, it can result in an import error.
Numbers must be formatted as numbers rather than being comma-separated or in currency.
The debit value must be zero. Both the debit and the credit must add up. Utilize a function to verify this. Otherwise, it can result in an import error.
The ledger names used in Excel and Tally must match identically since Tally matches ledger names while importing. You can use Copy+Paste to edit the ledger masters after exporting them from Tally (Gateway > Display > List of Accounts > Alt+E).
When importing data to tally, special characters in the data, such as those in ledger master names and narrations, can result in errors. Use the regular expression [a-zA-Z0-9,.-rns]+, which matches any characters aside from alphabets, numerals, and commas, to find this or replace it in the data.
This can be changed or eliminated in favor of another suitable character. The created final XML can also include this step.
In Tally, special characters in ledger master names must be manually adjusted. If there are a lot of masters, it may be possible to export the data as XML, edit it, and then import it again.
- Copy the XML for editing.
The XML from TALLYMESSAGE xmls :UDF=”TallyUDF”> to /TALLYMESSAGE> should be copied to a new file. This is the part that needs to be changed. Because we must paste the final output here, take note of this location in the original XML file. The XML codes for the header and footer must not change.
- Identifying elements must be eliminated.
Remove the values (highlighted in yellow) from the Tally-inserted unique identifiers (tag values) VOUCHER and GUIID in the duplicated XML. Only remove the values; leave the tags alone.
Delete the full tag EFFECTIVEDATE>xxxx/EFFECTIVEDATE> as well (highlighted in yellow).
You might choose to delete all content inside the VOUCHER> tag (underlined in red). This is optional.
- Excel columns can be added.
Excel users can insert one column between each set of data columns.
- The XML static data lines can be pasted.
Each line of static data in the XML that is highlighted in yellow should be copied and pasted into the first row of its corresponding column in the Excel sheet.
- Drag the cells for all rows to fill automatically.
Drag the fill-handle to automatically fill the data into every row of the column.
- Copy and concatenate data
To merge the values from all the columns in a row, use the concatenate function =CONCATENATE(A2, B2, C2,…, N2, O2). To pick each cell individually inside the function, press CTRL+CLICK. To apply the formula to all rows, drag using the fill handle. Concatenate issues may occur when XML tags are extremely long.
Additionally, you can just choose out the desired cells, copy them all, and then paste them into Notepad++. It is possible to completely omit the concatenate step.
However, in this scenario, pasted tab characters should be removed. In Notepad++, use regex t to search for and replace tab characters.
- Add the XML data to the source file.
To replace the text, copy the text and paste it into the original XML file in the same spot after copying the concatenated data or directly from the cells. The XML tags at the start and the end shouldn’t be changed. Save the XML document.
- into Tally import
Before importing data, make sure the following settings are in place: load the company > press F12 (configure) > General > set Ignore errors & continue during data import to YES.
Select Vouchers under Import of Data in Tally. To import, type the XML filename and path and press enter. To check for errors, look in the Tally folder’s import log file, tally. imp. This file can be examined to determine the cause of the error.
- Complete
The day book would contain the vouchers.
Get FREE Demo of Excel to Tally Software
