What is a flat file? CSV, fixed-width and how flat files relate to EDI

Where a UK supplier meets one, a flat file sits at the edge of the ERP or accounting system: a list of orders it exports, or a file it reads back in. Retail customers that trade by EDI work in something stricter, a message built to a standard such as EANCOM, TRADACOMS or X12. The two can carry the same order. They are built very differently. (Flat file here means the data kind, as opposed to the hand tool.)

What is a flat file?

A flat file is data laid out as records, one after another, with nothing in the file linking them. Wikipedia describes the related flat-file database as "a tabular flat file in which each record is semantically independent". On the word itself: "The term flat loosely refers to data that is record-based and sequential yet lacks more complicated aspects such as nesting, relationships and metadata (with the exception of column headers)." Links between records are left to the reader: "Relationships can be inferred from the data, but the format does not provide special accommodations for relationships."

TechTarget reduces it to two dimensions. It calls the format a flat database "because there are only two dimensions, rows and columns, with no relationships to other data elements."

The W3C's model for tabular data does not use the words flat file, but it describes the same shape: "Tabular data is data that is structured into rows, each of which contains information about some thing." It also lists how such data moves about: "Tabular data is routinely transferred on the web in a variety of formats, including variants on CSV, tab-delimited files, fixed field formats, spreadsheets, HTML tables, and SQL dumps." The first three are the flat-file layouts below.

What are the main types of flat file?

Plain-text flat files are typically laid out in one of two ways. In a delimited file, a chosen character separates the fields. In a fixed-width file, each field takes up a set number of characters. Microsoft's SQL Server documentation describes a third layout, ragged right, which is fixed-width except for the last column.

RFC 4180 opens its description of CSV with: "Surprisingly, while this format is very common, it has never been formally documented." It then sets down the form "that seems to be followed by most implementations": "Each record is located on a separate line, delimited by a line break (CRLF)", fields are "separated by commas", with an optional header line first. The RFC is Informational. In its own words, it does "not specify an Internet standard of any kind". It records common practice, including where programs differ on quoting.

Some of what a reader needs is not in the file at all. In Microsoft's tooling, whoever sets up the flat file connection manager specifies "the file, locale, and code page to use", where "the code page is used to convert string data to Unicode". They also set a text qualifier: "you can specify that text fields are enclosed in quotation marks". The file carries the values. The tool is told how to read them.

Is a CSV file a flat file?

Yes. TechTarget is direct about it: "One of the most prominent flat file examples is a comma-separated values (CSV) file." Wikipedia puts "primitive spreadsheets (perhaps comma-separated or tab-delimited)" among the data stores that "can all be seen as flat-file databases because they lack integrated indexes, built-in references between data elements, and complex data types."

The same goes for a spreadsheet saved as CSV or as tab-delimited text: the saved file is a flat file.

Flat file or database? TechTarget says it in one line: "A flat file database comprises a single table." Richer formats go further. TechTarget notes that XML, JSON and YAML "can have nested data more complicated than simple flat files."

How do flat files move orders and invoices in and out of your ERP or accounting system?

Flat files are one of the plainest ways for two systems to hand data over. TechTarget's everyday case: "Microsoft Outlook might export a list of contacts in CSV format, which can then be easily imported into Google Contacts". Microsoft describes its own flat file connections being used "to extract and load data". Business documents can travel the same way.

A typical route from your system to a retail customer runs in four steps:

1. The record. Your system holds the order or invoice. As the ERP guide puts it, for a UK supplier the ERP "is where a customer's order is recorded as a sales order, and where despatch and the invoice are run from it."
2. The hand-off. The document leaves your system as a file, through an API or through a pre-built connector.
3. Mapping and translation. An EDI provider moves each field into the partner's message and converts it into the partner's standard. The EDI mapping guide covers this step in full.
4. Transport. In the words of the EDI standards guide: "The standard sets the document format; a communication protocol such as AS2, AS4, SFTP or API moves it."

Inbound orders run the same steps in reverse.

Format and transport are separate questions. A CSV sent over SFTP is still a CSV, and sending it securely does not turn it into an EDI message.

What is the difference between EDI and a flat file?

A flat file's layout is set by the system that writes it, or agreed between the two sides. An EDI message's layout is set by a published standard, and then narrowed by each retailer's own guide.

GS1's syntax summary for EANCOM shows how much more structure that brings: "The UN/EDIFACT syntax rules set the rules for structuring data into segments, segments into messages, and messages into an interchange." Each segment starts with a tag and ends with a segment terminator. In between, EANCOM's default service characters do the separating: the plus sign after the tag and between data elements, the colon between the components of a composite element, and an apostrophe to close the segment. Segments sit in a heading section, a detail section and a summary section, and "specific groups of functionally related segments may be repeated" as segment groups, which can nest.

The standards guide explains what a standard adds. It defines "the order of information, how each value is labelled, and the syntax used, so a document sent by one company is read correctly by another company's system." On top of the standard, as the mapping guide puts it, "Each retailer's Message Implementation Guide sets out the detail of every message, and one partner's guide can require a field that another's leaves out."

Watch the naming. EANCOM and EDIFACT call an order a message, X12 calls its purchase order transaction set 850, and, as the EDI transactions guide notes, "TRADACOMS calls it a file (Order File)". In Wikipedia's account of the standard, a TRADACOMS file is built from a set of structured EDI messages: a header message, one or more order messages, then trailer messages. It is not a flat file.

How does an EDI provider turn a flat file into EANCOM, TRADACOMS or X12?

In two moves: mapping, then translation. The mapping guide defines the first: "EDI mapping is the set of field-by-field rules that moves data from your system into a trading partner's EDI message, and back again." The second is "running the conversion between formats or standards, such as TRADACOMS, EANCOM, EDIFACT and X12". Neither is done once for everyone: "Each partner's guideline differs, so each one needs its own map."

Here is one order written three ways.

Illustrative only: GS1's 2002 example values, not a live order and not a format any retailer or Transalis specifies. The field names and the widths (10, 10, 14, 14 and 6 characters) are ours.

As a CSV line with a header row, each field enclosed in double quotes:

`` "order_number","order_date","buyer_gln","supplier_gln","quantity" "128576","20020830","5412345000013","4012345500004","48" ``

As a fixed-width line, each field padded with spaces to its set width:

`` 128576 20020830 5412345000013 4012345500004 48 ``

As GS1's own EANCOM segments, from its worked purchase order:

`` BGM+220+128576+9' DTM+137:20020830:102' NAD+BY+5412345000013::9' NAD+SU+4012345500004::9' QTY+21:48' ``

The five values are identical. What changes is how each one is labelled. The CSV leans on its header row, and the fixed-width line leans on positions both sides have agreed. In the EANCOM version each value travels inside a tagged segment with its own qualifiers, so NAD+BY marks the buyer and NAD+SU the supplier, each identified by GLN. As the mapping guide says of this example: "This is GS1's illustration from 2002. It is not a live order." A real order carries far more fields, and which ones depends on each retailer's guide.

How Transalis connects your system

Transalis is a managed outcomes provider for B2B integration, EDI and eInvoicing. On the retail side, the standards are its job. The EDI page names the partner formats as "TRADACOMS, EANCOM, EDIFACT, X12 and more", and says of TRADACOMS, EDIFACT and ANSI X12: "Transalis translates between them automatically." For the UK side, EANCOM, TRADACOMS, GS1 UK numbers and Peppol, the standards guide says Transalis supports all of these "on one connection and translates between them, so a supplier does not need to know which standard each partner uses." The mapping guide adds: "Transalis translates each partner's format on its side, so your ERP sees one structure whoever the partner is."

On your side, the EDI page describes Integrated EDI and Web EDI:

• Integrated EDI: "EDI wired directly into your ERP, accounting or warehouse system."
• Web EDI: "A managed web portal to receive orders, send invoices and manage compliance in a browser."

For a supplier who would rather not connect a system at all, the web EDI guide covers the browser route.

Book a 30-minute call with a Retail Supply Chain specialist, who will walk you through EDI and how Transalis makes it simple.