Importing Invoices from an Excel File: Steps, Preparing the File, and Solving Errors
Importing invoices lets you add a large number of sales invoices at once from an Excel or CSV file, such as moving invoices from a previous system or an online store, instead of entering each invoice manually. Each imported invoice is created the same way as a manual invoice, with its entries, stock movement, and payments.
This guide explains preparing the file, how items are grouped into one invoice, the import steps, how the system handles clients, products, taxes, and payments, the effect of importing on the system, and the error messages and their solutions.
Contents
Preparing the Import File
Allowed files: csv and xls and xlsx. Start by downloading the sample file from Download Sample on the import page, and write your data in it. Each row in the file = one item in an invoice.
| Column in the sample file | System Field | Required | Notes |
|---|---|---|---|
| Invoice Number | Invoice Number | Yes | Groups the items into one invoice. It is used as the invoice number unless automatic numbering is enabled. |
| Date | Date | Yes | The invoice date, in the date format set in your account. |
| Issue Date | Issue Date | No | If it is empty, it is today’s date. |
| Currency Code | Currency Code | No | Such as SAR or EGP. If it is empty, the account’s base currency is used. |
| Client | Client | Yes | The client’s name, number, phone, or email. |
| Item | Item | Yes | The product or service name, code, or barcode. |
| Description | Description | No | The item description. |
| Quantity | Quantity | Yes | The item quantity. |
| Tracking Data | Tracking Data | No | For products with serial numbers or batches, separated by a comma. |
| Unit Price | Unit Price | Yes | The item price. |
| Discount Item Amount / Percentage | Discount Item Amount / Discount Item Percentage | No | If both are filled, the amount is applied. |
| Tax1 / Tax2 | Tax1 / Tax2 | No | The tax name as in Taxes. |
| Shipping Option / Shipping Fees | Shipping Option / Shipping Fees | No | At the invoice level. |
| General Percentage / Amount Discount | General Percentage Discount / General Amount Discount | No | A discount on the whole invoice. |
| Adjustment Label / Value | Adjustment Label / Adjustment Value | No | An adjustment on the invoice total. |
| Payment Method / Payment Amount | Payment Method / Payment Amount | No | To record the invoice as paid. For more than one payment, separate them with a comma, such as “cash, bank” and the amounts “500, 700”. |
| Warehouse | Warehouse | No | The warehouse name as in Warehouses. If it is empty, the main warehouse is used. |
| Custom field 1 / 2 | Invoice custom fields | Depending on the field setting | Invoice custom fields appear in the field mapping step. |
The Fields Mapping page also shows the fields Order Source, Delivery status, and Delivery Date, which aren’t in the sample file.
🛈
Note: The number of columns must be equal in all rows, and the file must not contain only one column. Save a CSV file with UTF-8 encoding so that Arabic letters appear correctly.
One Invoice with More Than One Item
Rows that carry the same Invoice number are grouped into one invoice, and each row represents an item. The invoice data must be identical in all its rows: the date, issue date, client, currency, shipping, general discount, adjustment, payment, and custom fields.
| Invoice Number | Date | Client | Item | Quantity | Unit Price | Payment Method | Payment Amount |
|---|---|---|---|---|---|---|---|
| 1001 | 29/10/2024 | Al Noor Company | Network cable | 10 | 25 | Cash | 550 |
| 1001 | 29/10/2024 | Al Noor Company | Installation service | 1 | 300 | Cash | 550 |
| 1002 | 30/10/2024 | Al Rayan Establishment | Wireless router | 2 | 300 |
Result: two invoices; the first, 1001, for Al Noor Company with two items and paid 550, and the second, 1002, for Al Rayan Establishment with one item and unpaid.
Import Steps
- Open Manage Invoices and click the arrow next to Add and choose Import, or open Importing Invoices.
- In CSV / Excel File Choose the file.
- For CSV files, choose Delimiters: usually Comma Separated ( , ), or Semicolon Separated ( ; ), or Tab Delimited. This field doesn’t appear for Excel files.
- Enable Import First Row only if the first row in the file is data and not column headers.
- Choose Invoice Status: Issued or Draft, and enable Auto-generate Invoice Numbers if you want the system numbering.
- Click Next Step.
- In Fields Mapping For each Field in the system choose the matching column from the matching field in the file. The system matches columns with the same name automatically.
- Enable Ignore errors while importing if you want to import the valid rows and skip the invalid rows.
- Click Import, and follow the progress on the page Importing Invoices.
Import Options
| Option | Effect |
|---|---|
| Invoice Status: Issued | Invoices are issued directly, with their entries and stock movement, and the payments are completed. |
| Invoice Status: Draft | Saved as drafts for review; no entries or stock movement until they are issued, and the payments are drafts. |
| Auto-generate Invoice Numbers | Enabled: invoices take the next numbers in the system numbering, and the invoice number column is used for grouping only. Disabled: invoices take the numbers in the file. |
| Ignore errors while importing | Enabled: valid rows are imported and a summary of errors appears. Disabled: the whole import stops at the first invalid row. |
There is no option to update existing invoices; importing always creates new invoices.
How Does the System Handle the File Data?
| Data | Search method | If not found |
|---|---|---|
| Client | By business name, client number, phone, name, email, or tax number, among non-suspended clients. | A new client is created with the written name. |
| Item | By product name, code, or barcode (including unit barcodes). | A new product is created with the name, a price of 0, and stock tracking. |
| Tax | By name. | An error in the row; add the tax first from Taxes. |
| Warehouse | By name. | An error in the row. |
| Shipping option | By name. | A new shipping option is created. If the option has fees, its fees are used instead of the file’s fees. |
| Payment method | By name. | A new payment method is created. |
⚠
Warning: The search for the client and product is partial; a name like “Al Noor” may match “Al Noor Company” or “Al Noor Trading”. Use the client number and product code in the file to avoid wrong linking or creating duplicate clients and products.
Following the Import and the Result
- After clicking Import the page Importing Invoices opens, and the import runs in the background invoice after invoice.
- Counters appear for Successful and Failed, and next to each invoice Invoice #(number) has been successfully created. with its link, or the reason for failure.
- When it finishes, the message Importing was completed successfully appears. If the import stops, you can click Resume.
- If rows fail the data checks, the import stops at the Result step, which shows the message Thank you, your file has been imported successfully with the following errors., the list of errors, and the counters Imported successfully., Updated successfully., and Records failed to import.
- Invoices that succeeded remain even if other invoices failed; correct the failed invoices in a new file and import them only.
The Effect on the System
| Place | What Happens (for issued invoices) |
|---|---|
| Invoices | They appear in Manage Invoices with the default invoice layout, and in the current branch. |
| Journal entries | A sales entry for each invoice like a manual invoice, and an entry for each payment. |
| Inventory | Deducted from the specified or main warehouse, or requisitions are created if requisitions for sales are enabled. |
| Client balance | It increases by the unpaid remainder. |
| Numbering | Invoice numbering advances with automatic numbering, and client and product numbering advances when new ones are created. |
| E-invoice | Issued invoices are sent to the platform (such as ZATCA) if the integration is enabled. |
Drafts don’t create entries or stock movement until you issue them.
Limits and Rules
- The maximum is 10000 rows in the file.
- The maximum is 500 items in one invoice.
- The treasury, sales person, branch, due date, or notes can’t be set from the file.
- An invoice in a closed period isn’t imported.
- If Negative Inventory is set to Disable negative inventory for all items, an issued invoice that exceeds the available quantity fails.
Error Messages and Their Solutions
| Message Shown | Cause | Solution |
|---|---|---|
| This is a required field and could not be empty. | A required field is empty in Import Options: CSV / Excel File, Delimiters, or Invoice Status. | Choose the file, the delimiter, and the invoice status, then click Next Step. |
| Invalid Data, Missing required field or does not exist (field) at row #(number) | A required field is empty: the invoice number, date, client, or item. | Complete the value. |
| The invoice (field) should be the same for all invoice lines that share the same invoice code at row #(number) | Invoice data differs between rows with the same number. | Unify the date, client, payment, general discount, and others in all rows of the invoice. |
| Invalid Data, Wrong Relation (field) at row #(number) | A tax or warehouse that doesn’t exist. | Write the name as it is in the system, or add it first. |
| Invalid Data, Data must be in the correct date format in ‘(field)’ field at row #(number) | A date in the wrong format. | Write it in the date format set in your account, and format the Excel cells as text so the format doesn’t change. |
| Error Adding Invoice #(number): Valid number required at row: (number) | Text in a numeric field. | Write a number only. |
| Invalid Data, encoding issue or corrupted characters in field (field) at row #(number) | The file isn’t UTF-8 encoded. | Save the file as CSV UTF-8 from Excel. |
| The maximum number of records in the sheet is 10000 | The file has more than 10000 rows. | Split the file. |
| The number of items lines you can import in each Invoice is 500 | An invoice with more than 500 items. | Split it into more than one invoice. |
| Sorry, it is not allowed to import one column file, make sure you have selected a file with the correct format | A file with one column, usually because of choosing the wrong delimiter. | Choose the correct delimiter, such as Semicolon for Arabic files. |
| The uploaded file could not be processed. Please ensure it is a valid XLS, XLSX, or CSV file and try again. | An unsupported or corrupted file type. | Save it again in xlsx or csv format. |
| Error Adding Invoice #(number): Serial number already exists at row: (number) | The invoice number is already used, and Unique is enabled for invoices in Auto Number Settings. It appears when the status is Issued; draft invoices are imported with the repeated number. | Enable Auto-generate Invoice Numbers, or change the numbers. |
| Error Adding Invoice #(number): (reason) at row: (number) | The invoice didn’t pass the invoice validations, such as a closed period, an unavailable quantity, or a discount greater than 100%. | Fix the mentioned reason and import the invoice again. |
| You are not allowed to access this page | You don’t have the permission to add invoices. | Request the permission from the account manager. |
Frequently Asked Questions
How do I import an invoice with more than one item?
Write each item in a row, and repeat the same invoice number and its data in all rows.
Can invoices be imported as paid?
Yes, fill in the payment method and payment amount, and for more than one payment separate them with a comma.
Does importing create new clients and products?
Yes, if it doesn’t find the client or product, it creates it with the written name.
Can existing invoices be updated by importing?
No, importing creates new invoices only.
I want to review the invoices before approving them?
Choose Draft in Invoice Status, then review and issue them.
Why did the Arabic letters appear as symbols?
Because the file isn’t UTF-8 encoded; save it from Excel as CSV UTF-8, or upload an xlsx file.
Why did the invoice numbers differ from the file?
Because Auto-generate Invoice Numbers is enabled.
Some invoices failed; do I reimport the whole file?
No, the successful invoices were created; import only the failed invoices after correcting them.