Help Document for Importing Sales Transactions as Bulk Using Excel File
Purpose: This guide will help you prepare an Excel file for bulk importing sales transactions into your system. The document outlines the structure of the Excel file, field descriptions, and steps to ensure a smooth import process.
Things to do before start importing transaction
- Take Manual backup from About page.
- Go to Settings > Serial Numbers and update the Invoice transaction series to Manual.
- Go to Settings > Company Settings and fill default HSN Code & default Tax rate if not provided already.
- In Settings > General, set “Total Amount (Invoice)” as either Inclusive or Exclusive.
STEP 1 : Select File
- Go to Sales > Invoice. Click Import XLS from the dropdown near Export Icon.
- In the pop-up, click Choose files and select the file in your local folder.
- Here you can download the import template file with sample records.
STEP 2 : Map Column Headers
Click “Map Column Headers” and provide
1. Excel File Format
The Excel file must contain specific headers and data in a structured format. Below are the key fields required in your file:
| Column Name | Description | Example |
|---|---|---|
| Trans No | Unique identifier for each transaction. | IN-167 |
| Transaction Type | Type of transaction. | Reti – Cash Invoice |
| Category Code | Code representing the product or sales category. | (Optional) |
| Contact Name | Name of the customer or contact associated with the transaction. | JAMMU & KASHMIR |
| GSTIN | GST Identification Number for the customer. | 29ABCDE1234FZ1 |
| Contact Email | Email address of the customer. | contact@example.com |
| Source of Supply | State from where the goods are supplied. | JAMMU & KASHMIR |
| Destination of Supply | State where the goods are being delivered. | TELANGANA |
| Billing Address Details | Billing address (Name, Address1, Address2, Country, City, State, Zip, Phone). | N/A |
| Shipping Address Details | Shipping address (Name, Address1, Address2, City, State, Zip, Phone). | N/A |
| Trans Date | Date of the transaction in the format DD/MM/YYYY. | 03/07/2024 |
| Due Date | Payment due date in the format DD/MM/YYYY. | 10/07/2024 |
| Sales Rep | Name of the sales representative handling the transaction. | John Doe |
| Buyer Order No | Buyer’s order number if available. | 12345 |
| Buyer Order Date | The date when the buyer placed the order (DD/MM/YYYY). | 01/07/2024 |
| Notes | Additional notes regarding the transaction. | Urgent Delivery |
| Reference | Reference number or information related to the transaction. | REF-1234 |
| Status | Current status of the transaction (e.g., Pending, Completed). | Completed |
| Taxable Amount | Amount on which the tax is calculated. | 940.68 |
| RoundOff | Round-off adjustment if applicable. | 0.32 |
| Account Name | Account to which the transaction is recorded. | Sales Revenue Account |
| Total Amount | Final amount for the transaction, including tax. | 1110.00 |
| Item Name | Name of the product sold. | TEFLON BELT – 750 |
| Item Qty | Quantity of the product sold. | 1 |
| HS Code | Harmonized System (HS) Code of the product. | 9405.50 |
| Free Qty | Free quantity offered with the product, if any. | 0 |
| Unit | Unit of measurement (e.g., Pack, Set). | Pack |
| Rate | Unit rate or price per item. | 940.68 |
| DiscPer1 | Discount percentage if applicable. | 2% |
| DiscAmt1 | Discount amount applied. | 18.81 |
| Tax | Tax amount applied to the transaction. | 169.32 |
| Tax Rate | Tax rate percentage applied to the transaction. | 18% |
| Item Total | Total amount for the line item (Quantity * Rate – Discount + Tax). | 1110.00 |
| Currency Code | Currency in which the transaction is processed (e.g., INR, USD). | INR |
| StateName | The state where the transaction takes place. | JAMMU & KASHMIR |
| Payment Mode Reference | Reference to the payment mode used for the transaction (e.g., Bank Transfer, Credit Card). | Bank Transfer |
| Payment Account | The account to which payment is made. | Current Account |
| Payment Amount | The amount paid for the transaction. | 1110.00 |
2. Sample Excel Data
Here’s how the data might look in an Excel spreadsheet:
| Trans No | Transaction Type | Contact Name | Source of Supply | Destination of Supply | Item Name | Item Qty | Rate | DiscPer1 | Tax | Total Amount |
|---|---|---|---|---|---|---|---|---|---|---|
| IN-167 | Amazon Sales | JAMMU & KASHMIR | JAMMU & KASHMIR | TELANGANA | TEFLON BELT – 750 | 1 | 940.68 | 2% | 169.32 | 1110.00 |
| MAA4-232 | Amazon Sales | JAMMU & KASHMIR | JAMMU & KASHMIR | TELANGANA | BAND SEALER CARBON BRUSH | 1 | 305.08 | 0% | 54.92 | 360.00 |
3. Steps to Import Bulk Sales Transactions
- Prepare Excel File: Ensure the Excel file contains all the fields listed above with accurate data. Save the file in
.xlsxor.csvformat depending on your system requirements. - Log In to the Sales System: Open your Sales/ERP system where you intend to import the transactions.
- Navigate to Bulk Upload: Go to the Sales or Transactions module. Look for an option that says “Import Sales Transactions” or “Bulk Upload”.
- Upload Excel File: Click on Upload File or Import and choose the prepared Excel file from your system.
- Map Fields (If Required): Some systems may prompt you to map the columns in your file to the system’s data fields. Ensure each column (e.g., Trans No, Transaction Type, Item Qty) is mapped correctly.
- Validate Data: The system may perform a data validation step. Ensure all required fields are filled and check for errors such as missing data, incorrect formats, or mismatched fields.
- Confirm Import: Once validation passes, proceed with the import. The transactions will now be processed and recorded in your system.
- Review and Reconcile: After the import, review the transactions to ensure they have been correctly added. Check for any discrepancies and reconcile the imported data with your sales records.
4. Troubleshooting
- Missing Fields: If any fields are missing or incorrectly named, the system might throw an error. Double-check that all necessary columns are present.
- Data Format Errors: Ensure dates, numbers, and text are formatted correctly. For example, the date should be in DD/MM/YYYY format.
- Validation Failures: If your file doesn’t pass validation, look at the error message and correct the issues in the file.
5. Common Tips
- Save Backup: Always save a backup of your file before uploading.
- Check System Guidelines: Different systems may have slight variations in their required fields or formats. Always consult your system’s specific import documentation.
- Test with Sample Data: Before importing large batches, try importing a smaller file with a few records to ensure the process works smoothly.
