Reinsurance Bordereaux Format Conversion: Pitfalls & Fixes
On this page
- Key Takeaways
- Which Pitfalls Affect Reinsurance Bordereaux Format Conversion?
- What Should an Import Specification Say for Each Cedent File?
- Which Bordereaux Conversion Errors Should Be Fixed First?
- How Can Last Quarter's Bordereaux Files Be Tested for Conversion Errors?
- How Does Insurnest Help After Bordereaux Are Converted?
- Key Terms
- Sources
- Frequently Asked Questions
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.
| Pitfall | How it shows up | Detection rule | Fix |
|---|---|---|---|
| 1900 vs 1904 date system | Dates shifted by 1,462 days | Inception dates outside the treaty period | Read raw serials and set the source date system |
| Leading zeros dropped | 000457812 becomes 457812 | ID length differs from the cedent's spec | Import ID columns as text |
| 15-digit precision | Long IDs end in zeros or show as 4.40219E+18 | Distinct IDs fewer than rows; IDs ending 000 | Import as text; never re-save the CSV in Excel |
| Delimiter mismatch | Whole row in one column | Column count is not the same on every row | Declare the delimiter at import |
| Decimal comma locale | 1.234,50 read as text or 1.234 | Numeric parse rate below 100%; totals off 1,000 times | Declare decimal and thousands separators |
| Unquoted commas in fields | Address splits into two columns | Column count varies by row | Quote fields or ask the cedent to resend |
| Text encoding | Zürich becomes Zürich | Search for "Ã" sequences | Read as UTF-8; check for a byte order mark |
| XML namespaces | Parser finds zero premium elements | Rows parsed below the control count | Use namespace-aware parsing |
| XML nesting flattened | Policy premium repeated on each claim row | Premium total above the control total | Flatten 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 number | First 15 digits kept | Stored value | Shown as |
|---|---|---|---|
| 4402190000158734621 | 440219000015873 | 4402190000158730000 | 4.40219E+18 |
| 4402190000158734688 | 440219000015873 | 4402190000158730000 | 4.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.
| Setting | Rule | Example value |
|---|---|---|
| File format | Named per cedent and treaty | CSV |
| CSV delimiter | Declared, never auto-detected | Semicolon |
| Decimal separator | Declared per cedent | Comma |
| Encoding | Declared, with byte order mark noted | UTF-8 with BOM |
| Dates | Pattern or Excel date system stated | DD.MM.YYYY |
| Text columns | Every identifier forced to text | Policy, claim and cedent codes |
| Control totals | Rows, premium and claims sent with the file | 12,408 rows; EUR 3,912,440.18 |
| XML | Namespace URI and record level stated | Policy 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.
- Identity: truncated IDs, dropped zeros and shifted columns.
- Money: decimal locale and flattened XML.
- Period: date-system shifts, which move business between quarters.
- 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.
- Rows in the converted file equal rows in the source.
- Premium and claims totals equal the cedent's control totals.
- Every ID has the length in the cedent's specification, with no trailing 000 pattern.
- Every date falls inside the reporting period and treaty period.
- No field contains "Ã" or replacement characters.
- 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
- Microsoft Support: Date systems in Excel
- Microsoft Support: Keeping leading zeros and large numbers
- Microsoft Support: Opening CSV UTF-8 files correctly in Excel
- Microsoft Support: Change the character used to separate thousands or decimals
- IETF: RFC 4180, Common Format for CSV Files
- W3C: Namespaces in XML 1.0
- Insurnest: Reinsurance Bordereaux Reconciliation
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
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 →