Importing bank transactions into Workspace

OVERVIEW 

Various data can be efficiently bulk imported to semilimes ERP using a CSV feed file. CSV files must comply with a number of formatting and layout requirements. Bulk importing of financial transactions into the ERP is a frequently performed operation. This article describes the process of importing payment transactions.
 

A typical data import process via CSV file looks like this: 

  •  obtain data from the source
  •  create a CSV file, applying the recommendations outlined below
  •  upload a CSV file and match the columns
  •  import data
  •  validate data in the ERP

This article covers these steps in further detail.

CSV FILE

CSV (comma-separated values) is a file format, which includes text and numerical values, separated by commas. CSV files can be opened, edited and saved in the spreadsheet or text editing programmes, like Excel, Google Spreadsheet or Notepad.
CSV file formats are used to extract and import data by many ERP, and CRM systems, online banks and financial data providers. 

EXTRACT DATA

Access your online bank system, select the period of transactions, apply other filter criteria (if required) and download this data in CSV, XLS or any other format, which can be convertible to CSV format.

PREPARE CSV DATA FILE

Open the downloaded file in one of the spreadsheet programs, like Excel, Libre Office or Google Spreadsheet, and modify the data in accordance to the requirements listed below. 

Column headers. The CSV importing process will be simplified if the column headers in the CSV match the names of data fields where inputs are required. The list of the column headers for the Statement can be seen in the Import Menu and is accessed by pressing the "Import" button.

All headers of the columns for a Statement will be listed on the left. Note, that Lines column includes expandable sub-columns. The headers of the expandable columns should include the parent header, for example: Lines/Account, Lines/Amount etc. 

Column names are case sensitive, so make sure that all column names in the CSV file exactly match the required input names (e.g. use of upper case/lower case letters).

Fill in all required inputs. Depending on the document or transaction, there will be different required fields to be filled in with data. All required fields in the ERP are marked with the asterisk “ * ”

 

In the example above, Name, Journal, Start Balance, End Balance, statement Date, Amount, transaction Date and Account are the required fields in the Statement.

Please note, that some inputs can become required upon certain conditions. For example, some general ledger accounts require Party field not to be empty. Even though Party is a non-required field, it can become mandatory if a specific account had been selected in statement Account field.

Fill in other non-required inputs. Statement contains other non-required fields, like Number, Sequence, Party, Invoice etc. These inputs are not mandatory, however including the data to these fields in the CSV file will reduce the manual inputs into Statement and will simplify the subsequent payment reconciliation process.

Exact values for required and non-required inputs. It is quite important, that for required and non-required inputs the correct values are used. For example, the inputs for the field Party should contain the full name of the Party, as it is named in your ERP.
The field Account should include the general ledger account name only. In our example case (please see pictures above), the Account field has value “2010-Accounts Payable Trade”, which includes the account number and account name. The account column in the CSV file, however, should include the account name only (e.g. “Accounts Payable Trade”).

Maximum number of lines. To avoid the slow processing of imported data into ERP, we recommend that each CSV file contains no more than 400 lines. 

Income and expense amounts. Statement files, provided by banks, usually separate income and expense amounts into two different columns. The CSV file should combine all values in one column, where positive amounts are income and amounts beginning from a negative sign “-” are expenses. The amounts should not include currency symbols. 

Decimal separator. Some countries use comma (“,”) as a decimal separator, however in most countries dot (“.”) is used to separate  integer and its fraction. In the CSV file, prepared for the import, only dot (“.”)  should be used for the numerical values.

Date format. All dates in the file should be presented in the following format: YYYY-MM-DD. Make sure that all dates in your CSV file are converted to this date format.

Value delimiters. Although CSV file format allows to use different types of value delimiters, make sure that your CSV uses either semicolon “;” or comma  “,” as value delimiters.  

Text separator. Use only quotes (“ “) to separate the text values in the CSV file.

Recommended encoding for the data saved in the CSV file is UTF-8.

Use the sample CSV file for importing Statement with transactions into semilimes. 

UPLOAD FILE

Once the CSV file is prepared and ready, upload it to the EPR. Press "Import" button in the Statement menu to open the import window. Press "Attach File" and select the CSV file to upload. 

COLUMNS MATCH 

If the column headers in the CSV file match the field names in the ERP, the headers will be detected automatically when the CSV is uploaded. Alternatively, select the columns manually from the list located on the left. Selected columns will appear in the list on the right.  The sequence of the selected columns should match the column order in the CSV file. 

IMPORT

Once the CSV file is attached and columns are detected, press the "Import" button and wait for a moment. The data is being uploaded to the system. Wait for the notification about the successful data import and update the Statement list. The statement data is saved in the ERP in draft mode and can now be processed further.

FURTHER IMPROVEMENTS

We are continuously working on improving the system usability and data transfer tools to the ERP. OFX file formats will also soon be supportend for the transfer of financial data. In addition to this, we are working on integrations with the third-party financial data aggregators suppliers. Please feel free to drop us a note if you require assistance with your data import, or you're missing important features that you require to run your business on semilimes. 

Recent Posts

powered by semi/imes