Reinsurance

Reinsurance Bordereaux Format Conversion: Pitfalls & Fixes

On this page

Last reviewed: November 2026

The reconciliation shows policy 4402190000158730000 twice. The cedent says no policy has that number.

Both rows began as different 19-digit policy numbers. A spreadsheet turned them into one.

Reinsurance bordereaux format conversion is turning cedent files in CSV, Excel or XML into one consistent dataset.

Done well, it changes the format and nothing else: no value, date or identifier moves.

This guide gives you a pitfalls table with detection rules and fixes, a worked corrupted-ID example and a test plan for past files.

Key Takeaways

  • Import identifiers as text. Policy and claim numbers are labels, not numbers.
  • Declare delimiter, locale and encoding. Never let software guess them.
  • Check control totals after every conversion. Row counts and money must survive unchanged.
  • Fix identity and money errors first. Dates and names come next.

Which Pitfalls Affect Reinsurance Bordereaux Format Conversion?

Reinsurance bordereaux format conversion is affected by nine common pitfalls. Each has a detection rule you can automate.

PitfallHow it shows upDetection ruleFix
1900 vs 1904 date systemDates shifted by 1,462 daysInception dates outside the treaty periodRead raw serials and set the source date system
Leading zeros dropped000457812 becomes 457812ID length differs from the cedent's specImport ID columns as text
15-digit precisionLong IDs end in zeros or show as 4.40219E+18Distinct IDs fewer than rows; IDs ending 000Import as text; never re-save the CSV in Excel
Delimiter mismatchWhole row in one columnColumn count is not the same on every rowDeclare the delimiter at import
Decimal comma locale1.234,50 read as text or 1.234Numeric parse rate below 100%; totals off 1,000 timesDeclare decimal and thousands separators
Unquoted commas in fieldsAddress splits into two columnsColumn count varies by rowQuote fields or ask the cedent to resend
Text encodingZürich becomes ZürichSearch for "Ã" sequencesRead as UTF-8; check for a byte order mark
XML namespacesParser finds zero premium elementsRows parsed below the control countUse namespace-aware parsing
XML nesting flattenedPolicy premium repeated on each claim rowPremium total above the control totalFlatten each level to its own table and join by key

How do date-system errors appear in bordereaux files?

Date-system errors appear in bordereaux files as dates moved by exactly 1,462 days, about four years and a day.

Excel supports two systems. Microsoft's date systems guidance puts the gap between them at 1,462 days.

A 1 January 2026 inception read in the wrong system shows as 2 January 2030 or 31 December 2021. Both fail a treaty period check.

See date sequence anomalies for the wider date checks.

How do long policy numbers get corrupted in bordereaux conversion?

Long policy numbers get corrupted in bordereaux conversion when software stores them as numbers instead of text.

Microsoft's guide to keeping leading zeros and large numbers confirms 15 digits of precision.

Digits past the 15th become zero, and long numbers display in scientific notation. Leading zeros are removed too.

Here is the illustrative case from the opening, two different 19-digit policy numbers:

Original policy numberFirst 15 digits keptStored valueShown as
440219000015873462144021900001587344021900001587300004.40219E+18
440219000015873468844021900001587344021900001587300004.40219E+18

Two policies now share one ID. A join to last quarter's data attaches both premiums to one policy and none to the other.

The fix is upstream. Import the column as text, then check that distinct IDs equal rows for single-row-per-policy files.

How do delimiters and decimal commas break CSV bordereaux?

Delimiters and decimal commas break CSV bordereaux when a file built for one locale is read with another's settings.

A continental cedent may send semicolon-separated values with 1.234,50 meaning one thousand two hundred and thirty-four.

Read with English settings, the row lands in one column, or 1.234 becomes a value 1,000 times too small.

Excel uses the separators in regional settings by default, per Microsoft's separator guidance.

RFC 4180 says fields containing commas, quotes or line breaks should be in double quotes. Unquoted addresses shift columns.

How does character encoding damage names in bordereaux?

Character encoding damages names in bordereaux when UTF-8 text is read as ANSI, so Zürich becomes Zürich.

Microsoft's note on opening CSV UTF-8 files says Excel opens them normally if saved with a byte order mark.

Without one, import through Get Data from Text/CSV and choose UTF-8.

What goes wrong when XML bordereaux are flattened?

When XML bordereaux are flattened carelessly, parent values repeat on every child row.

A policy with USD 12,000 premium and three claims becomes three rows at USD 12,000. Premium shows USD 36,000, overstated by USD 24,000.

Namespaces cause the opposite fault. The W3C namespaces specification applies a default namespace to unprefixed elements.

A parser asking for "Premium" without that namespace finds nothing, and the file looks empty.

What Should an Import Specification Say for Each Cedent File?

An import specification for each cedent file should fix every setting that software would otherwise guess.

SettingRuleExample value
File formatNamed per cedent and treatyCSV
CSV delimiterDeclared, never auto-detectedSemicolon
Decimal separatorDeclared per cedentComma
EncodingDeclared, with byte order mark notedUTF-8 with BOM
DatesPattern or Excel date system statedDD.MM.YYYY
Text columnsEvery identifier forced to textPolicy, claim and cedent codes
Control totalsRows, premium and claims sent with the file12,408 rows; EUR 3,912,440.18
XMLNamespace URI and record level statedPolicy records, claims as a child table

Agree it once with each cedent and keep it with the treaty file.

If a file breaks it, ask for a corrected file under bordereau resubmission handling.

Which Bordereaux Conversion Errors Should Be Fixed First?

Bordereaux conversion errors that change money or identity should be fixed first, because they flow into shares and balances.

  1. Identity: truncated IDs, dropped zeros and shifted columns.
  2. Money: decimal locale and flattened XML.
  3. Period: date-system shifts, which move business between quarters.
  4. Names: encoding faults, which break matching but not amounts.

Field-level mapping is a separate job, covered in bordereau template mapping.

How Can Last Quarter's Bordereaux Files Be Tested for Conversion Errors?

Last quarter's bordereaux files can be tested for conversion errors by re-importing each one as text and comparing six results.

  1. Rows in the converted file equal rows in the source.
  2. Premium and claims totals equal the cedent's control totals.
  3. Every ID has the length in the cedent's specification, with no trailing 000 pattern.
  4. Every date falls inside the reporting period and treaty period.
  5. No field contains "Ã" or replacement characters.
  6. For XML, rows per policy match the claim count, and premium is counted once per policy.

Score the results by cedent, as in ceded data quality scores, and ask repeat offenders to change their export.

Persistent faults are rarely one file's problem. See bordereaux quality is a portfolio problem.

How Does Insurnest Help After Bordereaux Are Converted?

Reinsurance Bordereaux Reconciliation does not convert file formats or map fields. Conversion stays with your data team.

It calculates each reinsurer's share of premium and claims, and validates calculations against master and reference data.

Where a converted value differs from the bordereaux, master or settlement record, it flags the mismatch for investigation.

A truncated policy number may surface that way. Finding why it was truncated stays with your finance and data teams.

Key Terms

  • Date system: The start date Excel counts serial numbers from, 1900 or 1904.
  • Byte order mark: A marker at the start of a text file that signals its Unicode encoding.
  • Control total: A count or amount supplied with a file so the receiver can confirm nothing changed.

Sources

Frequently Asked Questions

Which pitfalls affect reinsurance bordereaux format conversion?

Reinsurance bordereaux format conversion is affected by Excel date systems, dropped leading zeros, 15-digit precision, delimiters, decimal commas, unquoted commas, text encoding and XML namespaces or nesting.

How do date-system errors appear in bordereaux files?

Date-system errors appear in bordereaux files as dates shifted by exactly 1,462 days, when a workbook using the 1904 date system is read as 1900, or the reverse.

How do long policy numbers get corrupted in bordereaux conversion?

Long policy numbers get corrupted in bordereaux conversion when Excel treats them as numbers, keeping 15 significant digits and turning the rest into zeros.

How do delimiters and decimal commas break CSV bordereaux?

Delimiters and decimal commas break CSV bordereaux when a semicolon or comma-decimal file is read with English settings, collapsing columns or making amounts text or wrong.

How does character encoding damage names in bordereaux?

Character encoding damages names in bordereaux when a UTF-8 file is read as ANSI, so Zürich becomes Zürich and name matching against master records fails.

What goes wrong when XML bordereaux are flattened?

When XML bordereaux are flattened carelessly, parent values such as policy premium repeat on every child claim row, overstating premium, or namespaced elements are missed.

Which bordereaux conversion errors should be fixed first?

Bordereaux conversion errors that change money or identity should be fixed first: truncated IDs, flattened XML, decimal locale and shifted columns. Dates come next, then encoding.

How can last quarter's bordereaux files be tested for conversion errors?

Last quarter's bordereaux files can be tested for conversion errors by re-importing each as text and comparing row counts, control totals, ID lengths, date ranges and encoding.

Hitul Mistry

Hitul Mistry

CEO, Insurnest

An InsurTech leader with more than a decade of experience across insurance and technology, focused on solving business problems with the help of technology. Has worked with brokers, insurance carriers, and reinsurance firms across the India, UAE, and US markets.

View LinkedIn profile →
ShareLinkedInX

Read our latest blogs and research

Featured Resources

Reinsurance

Bordereau Template Mapping: Onboard New Cedent Formats Fast

Bordereau template mapping as a reusable specification: header, field map, code tables, control totals and change log, so new cedent formats onboard cleanly.

Read more
Reinsurance

Ceded-Data Quality Scores: A Better KPI Than 'Report Submitted'

Ceded-data quality scores measure what matters: the accuracy, completeness, and timeliness of reinsurance data. Learn why 'report submitted' is no longer an adequate KPI.

Read more
Fraud & Anomaly Detection

Date Sequence Anomalies in India: 6 Timeline Violations AI Catches

Date sequence anomalies in medical documents expose forgery instantly. AI catches prescriptions dated before diagnoses and 5 other timeline violations in NSTP files.

Read more

Meet Our Innovators:

We aim to revolutionize how businesses operate through digital technology driving industry growth and positioning ourselves as global leaders.

circle basecircle base
Pioneering Digital Solutions in Insurance

Insurnest

Empowering insurers, re-insurers, and brokers to excel with innovative technology.

Insurnest specializes in digital solutions for the insurance sector, helping insurers, re-insurers, and brokers enhance operations and customer experiences with cutting-edge technology. Our deep industry expertise enables us to address unique challenges and drive competitiveness in a dynamic market.

Get in Touch with us

Ready to transform your business? Contact us now!

We respond within one business day.