Data Import Errors

What is this page about: How to troubleshoot errors when importing financial data via CSV: file format and column requirements, common validation errors (data types, duplicates, dates, amounts, vendor references), journal-entry-specific structural errors, and what to do when an import completes but data doesn't show up. Also covers less obvious edge cases like delimiter/encoding misdetection, ragged rows, and AI header-mapping failures.

# On this page

• Overview
• CSV Format Requirements
• Issue 1: File Format Not Recognised
• Issue 2: Column Not Found or Missing Required Column
• Issue 3: Invalid Data Type or Type Mismatch
• Issue 4: Duplicate Entry or Record Already Exists
• Issue 5: Invalid Date or Date Out of Range
• Issue 6: Invalid Amount
• Issue 7: Invalid Vendor/Customer or Reference Not Found
• Issue 8: Row Rejected or Validation Error
• Issue 9: Import Completes but Data Missing
• Issue 10: File Too Large or Upload Timeout
• Issue 11: Entry Not Balanced or Journal Entry Structural Errors
• Issue 12: Lines in the Same Entry Don't Match
• Issue 13: Account Code Rejected Even Though the Account Exists
• Issue 14: Delimiter or Encoding Misdetected
• Issue 15: Row Has Too Few Columns
• Issue 16: Header Mapping Failed (Vendors / Chart of Accounts)
• Related Articles

This article covers common errors that occur when importing financial data via CSV and how to fix them.

# Overview

Light supports importing financial data via CSV (Comma-Separated Values) files. This is useful for bulk uploads, migrating from other systems, or importing data that isn't available through integrations. This article helps you understand and resolve common import errors.

# CSV Format Requirements

Before troubleshooting errors, understand the required format:

# Basic Requirements

• Format: CSV text file for most imports (the chart of accounts import also accepts Excel files)
• Encoding: UTF-8 recommended (Light auto-detects the file's character encoding, falling back to UTF-8 if detection is inconclusive)
• Line Endings: Unix (LF) preferred, but Windows (CRLF) is accepted
• Column Headers: First row must contain column names
• Data Types: Values must match expected types for columns

Imports in Light start from the page for each record type, for example Journal entries, Bills, Vendors, or a bank account. Each import type expects its own set of columns.

# Common Column Names

• Bank transactions: Date, Payer/Payee Name, Transaction Id, Reference, Amount, Memo (Date and Amount are required)
• Journal entries: Entry Id, Date, Currency, Debit, Credit, Account Code, Entry Description, Line Description, Business Partner Name, plus optional columns such as Tax Code and Tax Amount
• Bills: Invoice Id, Company Entity Code, Currency, Pay From Account, Invoice Date, Invoice Total, Vendor Name, Status, Ledger Account Code, Tax Code, Amount
• Vendors and chart of accounts: column headers are mapped automatically by AI, so exact header names are not required

Tip: When in doubt, download the template from the import dialog on the relevant page to see the expected format. Required columns are marked with (required) in the template.

# Issue 1: "File Format Not Recognised"

# Symptoms

• Error along the lines of "Only CSV files are allowed"
• File upload is rejected
• Light doesn't let you preview the data

# Solutions

1. Verify File Extension:

   • Ensure file ends with .csv (not .xlsx, .txt, or others)
   • Rename file if extension is wrong
   • On Mac, make sure .xlsx files aren't hidden as .csv

2. Check File Encoding:

   • Light auto-detects file encoding, but UTF-8 is the safest choice
   • Open CSV in text editor (Notepad, VS Code, Sublime)
   • Go to File → Save As
   • Select UTF-8 from encoding dropdown
   • Save the file

3. Verify It's Not Excel Format:

   • Save as .csv rather than .xlsx or .xls. Exception: the chart of accounts import accepts Excel files directly, so this step doesn't apply there
   • For every other import type, if you have an Excel file:
     • Open in Excel
     • Click File → Save As
     • Choose CSV (Comma Delimited) format
     • Save as .csv

4. Check for Binary Data:

   • Don't include images, PDFs, or non-text data
   • CSV can only contain text values
   • If you need to import files, use document upload instead

5. Test with Simple CSV:

   • Create a test CSV in a text editor
   • Add simple headers: name,amount,date
   • Add one line of sample data
   • Try importing the test file
   • If test works, your main file has specific issues

# Issue 2: "Column Not Found" or "Missing Required Column"

# Symptoms

• Error along the lines of "Unable to load columns" or "This field is required"
• Only some columns are accepted

# Solutions

1. Verify Column Headers:

   • Open CSV in text editor
   • Check the header row (first row)
   • Column names must match what Light expects
   • Column names are not case-sensitive, and leading or trailing spaces are trimmed
   • Spaces and underscores in column names are interchangeable (e.g., "account code" and "Account_Code" both work)

2. Check Required Columns:

   • Different record types require different columns
   • Bank transactions require: Date, Amount
   • Bill imports require: Invoice Id, Company Entity Code, Currency, Pay From Account, Invoice Date, Invoice Total, Vendor Name, Status, Ledger Account Code, Tax Code, Amount
   • Verify your file has the minimum required columns

3. Export Template:

   • Light can provide a template CSV
   • Download the template from the import dialog on the relevant page (for example the journal entry import)
   • Use the template to understand correct column names, where required columns are marked with (required)
   • Copy your data into the template structure

4. Rename Columns:

   • If your CSV uses different column names, rename them
   • Open in text editor or Excel
   • Change column names to match Light's expectations
   • Save and retry import

5. Extra Columns Are Fine:

   • Light checks the required headers only, and ignores any unknown or extra columns in your CSV
   • If you're still seeing "Column not found," look for a required column that's missing or misnamed
   • Leave extra columns in place; stripping them out makes no difference

# Issue 3: "Invalid Data Type" or "Type Mismatch"

# Symptoms

• A validation error naming the problem column, along the lines of "invalid data type" or "expected number, got text"
• Row import fails for specific columns

# Solutions

1. Check Data Types:

   • Dates: YYYY-MM-DD (2024-01-15) works for all imports; bank transaction, bill, sales invoice, credit note, and customer credit imports also accept DD/MM/YYYY, DD.MM.YYYY, and DD-MM-YYYY. Use one of these rather than the US-style MM/DD/YYYY
   • Amounts: Numbers only, no currency symbols ($100 is wrong, 100 is right)
   • Numbers: Thousands-separator commas are stripped automatically (1,000 is read as 1000), but commas as decimal separators are not supported
   • Text: Anything can be text (no special handling)

2. Fix Date Format:

   • Convert all dates to YYYY-MM-DD (accepted by every import)
   • Excel: Format column as YYYY-MM-DD, then export
   • Spreadsheet trick: Change format first, then export CSV

3. Fix Amount Format:

   • Remove currency symbols: change "$1,000.50" to "1000.50"
   • Remove commas: change "1,000" to "1000"
   • Decimals are fine: "1000.50" is correct
   • Use negative sign for negative amounts: "-1000.50"

4. Check for Hidden Characters:

   • Amounts may have extra spaces: " 100 " instead of "100"
   • Open CSV in text editor
   • Look for spaces before or after values
   • Delete spaces

5. Use Find & Replace:

   • In spreadsheet app, use Find & Replace
   • Find: "$", Replace: "" (removes all $ signs)
   • Find: ",", Replace: "" (removes all commas)
   • Export again after cleanup

# Issue 4: "Duplicate Entry" or "Record Already Exists"

# Symptoms

• Import rejected because record exists
• "Duplicate invoice number"
• "Vendor already exists"

# Solutions

1. Check for Existing Data:

   • Search Light for the record being imported
   • Go to Transactions and search by date/amount
   • Go to Vendors and search by name
   • If found, remove from CSV before reimporting

2. Deduplication:

   • Check CSV for duplicate rows
   • Sort by key field (invoice number, vendor name)
   • Look for identical records appearing twice
   • Delete one of the duplicates

3. Watch for ID Mismatches:

   • If your CSV uses a different key field (for example a new ID system) than what's already in Light
   • Light can't match your rows to existing records by that field
   • Remove records from CSV that already exist under a different ID, to avoid creating duplicates

4. Remove Existing Records Before Re-importing:

   • If some records in your CSV already exist in Light, remove those rows first
   • Imports are create-only, so re-importing a record that already exists is either rejected as a duplicate or creates a second copy
   • Split your CSV so only genuinely new records are imported

# Issue 5: "Invalid Date" or "Date Out of Range"

# Symptoms

• Error: "Invalid date format"
• Only certain rows rejected for date

# Solutions

1. Check Date Format:

   • Use YYYY-MM-DD, which works for every import
   • January must be "01", not "1"
   • September 15, 2024 should be "2024-09-15"
   • Bank transaction and bill/invoice imports also accept DD/MM/YYYY, DD.MM.YYYY, and DD-MM-YYYY
   • Pick one of those formats rather than the US-style MM/DD/YYYY

2. Excel Export Gotcha:

   • Excel may have dates in its native format
   • When exporting, specify CSV and verify format
   • Open CSV in text editor to check format
   • If dates don't look like YYYY-MM-DD, reformat

3. Fix Format in Text Editor:

   • Open CSV in text editor
   • Use Find & Replace to fix format if needed
   • If your dates are day-first (DD/MM/YYYY), find \d{2}/\d{2}/\d{4} and replace with $3-$2-$1 to convert to YYYY-MM-DD
   • Double-check the result, because swapping the capture groups in the wrong order silently swaps the day and month
   • Or manually edit problem rows

# Issue 6: "Invalid Amount"

# Symptoms

• Error: "Invalid amount"
• Some rows rejected for amount field

# Solutions

1. Check Amount Values:

   • For journal entries, an entry is only rejected if every line in it has a zero amount. Individual zero-amount lines within an otherwise non-zero entry are fine
   • Check that amounts are actual numbers

2. Fix Amount Format:

   • Remove currency symbols ($, €, ¥)
   • Remove commas: "1,000" becomes "1000"
   • Decimals are okay: "1000.50" is fine
   • Negative amounts: "-100.50" is accepted

3. Check for Text:

   • Amounts can't be text like "one hundred"
   • Must be numeric: 100
   • Check for "N/A" or other text
   • Replace with actual number or delete row

4. Sign Conventions:

   • Light validates journal entries on debit-versus-credit structure, rather than on whether an amount is positive or negative
   • As a matter of good practice, many teams still record expenses as negative and income as positive for readability, but this is a convention, not a system requirement

5. Decimal Points:

   • Use period (.) for decimal, not comma
   • "1000.50" is correct
   • "1000,50" reads as 100050, since commas are treated as thousands separators and removed

# Issue 7: "Invalid Vendor/Customer" or "Reference Not Found"

# Symptoms

• Error: "Vendor not found" or "Account does not exist"
• "Invalid reference" error
• Foreign key reference is rejected

# Solutions

1. Create References First:

   • If importing transactions with vendor references
   • Vendor must exist before transaction can reference it
   • Create vendors first: export vendor template and import
   • Then import transactions

2. Check Reference Names:

   • Vendor names must match exactly
   • "Acme Corp" not "Acme" or "Acme, Inc."
   • Matching is case-sensitive
   • Search for vendor in Light first

3. Verify IDs Match:

   • If using vendor IDs instead of names
   • IDs must match IDs in Light
   • Check that you're using the same ID system
   • Export existing data to see ID format

4. Create Missing Records:

   • For vendors not yet in Light
   • Create them manually or via separate import
   • CSV: name, email, phone, address
   • Do this before importing related transactions

5. Use Natural Keys:

   • Bill imports match vendors by name, so the vendor name in the CSV must match an existing vendor exactly, including case
   • A bill import matches vendors that already exist in Light, so create any missing ones first from the Vendors page, then re-run the import

# Issue 8: "Row Rejected" or "Validation Error"

# Symptoms

• Error shows specific row number
• "Row 15 rejected"
• No clear explanation of what's wrong

# Solutions

1. Review Error Details:

   • Expand the failed row's error cell in the import results to see the specific problem
   • Or download the Error report for the import to see every failed row and why
   • It might say "Invalid amount in row 15, column 4"

2. Check the Specific Row:

   • Open CSV and go to the numbered row
   • Look at each column's value
   • Compare against other rows
   • One column likely has wrong format

3. Test Row by Itself:

   • Create CSV with just headers and problem row
   • Import just this row to isolate issue
   • Error message should clarify the problem
   • Fix and add back to full import

4. Compare with Template:

   • Use Light's template CSV
   • Compare your problem row to template
   • See how template row is formatted
   • Match your data to template format

5. Manual Entry:

   • For problematic rows, enter manually
   • Remove row from CSV
   • Import CSV without that row
   • Manually create the problematic record

# Issue 9: Import Completes but Data Missing

# Symptoms

• Import shows "successful" but records don't appear
• No error message but can't find imported data
• Some rows imported, others silently failed

# Solutions

1. Search for Imported Data:

   • Data may have imported to unexpected location
   • Search by amount, date, or reference
   • Narrow date range to import date
   • Might be in a different account than expected

2. Check Filters:

   • Applied filters may be hiding imported data
   • Click Filters and remove all filters
   • Expand date range
   • Re-run search

3. Verify Import Completed:

   • Open the import history on the page you imported into (for example Journal entries or Bills)
   • Each import is listed with its filename, status, and an error count
   • Download the error report to see which rows failed and why
   • Check if all rows were successfully imported

4. Review Import Summary:

   • After import completes, review summary
   • Summary shows rows imported vs. rows failed
   • If some rows failed, see details why
   • Fix those rows and re-import

5. Check Posting Status:

   • Imported journal entries are created as drafts unless you chose to post them during import
   • Filter the Journal entries page by the Draft status to find them
   • Bill imports use the status column in the CSV (draft, posted, or paid) to determine each record's status

# Issue 10: "File Too Large" or "Upload Timeout"

# Symptoms

• Error: "File too large"
• Upload times out
• Can't import large data sets

# Solutions

1. Split Large File:

   • Divide CSV into multiple smaller files
   • Import one batch at a time
   • Combine by date range or record type

2. Remove Extra Columns:

   • Delete columns you don't need
   • Each extra column adds to file size
   • Keep only required columns
   • File size will be smaller

3. Try a Different Browser or Clear Your Cache:

   • Browser may timeout on large uploads
   • Clear browser cache first
   • Try a different browser
   • Desktop browser handles larger files than mobile

4. Use Smaller Batch:

   • Instead of importing all historical data
   • Start with recent data (last 6 months)
   • Import historical data in separate batches later
   • This also lets you validate as you go

5. Contact Support:

   • For very large imports
   • Light support can help you find the best way to get your data in
   • Provide the file and explain your situation

# Issue 11: "Entry Not Balanced" or Journal Entry Structural Errors

# Symptoms

• Error: "Entry not balanced"
• A line is rejected for having both a Debit and a Credit value, or neither
• Entire entry rejected even though most lines look correct

# Solutions

1. Check That Debits Equal Credits:

   • For each Entry Id, total debits must equal total credits
   • Add up all Debit values and all Credit values for the entry
   • If they don't match, the entry will be rejected

2. Use Exactly One Column Per Line:

   • Each row needs an amount in exactly one of Debit or Credit
   • A row with values in both columns will be rejected
   • A row with values in neither column will also be rejected

3. Keep At Least One Non-Zero Line:

   • An entry is rejected if every line in it has a zero amount
   • This is different from a single zero-amount line within an otherwise non-zero entry (which is allowed)
   • Make sure at least one line in the entry has a non-zero amount

4. Group Rows by Entry Id Before Importing:

   • Sort your CSV by Entry Id so all lines belonging to one entry are easy to review together
   • Manually total the Debit and Credit columns for each Entry Id before importing

# Issue 12: Lines in the Same Entry Don't Match

# Symptoms

• Error: "Mismatching value" for a field within an entry
• Some lines of a multi-line journal entry are rejected while others succeed
• Import fails after a copy-paste of entry lines

# Solutions

1. Check Shared Fields Across Lines:

   • All rows sharing the same Entry Id must have identical Date, Ledger name, Currency, Business Partner Id, and Business Partner Name
   • These fields describe the entry as a whole, not the individual line
   • The Account Code (posting account), by contrast, is validated per line and can legitimately differ from row to row within the same entry, so leave a differing Account Code alone unless it's actually wrong

2. Watch for Copy-Paste Slips:

   • This error is common after copying one line to create additional lines
   • Double-check that the shared fields weren't accidentally edited on only some rows

3. Fix the Inconsistent Rows:

   • Open the CSV and filter/sort by Entry Id
   • Compare the shared fields across all rows for that Entry Id
   • Correct any row where a shared field doesn't match the others

# Issue 13: Account Code Rejected Even Though the Account Exists

# Symptoms

• Error: "Invalid account code" even though the code is a real, existing account
• Import fails only for entries that post to certain accounts (for example totals or summary-level accounts)

# Solutions

1. Check the Account Type:

   • Journal entry imports post to standard, postable accounts, rather than header or summary-level accounts
   • An account code can exist in your chart of accounts and still be rejected if it's the wrong type

2. Use a Leaf Account:

   • Post to a specific, postable account rather than a parent/summary account
   • Check your chart of accounts to confirm which accounts are postable

3. Update the Account Code Column:

   • Replace the summary account code in your CSV with the correct postable account code
   • Re-run the import

# Issue 14: Delimiter or Encoding Misdetected

# Symptoms

• Error: "Column not found" even though the header row looks correct when opened in a spreadsheet app
• Headers or values appear garbled, merged into one column, or contain unexpected characters

# Solutions

1. Check Your Delimiter:

   • Light automatically detects whether a file uses commas or semicolons as the delimiter, based on the header row
   • A file using tabs, pipes, or another delimiter can be misread, causing headers to be misinterpreted
   • Re-save the file using a comma or semicolon as the delimiter

2. Check Your Encoding:

   • Light auto-detects the file's character encoding
   • A file saved in an unusual encoding can be mis-detected, garbling headers or values
   • Re-save the file as UTF-8 to avoid detection issues

3. Open the Raw File in a Text Editor:

   • Spreadsheet apps can mask delimiter and encoding issues by rendering the file correctly regardless of the underlying format
   • Open the actual CSV in a plain text editor to see the real delimiter and check for garbled characters
   • Fix any issues found, then re-export

# Issue 15: Row Has Too Few Columns

# Symptoms

• Row rejected with no obvious data problem, even though every value looks correct
• A row that looks fine when opened in a spreadsheet still fails

# Solutions

1. Check for Missing Trailing Commas:

   • Every row must have the same number of fields as the header row, including empty trailing fields
   • A row that's missing its final, empty values (with no trailing commas) will be rejected
   • This is the opposite of the extra-columns case: here the row is short

2. Open in a Text Editor, Not a Spreadsheet:

   • Spreadsheet apps often hide missing trailing commas by displaying empty cells normally
   • Open the CSV in a plain text editor and check that each row has the same number of commas as the header

3. Pad Short Rows:

   • Add trailing commas to any row that's missing values for the last column(s)
   • Re-export or manually fix the file, then retry the import

# Issue 16: Header Mapping Failed (Vendors / Chart of Accounts)

# Symptoms

• Whole-file import failure for a Vendors or chart of accounts import
• The whole file fails at once, with no specific row or column called out

# Solutions

1. Understand the Cause:

   • Vendors and chart of accounts imports use AI to automatically map your column headers, since exact header names aren't required
   • If that automated mapping process fails, the entire file fails to import, rather than a single row

2. Retry the Import:

   • Since this is tied to the automated mapping step rather than your data, retrying the import can clear a transient failure

3. Simplify Your Headers:

   • If retrying doesn't help, try using column headers that closely match Light's expected field names
   • Clearer, more standard headers make automatic mapping more likely to succeed

4. Contact Support:

   • If the error persists across retries, contact Light support with the file and error message

# Related Articles

• Common issues and solutions
• Bank connection troubleshooting
• Integration sync issues
• Contacting Light support