Product: TrueCommerce Integrations

Article Link: http://help.truecommerce.com/en/articles/6676472-integrations-what-is-a-flat-file

What is a Flat File? This term is often used when describing a local file on your machine that holds data that you wish to transfer from one system to another. Flat File does not refer to any specific type of file, but rather is an umbrella term for any text-based file stored either locally or within an FTP server.

Since a Flat File can be many different types of files - and you may even have multiple types within a single integration - you may want to familiarize yourself with some of the basic terminology and standards of those types simply to help you communicate requests, whether that's in a support case, while on a call, or just simply trying to determine where data may be coming from.

Below you'll find a list of some of the most common types, and their basic properties that can help streamline your business process!

The Extensible Markup Language (XML) is one of the most common file types used within EDI. Its popular and easy to use format is used in many systems and easily digestible by most applications (such as Transaction Manager)

XML is composed of what is known as a hierarchy - a tree like system that organizes the data by providing a way that can easily be followed by both a person, as well as a computer.

At a base level the XML document is composed of Elements, Values, and Attributes.

An element is the wrapper used to identify the various pieces of data that is being transported (blue text in the above image). Things such as the Invoice Number, Date, and Item Code are all forms of Elements that define what data is being transported.

The elements of an XML stay the same across each individual file which is how the system knows where to find the data it is looking for in order to determine how many Items there are, what their prices are, or even what Invoice the file is referencing.

Elements are composed of an opening (<Price>) element and a closing (</Price)> element - anything in between is the value. Closing elements contain a forward slash prefixed to the name.

A value is the actual data being either inputted or outputted to represent your transaction (white text in image). While <Price> and </Price> indicate what the value is representing, 49.99 is the actual price value being passed through.

An attribute is a modifier of an element (grey text in image) - these attributes can help distinguish between two different elements that have the same name, while the attribute value (green text in image) can be used for filtering specific elements based on their attributes.

A Comma Separated Value (CSV) file is one that is split into Columns and Rows - which when get combined allow the system to designate specifically which Row/Column combination a particular piece of data may be residing in.

Columns run top to bottom - this allows data to align itself vertically. In the above example, your columns would be A, B, C, D and E. Each one of those have data stacking on top of each other so that you know for example, everything in Column C is the same.

Rows run left to right - and are composed of Columns. These horizontal lines represent a new section of data that can change the meaning of the Column associate with it, most often rows are split between Header and Item rows such as seen above. In the provided example the Header row is the 1st one, and represents the type of Transaction (810), the Invoice # (INV8441), it's date (10/26/22), and the PO Number associated (PO-US456-987) located in columns A-D.

While rows 2 and 3 are Item rows that change the representation of columns A-E by representing an Item (the 'I' in Column A), the internal ID (LP122/LP123), the Customer Item # (BLK-458/JFFLASH-BB), the price (49.99/149.99), and the description (MYSTERYBOX/HOVERBOARD).

The best way to identify a piece of data is through the Row/Column method. If you'd like to point to the Purchase Order Number (PO-US456-987), you would state "Row 1, Column D" or "R1.D".

If you're using a numbered-only system - you would still state the row first, and then the column 2nd using numeric values instead e.g "Row 1, Column 4" or "R1.4"

Note

Your system may not be separated by commas! Instead it may be separated by tabs, spaces, asterisks, or any other character - the format principle stays the same despite the character used to separate between columns differing.

A JavaScript Object Notation (JSON) file is another popular format similar to that of XML - it's a format designed to help transfer data in an easily readable format.

JSON is composed of Keys and Values known as Key-Value pairs that get wrapped in brackets

A key is the identifier (light-blue text in image)

A value is the data actually being transferred (light-orange in image)

A Fixed-Length File, sometimes referred to as a Positional File, is a type of row-based file where every space within the file is pre-defined as having a specific purpose. These type of files are used in older systems where character-count was more important than it is today, so every space was utilized specifically

A fixed length file is typically composed of Rows, Positions, and Sizes

A row is a vertical entry within the file, each new line entered is considered a row. These are usually identified at the front of the row with some sort of code, in the above example BIG, REF, IT1, PO4 all represent an identifier of a unique row.

A position - is where on the file (counted by characters from left to right) a particular field may start. For example if you wish to tell where the start of the Invoice # (INV8441) on row 1 was - you would count from the left over until you hit the start of your invoice #, in this case position 21 starts the Invoice #

A size - refers to how many characters to the right of the Position defined above contains the data. For example if you stated the Size of the Invoice # was 10, that means we count 10 spaces over to the right (including the start) - so for the Invoice # we would count from 21 to 30 (position 21 is considered 1 in our counting).

Another way to define Position and Size is to simply state the Range - using the above Position and Size we would say the Range of Invoice # is 21-30

rev 2/5/2024

​