Skip to content
Alternative Finance

Lending software · Platform operators and analysts

Loan data validation tool for CSV imports

This loan data validation tool checks a pasted CSV against a list of required columns and a set of rules: every required column present, no blank required cells, numeric columns that parse, factor rates between 1.00 and 2.50, no negative amounts or days past due, two-letter state codes and unique ids. It reports pass or fail, the issues by line and column, and a downloadable issue list. It is for platform operators, analysts and anyone loading data into a servicing system, a dashboard or a reporting file. The thing to watch is that it validates structure and ranges, not truth: a plausible wrong number passes.

Validation result
Failed
Rows checked
5
Missing required columns
None
Issues found
5
Rows with issues
5

Checks: required columns present, no blank required cells, numeric columns parse, factor between 1.00 and 2.50, no negative amounts or days, two-letter state codes, unique ids. Nothing leaves your browser.

Issues
LineColumnProblem
3balanceBlank required value
4fundedNot a number: "eighty"
5factorFactor 0.9 outside 1.00–2.50
6days_past_dueNegative days past due
—idDuplicate id 1001 appears 2 times

Formula

Missing required columns = required list − header row (case-insensitive, trimmed). Rows checked = data rows after the header.

Per-cell checks: blank required value; non-numeric value in a numeric column (funded, factor, balance, days_past_due, amount, rate, revenue, after stripping $ and commas); factor outside 1.00–2.50; negative funded, balance or amount; negative days_past_due; state not two letters.

Per-row check: cell count equals header count. Per-file check: each id appears once. Validation result = Passed only if there are no missing columns and no issues; Rows with issues counts distinct line numbers (duplicate-id issues are file-level and count as one).

Worked examples

Example 1: the sample file (five rows, five issues)

Required columns: id, funded, factor, balance, days_past_due, state. The header has all six, so missing columns is None. Line 2 (id 1001) is clean. Line 3 (id 1002) has an empty balance cell: balance is required, so the tool records Blank required value; the numeric check is skipped for empty cells, so there is no second issue. Line 4 (id 1003) has funded = eighty: the numeric check records Not a number: eighty; because the value is not a number the negative-amount check does not fire. Line 5 has id 1001 again and factor 0.9: the factor check records Factor 0.9 outside 1.00–2.50, and the duplicate is noted at the end. Line 6 (id 1005) has days_past_due of −3: Negative days past due. After all rows, the id map shows 1001 twice, recorded as Duplicate id 1001 appears 2 times with the line shown as a dash. Totals: rows checked 5; issues found 5; rows with issues 5 (lines 3, 4, 5, 6 and the dash); result Failed. The issues table lists each with its line and column, and the download is the same list as CSV so the fixes can be assigned.

Example 2: a clean file that still fails on a missing column

Change the required columns to id, funded, factor, balance, days_past_due, state, revenue and paste a file whose header is id,funded,factor,balance,days_past_due,state with five clean rows. The header lacks revenue, so missing required columns shows revenue and the result is Failed even though issues found is 0 and rows with issues is 0. The tool treats a missing column as fatal because the downstream import would either reject the file or silently fill the column with blanks. The fix is in the export, not the file: add the column to the query. If the column is genuinely optional for this import, remove it from the required list; the list is the contract between the exporting system and the importing one, and this is where to state it.

Example 3: an unquoted dollar amount and a spelled-out state

Paste a file whose line 2 is 1001,$50,000,1.30,21000,0,New York. The CSV parser splits on commas outside quotes, so $50,000 becomes two cells, $50 and 000, and the row has seven cells against a six-column header. The tool records a structure issue, Expected 6 cells, found 7, and then checks each column by position: funded is now $50, which parses as 50 after stripping the dollar sign and passes; factor is 000, which parses as 0 and is outside 1.00–2.50; balance is 1.30; days_past_due is 21000; state is 0, which fails the two-letter test; the final cell, New York, has no header and is ignored. One formatting error produced four issues on the line. Quote the amount ("$50,000") or export without separators, and use the two-letter postal code. The lesson is that the first issue on a line is often the cause and the rest are symptoms; fix structure issues first and re-run.

What the validator checks, and what it does not

Data imports fail in a small number of predictable ways, and the loan data validation tool checks for each of them. Structure: the header is parsed, the required list is compared with it, and each row's cell count is compared with the header's. Completeness: required cells may not be blank. Type: columns named funded, factor, balance, days_past_due, amount, rate and revenue must parse as numbers once dollar signs and commas are removed. Range: factor rates must lie between 1.00 and 2.50, amounts and days past due may not be negative. Format: a state column must hold two letters. Uniqueness: id values must not repeat. The rules are deliberately conservative; a factor of 2.6 or a negative balance from an overpayment may be legitimate in your system, and in that case the issue is a prompt to confirm rather than a rejection.

What it does not check is as important. It does not know whether $50,000 was really funded, whether the factor matches the contract, whether days past due is computed by the same rule across rows, or whether the state is the merchant's or the guarantor's. It does not check dates, because date formats vary by source; it does not cross-check columns (a balance larger than funded × factor passes); and it does not look up ids against another file. For those checks the right tool is the importing system's reconciliation or a query against the source database. The loan portfolio dashboard builder is the usual next step after a file passes; it consumes the same columns.

Rules applied by column
ColumnRuleTypical cause of failure
Any requiredNot blankExport query left-joins a table with no match
funded, balance, amountNumeric, not negativeText placeholders (TBD, n/a), unquoted thousands separators
factorNumeric, 1.00–2.50Entered as percent (30) or as payback amount
days_past_dueNumeric, not negativeNegative values used to mean paid ahead
rate, revenueNumericPercent signs, currency text
stateTwo lettersFull state names, country codes, blanks with spaces
idUniqueRenewals sharing the original id; export joined to a payments table

Reading the issues list and fixing at the source

The issues table has three columns: line, column and problem. Line numbers count the header as line 1, so the first data row is line 2, which matches what a spreadsheet shows. Sort the download by column to see whether a problem is systemic (every state cell is a full name) or isolated (one typo). Systemic issues are export problems and should be fixed in the query or the mapping; isolated issues are data-entry problems and should be fixed in the source record so the next export is clean. Fixing the CSV by hand solves today's import and guarantees next week's failure.

  • Structure issues first. A row with the wrong cell count shifts every later column, and the type and range failures on that row are consequences. Fix the quoting or the separator and re-run.
  • Blank required values are usually a join problem in the export, not missing data; check whether the source record has the value.
  • Not a number in a numeric column often means a sentinel value such as pending or N/A. Decide what the import should do with those rows and encode it (blank, zero or exclusion) consistently.
  • Duplicate ids on a loan file often mean a renewal reused the original id, or the export joined a one-to-many table. Both make every downstream KPI wrong, because balances are double counted.
  • Factor outside range is sometimes a loan file where factor was filled with the interest rate; a term loan at 14 percent should show factor as total payments ÷ principal, not 14.

Why clean loan data matters beyond the import

Loan data feeds reporting that has consequences. Investor and facility reporting: borrowing-base certificates and covenant calculations use balance, delinquency and concentration from the same files; a duplicated id overstates the balance and a blank days_past_due understates delinquency, and either can be a misrepresentation to a lender. Regulatory reporting: lenders covered by the CFPB's small business lending data rule must collect and report defined data points on applications, with validation rules published for the submission; the habit of validating before submission starts with files like these. Reserves: expected credit loss models trained on files with inconsistent days-past-due conventions produce allowances that are wrong in ways nobody can see. Servicing: an import that loads a wrong balance leads to wrong payoff letters and wrong debits, which generate ACH returns and complaints.

Standardization helps more than any validator. Use two-letter postal codes for state, which the ISO 3166-2:US standard also adopts; use NAICS codes alongside industry labels so that concentration can be compared with public data; store amounts as plain numbers with no separators; store dates in ISO format (YYYY-MM-DD) so they sort as text; and define days past due in a data dictionary that states whether it is calendar days from the oldest missed due date or missed-payment count. The CSV format itself is only loosely standardized, which is why quoting of fields containing commas is the most frequent cause of structure failures.

Security and handling of loan files

A loan file contains business names, amounts, delinquency and often owner names and bank details. The validator runs in the browser and sends nothing anywhere, but the file still exists on a workstation, in email, or in a shared folder. The FTC's cybersecurity guidance for small businesses and the NIST Cybersecurity Framework set out the basics that lenders' capital providers and bank partners expect: encrypt files at rest and in transit, limit who can export, log exports, strip columns that the recipient does not need, and delete working copies. A file sent to an investor for a borrowing-base certificate should carry ids, balances and delinquency, not bank account numbers.

  1. 1

    Define the export

    Columns, formats and the required list, in a written specification shared by the exporting and importing teams.

  2. 2

    Validate every file

    Paste into the validator or run the same rules in a script; do not import a failed file.

  3. 3

    Fix at the source

    Correct the record or the query, not the CSV, and re-export.

  4. 4

    Reconcile after import

    Compare record counts and total balance between source and destination; a validator cannot catch a file that is clean but incomplete.

  5. 5

    Retain the issue reports

    They document data quality over time and are evidence of control for auditors and capital providers.

Once a file passes, the next tools in the chain are the loan portfolio dashboard builder for KPIs, the loan portfolio concentration calculator for facility covenants and the underwriting decision matrix builder if the file is a lead or application set rather than a funded book. The cost of the data that fills these columns in the first place is in the lending API pricing calculator.

Questions people ask

What does the loan data validation tool check?

Required columns present, no blank required cells, numeric columns that parse (funded, factor, balance, days_past_due, amount, rate, revenue), factor between 1.00 and 2.50, no negative amounts or days past due, two-letter state codes, consistent cell counts per row, and unique ids. It reports pass or fail with an issue list by line and column. Nothing is uploaded.

Why does my file fail with no issues listed?

Because a required column is missing from the header. Missing columns are reported separately from row issues and make the result Failed on their own. Check the spelling and case of the header against the required list; the comparison trims spaces and ignores case but does not match synonyms such as amount for funded.

Can I change the numeric and range rules?

The required-column list is yours to set; the numeric columns and ranges are fixed in this version. If your data legitimately has factors above 2.5 or negative balances, treat those issues as confirmations rather than errors, or rename the column so the rule does not apply and validate the range in your own system.

How should I format dollar amounts in a CSV?

As plain numbers with no currency symbol or thousands separator: 50000, not $50,000. If the export must include separators, quote the field so the comma is not read as a delimiter. The validator strips dollar signs and commas inside a single cell, but an unquoted comma has already split the cell before it gets there.

Does the validator check dates?

No. Date formats vary by source, so date columns are passed through. Use ISO format (YYYY-MM-DD) in exports so dates sort correctly as text and so downstream tools such as the payment schedule generator can read them without conversion.

Sources and further reading

Related tools and guides

More lending software

Updated October 12, 2026 · By Alternative Finance editorial team

Next step

Turn the estimate into real offers

One request, several funding partners, side-by-side payback. Nothing is binding until you accept an offer.

  • Soft inquiry to request offers; a hard pull only if you proceed with a funder.
  • Decisions in as little as 24 hours for many products; funding often within days.
  • Use our calculators to check the true cost before you accept anything.

See what you may qualify for

Takes about a minute. Requesting offers does not affect your credit score. Free, no obligation.

We are not a lender or broker. Requests are forwarded to funding partners free of charge. How the site is paid.