Appendix V: CSV text file formats

CSV files offer a convenient way to import various payment-related transactions for matching.

Having flexible configuration options means that certain key information must be either in the file data or, if missing, information needs to be added as fixed values through “constants” within the segment definition. Examples for this would be currency or payment sign.

Flat vs. multi-structure files

The advanced bank statement import process supports CSV files with a flat structure, containing only detail lines, and multi-structure CSV files containing header, detail, and footer lines:

For an example of a flat file structure see the AR Lockbox format (Appendix I).

When defining the import structure, you need to identify if the file contains only one occurrence of the structure or if the entire structure is repeated within the file, (i.e., statements of several days within one file).

Bank import segment definition

If your file has two header lines and one footer line in addition to the detail lines, you would need to create four different segments:

• Two for the header lines
• One for the detail lines
• One for the footer line

Each segment defines the structure of each line type.

To create a segment definition for CSV, you need to provide the following information:

In the Segment header, you need to select CSV as the file type and enter a segment name and description.

In the Lines grid, you need to enter one line per field contained in the segment. In the grid, only settings relevant to the CSV type are active. Use the following settings:

• Type: Select Data if the field represents a data field. Select Segment ID if the field is an identifier.
• Identifier: If you select Segment ID, you need to enter the text/value of the ID.
• Destination field: If you select Data, you need to enter the database field where the content of the data field is stored. See Data field mapping for available fields.
• Format: A format given here checks the field data during the import.
• Field type: Select the type according to the destination field type.
• Mandatory: Select this check box to require that an empty field returns an error during import.
• Description: Enter an informative description

• Formula: Enter an Sage X3 formula if you need to apply specific controls or modifications to the imported field data.

  Formulas must be given in Sage X3 SAFE notation.

Let’s use a fictitious bank statement file with the following structure and fields:

Header line 1: Account holder name, bank account no, statement start date, statement end date
Header line 2: Starting balance, sign
Detail line: Date, amount, sign, text 1, text 2, recipient/sender
Footer line 1: Closing balance, sign

The transaction currency shall always be EUR because the account is held in EUR, but the bank doesn’t provide this information in the detail line. The sign in the file is C for credit and D for debit.

For the import, we would need to create the following four segments with the field settings described below.

Segment 1 (header line 1)

The four lines represent the four fields contained in the first header line. The header data is written to the bank statement import header table BSIIMP. The first line acts as a “dummy” line because we don’t need the name of the bank account holder. Nevertheless, we must define an entry for this field to properly process the line structure.

Type	Identifier	Destination field	Field type	Description
Data		Dummy	Alphanumeric	Account holder name
Data		F:[BSIIMP]REFBAN	Alphanumeric	Bank acc. No.
Data		F:[BSIIMP]DATSTR	Date	Start date
Data		F:[BSIIMP]DATEND	Date	End date

Segment 2 (header line 2)

The two lines represent the two fields contained in the second header line. The header data is written to the bank statement import header table BSIIMP.

Type	Identifier	Destination field	Field type	Description
Data		F:[BSIIMP]BLCSTR	Numeric	Start balance
Data		F:[BSIIMP]BLCSTRSNS	Alphanumeric	Sign start balance

Segment 3 (detail line)

The first six lines represent the six fields contained in each detail line. The detail data is stored in the bank statement import detail table BSIIMPD.

We store the transaction and the sender/recipient text in the reference/BP reference fields of the bank import so this information can be used in the automatic matching.

Because the transaction currency per statement transaction is required for processing later but not given in the import as a data field, we add this information by defining a “constant” value/field. This is added by the import process as a “virtual field” during the import of the detail line and the constant value entered in the Formula field is stored in the defined database field.

Type	Identifier	Destination field	Field type	Description	Formula
Data		F:[BSIIMPD]VALDAT	Date	Transaction date
Data		F:[BSIIMPD]AMTCUR	Numeric	Amount
Data		F:[BSIIMPD]SNS	Alphanumeric	Sign
Data		F:[BSIIMPD]FREREF(0)	Alphanumeric	Text 1
Data		F:[BSIIMPD]FREREF(1)	Alphanumeric	Text 2
Data		F:[BSIIMPD]BPREF(1)	Alphanumeric	Recipient/ Sender
Constant		F:[BSIIMPD]CUR	Alphanumeric	Fixed value for currency	“EUR”

Segment 4 (footer line)

These two lines represent the two fields contained in the footer line. Despite being a footer element, it contains data belonging to the entire import and is therefore technically part of the import header data. The data is therefore also written to the bank statement import header table BSIIMP.

Type	Identifier	Destination field	Field type	Description	Formula
Data		F:[BSIIMP]BLCEND	Numeric	End balance
Data		F:[BSIIMP]BLCENDSNS	Alphanumeric	End balance sign

Additional remarks

For recurring structures within a file (for example the file contains several statements), it is mandatory to define a segment ID for every header and footer line segment definition. Otherwise the import cannot recognize the next section within the file.

You can use any field containing a fixed value as a segment ID. In the example above, the bank account no. could be used as a segment ID.

In the example above, we assumed that the credit and debit indicator is already (as expected from the import) given as C or D in the file. If this is not the case in your specific file format, you can use the Formula field to either enter a formula or a Sage X3 SAFE function to convert the sign.

Even if you’re importing flat CSV files (consisting only of detail records), a header record is always automatically created by the advanced bank statement import process.

Bank import format definition

You need to create one import format definition per CSV file type containing the different segment definitions you created before.

To create an import format definition for CSV, you need to provide the following information:

In the format definition header:

• Enter a segment name and description and select CSV as file type
• After choosing CSV as file type, you need to define the character used as a field separator:
  • For a printable character (like “,” or “;”) you can enter it in the Field separator field.

  • For a non-printable character, like the Tab character, use the ASCII radio buttons to switch to the option ASCII code and enter the ASCII code number representing the character in the Field separator field.

    The field separator character cannot occur as part of field data.

In the Lines grid, you need to enter one line per segment of the file structure:

• The segment sequence in the grid (line number) must reflect the structure of the CSV file. Header segments must come first, followed by the detail line segment and finally followed by footer segments. You can move lines within the grid by dragging the line by the line number field.
• For flat CSV files, only one segment line is needed.

If we continue the example of the segment definition and assuming the field separator is a semicolon (;), we now need to define a bank import format definition like this:

Bank import format header data:

• Short name and description
• File type: CSV
• Field separator: ;

In the Lines grid, we need to create four lines in total.

The destination table setting needs to be aligned with the type of segment (header/footer or detail). The type of operation needs to be in line with the parsing of the data: The first header line is only evaluated and saved later (“unspecified”) while with the last header line, the header record is created within the Bank import tables (“create”). Each detail line creates a detail line entry while the footer segment data is part of the header record and therefore only updates the existing record.

Line no.	Segment	Destination table	Operation
1	Header 1	Header	Unspecified
2	Header 2	Header	Create
3	Detail	Detail	Create
4	Footer 1	Header	Update