Raindance and the Dark Art of Putting a Minus Sign in Column 140

Modern software has trained us to expect data formats with luxuries such as named fields, explicit structure, machine-readable schemas and the occasional error message that does not read like a ransom note.

Then a public-sector ERP hands you a spreadsheet saying that the credit marker goes in column 140.

Not a property called credit. Not a signed decimal. Column 140. One character. A minus sign if the row is credit, a space if it is debit. The amount next door is the absolute value, expressed using an exact number of positions, and its decimal representation is dictated by whichever Raindance import you are currently trying to appease.

This has been my personal integration hell.

I have built two open-source Frends task packages in this particular mess: MomentumToRaindance, which turns Momentum accounting data into customer invoices, and PigelloSIERaindance, which turns SIE vouchers from Pigello into Raindance entries. The implementations are not the interesting part. I am going to replace a fair amount of that code anyway, as is tradition after learning exactly which assumptions production intends to punish.

The interesting part is the format itself: how to understand it, how to make it deterministic, and how to prove that the invisible spaces are in the correct invisible places.

The request that sounded harmless#

On paper, the job is one sentence.

Take accounting data from system A and import it into system B.

Excellent. Both systems contain numbers. Computers have been moving numbers since before product managers discovered the word “AI.” This should be fine.

System A may even be civilised. Momentum exposes structured data through an API. Pigello exports SIE, an old format but at least one with recognisable records and an existing ecosystem. The source data contains invoices, voucher dates, accounts, dimensions, VAT, row text and amounts.

Raindance wants a text file that looks approximately like somebody flattened a database record with a steamroller:

1K 1510      9997                                                        870       EK23                                              1530000-    Some row text

That line is not meant for human eyes. Its meaning comes from position:

11  3         13        23        33        43        53        63        73        83
2|  |         |         |         |         |         |         |         |         |
3K  konto     ansvar    verksamhet aktivitet objekt    projekt   fri       motpart   källa

Calling it “plain text” is technically correct in the same way that calling a land mine “garden hardware” is technically correct.

The title’s particular act of accounting vandalism looks like this in the invoice K layout:

1field             positions      encoded value
2amount            125–139        "        1530000"
3credit marker     140            "-"

This is a fixed-width positional format. There is no delimiter character to split on. The delimiter is how many positions you have already consumed. Once encoded, those positions must still land on the expected bytes. A field is defined by a one-based start position and a length. Empty fields are not absent; they are stretches of spaces which preserve the position of everything after them.

Delete one space and motpart becomes källa. Add one character to the row text and a periodisation key starts three fiscal years into the future. The file can remain perfectly readable while becoming semantically radioactive.

First, stop looking for the object model#

The natural instinct is to model an invoice and then serialise it. That works for JSON because JSON has structure. Here, the record layout is the structure.

Raindance distinguishes records by the first character or characters. The records currently emitted by the Momentum integration are:

1S  customer
2H  invoice header
3R  invoice row
4K  accounting row

The SIE flow uses an H header followed by one or more K accounting rows. A file is therefore closer to a tiny line-oriented protocol than a document:

1S -> H -> (R -> K+)+

or:

1H -> K+

That distinction matters. A valid field in an invalid record sequence is still an invalid file. So is a valid string encoded using the wrong bytes. So is a line with the right visible contents but the wrong trailing spaces.

The useful mental model is not “export some text.” It is “construct a byte-addressed protocol frame for an accounting system.” Suddenly the spreadsheet starts looking less like documentation and more like a packet layout written by someone who really trusted Microsoft Office.

The spreadsheet is the schema, unfortunately#

Where do you start attacking a format like this?

Not in the string interpolation. Start by turning the spreadsheet into an explicit schema. For every record type, write down only the facts needed to construct and validate it:

1record type
2field name
3one-based start
4length
5requirement
6formatting rule

Then calculate the end of every field:

1end = start + length - 1

Sort by start and reject overlaps unless the format explicitly defines one. Record the final line length. Mark gaps as reserved space rather than pretending they do not exist. A gap is still bytes you must emit.

The first Raindance mapping below ends its K record with 166 positions of “other available space.” That is not an invitation to omit the space. It means the record extends through column 359. The void has a length requirement.

A robust writer begins with a buffer already filled with spaces:

1record = 359 spaces
2put(start: 1,   length: 1,  value: "K")
3put(start: 3,   length: 10, value: account)
4put(start: 125, length: 1,  value: creditMarker)
5put(start: 126, length: 15, value: amount, align: right)

Every put operation should enforce the contract:

• Convert the one-based spreadsheet position to a zero-based buffer offset exactly once.
• Normalise carriage returns and line feeds inside values.
• Apply the field’s alignment rule.
• Reject or deliberately truncate values longer than the field.
• Reject writes beyond the record boundary.
• Encode the final record and verify its byte length, not merely its character count.

That last point is where pleasant Unicode abstractions meet payroll software carrying a chair.

Characters are not bytes, except when the ERP says they are#

The Momentum import expects ISO-8859-1, commonly called Latin-1. Swedish names and addresses therefore need to become the exact single-byte values Raindance expects. If the generated file is opened as UTF-8, å, ä and ö may look broken even when the byte stream is correct for the receiver.

This is more than an editor inconvenience. Fixed-width layouts are often byte layouts disguised as character layouts. UTF-8 encodes an ASCII character such as K as one byte, but ö as two. A 40-character customer name can therefore exceed a 40-byte field even though the runtime reports a string length of 40.

Latin-1 makes the supported Swedish characters one byte each, which preserves the positions, but it introduces another obligation: decide what happens when the source contains a character Latin-1 cannot represent. An emoji in an invoice reference is no longer whimsy. It is a serialisation policy decision.

The safe order is:

1. Normalise the source value.
2. Validate that every character is representable in the target encoding.
3. Truncate or reject according to an explicit business rule.
4. Pad and align the field.
5. Encode the complete line.
6. Assert the expected byte length.
7. Append the required CRLF line ending.

Do not let a library silently replace an unsupported character with ?. Silent substitution is how an organisation number becomes a support ticket three weeks later.

Accounting signs, now sold separately#

Numbers have their own failure mode because these formats do not merely serialise a decimal. They deconstruct it.

In the SIE K layout, the credit marker lives at position 125 and the amount begins at 126. In the invoice-accounting K layout, the amount begins at 125 and the credit marker lives at 140. For an invoice row, the amount begins at 63 and its credit marker lives at 78.

Same target ERP. Similar concept. Different positional contract.

This is why the phrase “Raindance format” is dangerously reassuring. There is no one universal row you can generalise from after seeing it twice. There are record layouts, import routines and local agreements. Reuse the mechanism for placing fields, but keep each schema explicit.

The underlying conversion should be boring and testable:

1source amount:  -1234.56
2magnitude:       123456
3credit marker:   -

For a format with two implied decimal places, multiply the absolute value by 100 using decimal arithmetic, round according to an agreed rule, and render with invariant digits. Then place the sign in its own field. Never inherit numeric formatting from the machine’s current locale. Render exactly the representation prescribed by the selected record layout. The spreadsheet, not the operating system, wins.

This also needs accounting semantics, not just amount < 0. In SIE, debit and credit may be conveyed through the sign. In API data, an amount may arrive with a separate debit flag. When accounting rows are grouped, signs must be applied before summation. Zero-value groups should disappear only if the receiving business rule says so.

The minus sign is one byte. Determining whether it belongs there is the entire integration.

Tables do not map themselves#

The most exhausting work is not writing padding code. It is translating between two unrelated descriptions of reality.

Momentum might call something a customer identity, an official number, a distribution, a ledger row or an account coding string. Raindance wants Kundidentitet, Person-/organisationsnummer, Motpart, Konto, Ansvar, Verksamhet and several optional dimensions with ten characters each.

SIE contributes voucher records and dimension identifiers. A local agreement then decides that dimension 1 is Ansvar, dimension 2 is Objekt, dimension 3 is Motpart, dimension 4 becomes row text, and EK23 is a fixed source code. None of those transformations is difficult in isolation. Together they form a dense pile of tiny obligations:

• Which source field wins when there are two possible references?
• Does a personal identity number retain its separator?
• When is a VAT number constructed from an organisation number?
• Which customer classes map to PRIV, FTG or ÖVR?
• Which account dimensions are mandatory for a particular account?
• Should repeated revenue rows with identical coding be grouped?
• Are rounding rows transferred, ignored or posted separately?
• Is an empty field spaces, zeroes, a fixed default or a rejected invoice?
• Does “optional” mean optional to Raindance, optional for this municipality, or not currently supplied by the source API?

Once those decisions are explicit, padding is the easy part. Until then, the serialiser is merely business policy hiding inside string interpolation.

This is not an algorithmic challenge. It is contract reconciliation. The complexity comes from the number of independent rules and the cost of violating any one of them.

An ERP consultant may describe this as dark magic. It is not dark magic. Dark magic would at least have coherent lore.

It is just fucking annoying.

How to test invisible structure#

These integrations need tests at three levels.

First, test fields. Given a value and a schema entry, verify truncation, padding, alignment, unsupported characters and numeric conversion. Boundary tests matter: empty, exactly full, one character too long, maximum amount, negative amount and zero.

Second, test records. Render a known invoice-accounting K row and assert all of these independently:

1record byte length == schema byte length
2record[0]          == "K"
3record[2..12]      == expected account field
4record[124..139]   == expected amount field
5record[139]        == expected credit marker

The slices above use zero-based, end-exclusive notation. The spreadsheet does not. Mixing those coordinate systems casually is an excellent way to spend an afternoon moving a minus sign between two equally plausible spaces.

Third, test complete files as bytes. A golden fixture should include Swedish characters, long text, debit and credit rows, absent optional dimensions, multiple record types and exact CRLF endings. Compare byte for byte and print a useful positional diff on failure:

1record 4, position 140
2expected: 0x2D '-'
3actual:   0x20 ' '

A normal text diff is not enough. Trailing whitespace is the data, and many diff viewers exist specifically to pretend trailing whitespace does not exist.

I also want a validator that reads the generated bytes back using the same declarative schema. It cannot prove the accounting is correct, but it can prove structural invariants before a file leaves the integration platform:

• Every line has a recognised record marker.
• Every line has the exact byte length for that marker.
• Required fields are populated.
• Numeric fields contain only permitted bytes.
• Credit markers contain either - or space.
• Reserved positions remain spaces.
• The record sequence is legal.

The target system should not be the first parser to encounter the file. Production is an expensive hex editor.

The aha moment#

That makes the correct abstraction obvious: this is a compiler.

You are not “formatting a string.” You are compiling structured source data into a legacy record layout. The compiler has a symbol table—the mapping between source concepts and accounting dimensions. It has a target ABI—the starts, lengths, encoding and line endings. It needs type checking, range checking and deterministic output. The ERP import is the runtime, except its diagnostics may be a Swedish modal dialog last redesigned when Internet Explorer was considered a strategic platform.

The analogy also draws the boundary for reuse. Reuse the fixed-width writer, encoding checks, amount formatters, schema validation and byte-level test tools. Do not bury every Raindance dialect behind one clever universal serialiser with seventeen boolean options. The layouts below disagree in meaningful ways. Make those differences visible as data.

Something like this is easier to audit than a cathedral of interpolated strings:

 1K_ACCOUNTING_SIE = [
 2  field("postmarkering",    1,   1, fixed="K"),
 3  field("konto",            3,  10, required=true),
 4  field("kreditmarkering", 125,  1),
 5  field("radbelopp",       126,  15, align=right),
 6  field("text",            142,  30),
 7]
 8
 9K_ACCOUNTING_INVOICE = [
10  field("postmarkering",    1,   1, fixed="K"),
11  field("konto",            3,  10, required=true),
12  field("belopp",          125,  15, align=right),
13  field("kreditmarkering", 140,  1),
14  field("radtext",         145,  30),
15]

The difference is no longer an implementation accident. It is reviewable documentation.

The post-mortem#

Fixed-width ERP files are archaic, but age is not their worst property. A fixed-width format can be perfectly reliable. It is deterministic, streamable, compact and easy to process in systems that do not want to discover XML namespaces before breakfast.

The real danger is that its schema lives in screenshots, spreadsheets, consultant memory and example files which are almost representative. The format has no field names in transit, weak self-description and little room for graceful evolution. Every unstated assumption becomes a byte somebody eventually has to debug.

So if you inherit one of these integrations, do not begin by admiring the existing string concatenation. Recover the contract:

1. Inventory every record type and legal sequence.
2. Transcribe starts, lengths and requirements into machine-readable schemas.
3. Clarify every numeric, sign, date, encoding and truncation rule.
4. Separate source-system mapping from fixed-width serialisation.
5. Validate record structure before delivery.
6. Keep byte-exact golden files for every important business case.
7. Treat every new spreadsheet revision as an API version, because that is what it is wearing business-casual clothing.

The task is not intellectually impossible. It is precision work performed against a contract whose type system is cell borders.

And yes, the spaces at the end matter.

Appendix A: SIE voucher layout#

Appendix B: Momentum customer-invoice layout#

Kundpost#

Fakturahuvud#

Bilaga#

Fakturarad#

Konteringsrad#