Import general journal / financial transactions

Disclaimer

All screenshots in the article were taken in the Dutch version of Yuki.

Yuki allows you to import certain journal entries, using a simple format (CSV). This is very useful for regularly recurring entries such as payroll journal entries, depreciation etc.

Only a user with a 'Back office' role in the domain or the 'External accountant' role can import a general journal entry or financial transactions.

You can import general journal entries or financial transactions from different places in Yuki:

• Click on the Back office icon in the navigation bar and then, in the now-opened screen, click on Import
• Hover your mouse over the Financials icon in the navigation bar or click on the Back office icon in the navigation bar. Subsequently click on General journal , in the now-opened screen, click on the Import button. 

The following screen is opened:

Search for and select the financial import file here, if required enter a subject and then click on the Import button.

TIP!

You can also upload the financial import file in the Archive via the usual method.

The import format

The import file has to be a so-called CSV (Comma Seperated Values) file (fields are separated by commas or semicolons (;)). Such a file can e.g. be created from Excel. On a computer with Apple this doesn't always work because e.g. the quotation marks are not included. You can also use Google spreadsheets or OpenOffice. 

General journal transactions can also be submitted as XML file. These XML files can possibly be exported from external applications. This XML file can be uploaded directly or submitted via the Yuki Accounting Webservice.

The import file (general journal entries) has to contain the following six mandatory columns in the exact order as shown in the screenshot above. The columns G up to J are optional columns that you can add.

A. Regelnummer
B. Datum (transaction date): 'dd-mm-jjjj'
C. Omschrijving: description of the line
D. Grootboek(rekeningnummer): code from Yuki's standard chart of accounts
E. Bedrag (in euro): negative is credit, positive is debit
F. KvK-nummer: identifier of the administration (is only needed when you have created more than one administration in the domain) 

G. Relatiecode (optional): code that is saved with a contact (exact match). When you only have the contact name you need to create this column in the header but you can leave the fields blank.

H. Relatienaam (optional): full name of a contact (exact match)
I. Dossiercode (optional): code that is saved with a file/project (exact match). When you only have the project name you need to create this column in the header but you can leave the fields blank.

J. Dossiernaam (optional): full name of a file/project (exact match).

When a mandatory column is not known or not applicable to the transaction line in question then you can leave it empty. However, for the import it is relevant that all mandatory columns have been created. Yuki then stores the data in the correct fields of the database.

Explanation

The first line may be a header (indicating the column name), however this header will not be used.

• The Regelnummer must be a consecutive number.
• In Datum ( - ) and ( / ) may be used as separator. 
• The Datum of the various transaction lines may be different, however the balance of all transactions on the same date must be 0,00/in balance.
• A Bedrag may NOT contains separators for thousands (so not 1.000,00 but 1000,00) (BUT when the file has a COMMA as a separator then separators for thousands are allowed)
• A Grootboek(rekening) in Yuki has to be entered with a possible leading zero included (so not 2450 but 02450)
• The KvK-nummer is only mandatory in domains with multiple administrations. However this must be identical to the CoC number that is filled in with the administration.
• The Relatiecode is optional. If filled in the transaction will be recorded on this contact and then will be linked with other transactions.
• The Relatienaam is optional. If filled in the transaction will be recorded on this contact. When the contact code is also included then there will only be searched for 'code'. When no contact can't be found a new contact will be created. 
• The Dossiercode is optional. If filled in the transaction will be linked with the file/project.
• The Dossiernaam is optional. If filled in the transaction will be linked to the file/project. When the project code is also included then there will only be searched for 'code'. When no file/project can't be found a new file/project will be created.
  
  When you only have a contact name or a file/project name you still need to create the 'Relatiecode' or 'Dossiercode' column. You can leave the fields blank or include a ';'. If you don't, the import file (general journal entries) will not be imported correctly and the names will not be created.

Create file in Excel

You can simply create such a CSV file in Excel. Please note the points of attention below:

• Make sure that the thousands in the amount are not separated by a dot (.).
• Make sure that the code of the GL accounts always has five characters. Excel changes 02450 rather quickly to 2450. You can prevent this by a code starting with a single quotation mark (') so '02450.
• Make sure that the spreadsheet doesn't contain information other than the lines to be imported.
• Save the Excel-sheet in CSV format (select 'Save as...)

You can find a layout of the import of a general journal entry with a project code in the appendix (Excel).

Remark when creating the file in another application than Excel!

As separator you may use a comma (,), semicolon (;), single quotation mark (') or colon (:). The use of a semicolon (;) is the safest. The use of double quotation marks (") is only necessary when the content of the field also contains the chosen separator of the fields.

Error messages that can occur during import

• The import file has to contain the CoC number of exactly one administration. 
  • The import file may not contain transactions for multiple administrations.
  • When there is more than one administration in a domain the CoC number has to be included in the file. 
  • When there is only one administration in a domain the CoC number is not checked.
  • Check the CoC number in the details of the administration for non-readable characters.
  • CoC number doesn't belong to any of the administrations in the domain.
  • CoC number must be exactly identical to the CoC number that is filled in with the administration, so if it contains dots then the CSV file should contain dots also.
• The entries are unbalanced, there is a balance difference of "-xxxx,xx"
  • The sum of all lines must be 0,00.
• Saving the transaction has failed. The entries are unbalanced per date.
  • .The date of the different transaction lines may differ, however the balance of all transactions of the same date must be 0,00..
  • When the amount contains a separator for thousands for example 1.234,78.
  • Unknown GL accounts in the domain are not included in the count. In that case there are actually two error messages but Yuki will only indicate that the entries are unbalanced.
  • The 'Amount' field contains besides a numerical value also an alphanumeric character of characters like for example €.
  • The 'Amount' field contains for example two minus signs. Excel converts that to positive but the import feature of Yuki doesn't.
• A GL accpount with code "XXXXX" cannot be found.
  • The GL account must be known in Yuki. Is the number less than five digits then Yuki automatically adds an 0 in front.
• Transaction lines have the same ID: three
  • The lined number must be unique.
• Import has failed. The file format is invalid. (The line contains an incorrect number of columns: 6;;l;;)
  • Each line has to contain the correct number of columns, see the above description of the import format.
• Transaction line is invalid: The entry date is blank.
  • Date with no separators 28122012 must be 28-12-2012, 28-12-12 is also allowed.
• Transaction with line number X cannot be processed: Date "31-04-2012" is an invalid date format.
  • 31st of April doesn't exist.
• Transaction with line number 2 cannot be processed: Date "2012-03-30" is an invalid date format.
  • Date notation must be'dd-mm-jjjj' or 'dd-mm-jj'.
• Saving the transaction has failed. Entries before the start date of the administration are not allowed.
  • See the 'Starting from which year do you want Yuki to keep your records?' field on the Administration tab in the company profile
• Import has failed: unknown format.
  • Strange characters in places that Yuki doesn't expect them like for example double quotation marks at the beginning and the end of the line.
• Transaction line is invalid: no transaction lines found.
  • In each line the amount is missing. In addition they must be <> 0,00.
• Saving the transaction has failed. 
  • Transactions with an entry date before the start date of the administration are only allowed when they are recorded on a balance sheet account.
• Saving the transaction has failed. 
  • Cannot create or change transactions before 02-01-2010 for administration 'Test Webservices'.
• Saving the transaction has failed. 
  • Cannot record this transaction line because the GLAccount is disabled (code = '03200').
• Transaction with line number <NR> cannot be processed: a GL account with code "11007" cannot be found.
• An item with the same key has already been added. 
  • The 'Relatiecode' field contains the same value as the 'Relatienaam' field.
• Saving the transaction has failed. 
  • Cannot record directly on a bank or cash account. Yuki checks whether GL account numbers starting with 10xxx (petty cash), 11xxx (bank), 120xx (savings), 125xx (deposit) or 15xxx (credit card) are included in the file.
• Transaction with line number 2 cannot be processed: Multiple contacts are found with name or contact code"4545".
• Document image type not supported for processing. 
  • This notification is very common but this may be the reason: Line number is in the header but at the beginning of each line the number is missing.
• Saving the transaction has failed. VAT type is not specified in VAT transaction line. 
  • The VAT rate is disabled.