Data exchange via SFTP server

Written by Anika Hermanns

March 28, 2017 12:50

To automatically synchronize data with your Declaree Administration, you can use your own Declaree Exchange account. With the help of this, files can be exchanged periodically between Declaree and your existing system (e.g. approved expenses, personnel, cost centers and exchange rate data).

How do I create an SFTP account?

When your sFTP module is activated by Declaree:

Go to "Settings"> "Import & Export"> "FTP Account".
Here you'll see the username for your account.

Activate your authorization key

Generate the private / public key.
Open your public key in a text editor and copy the content.
Then press "Add key".
Give your key a name and paste the public key from the clipboard.
Press "Save".

Generate public / private key under Windows

You can download PuttyGen and use it to generate a key set.
Then click "Generate" and move the mouse over the grey area until 100% of the code has been generated.
Click "Save private key" to save the private key. Repeat the same steps to generate the public key.
To save the generated public key in Declaree, you must first adapt the file: In the file, delete the instructions 'begin' and 'end' and the comments. Next, add "ssh-rsa" for the key and make sure the entire key is on one line. It will look like this: "ssh-rsa AAAAB3NzaC1yc2EAAA .... GzSznbU9MrvTk =" As a simplification, you can also do this via this website which automatically converts the public key for you and converts it into a declaration compatible file.
Store the public key in Declaree.

Generate public / private key under Linux / Mac OS X

Open the terminal.
Enter the following command: ssh-keygen -t rsa -b 4096 -C "[your email address]".
Select a folder in which you want to save the keys.
Leave the passphrase blank by clicking "Enter" twice.
Add the public key (id_rsa.pub) in Declaree.

Connect

Hostname	ftp.declaree.com
Poort	22
Username	You can find it at Import/Export: https://app.declaree.com/admin/exchange_account

Classification of files

An SFTP account has the following folder layouts that you can use;

/in
   \-- /users
   \-- /tags
   \-- /exchange
   \-- /transactions
/out
   \-- /reports

The relevant personnel, cost center and exchange rate data can be added in the / in / * folder. Make sure that the Excel or CSV file is named and at least contains the information in the tables below.

The periodic export files with the file names "Expenses_ [Ymd] .csv" are automatically placed in the / out / * folder, where [Ymd] stands for the year, the month and the day of the export (e.g. "Expenses_20170502"). If evidence loads in the form of PDF reports were also exported during the export, then these are in the / out / Reports folder, provided with the file name '[ID] .pdf', in which [ID] for the specific report number in Declaree stands.

Frequency migration of files

Files that have been placed in the / * folder are automatically processed within one minute. You can track the status of all imports and exports in your Declaree Administration under "Manage"> "Account"> "Data Exchange".

Data checks

New data are automatically checked for their content. If irregularities are discovered, for example because the number of lines is significantly smaller than already specified in the Declaree Administration, a link will be sent to you for checking and sent to you. Please always enter your current email address under "Manage"> "Account"> "Data exchange"> "Technical contact".

Format of employee data

Type: Excel of CSV (UTF-8 encoded)
(For CSV use the Unix line-endings and semicolon as column delimiter)

Column	Mandatory	Limitations
Username	Yes	Text
Initials	No	Text
First Name	No	Text
Prefix	No	Text
Last Name	Yes	Text
Email	Yes, Standard Setup	Valid E-mail address
Code	Yes	Text
Manager	Yes, Standard Setup	Valid Username
Submits to	No	Valid Username
Groups	No, Standard Setup	Text, comma separated
Department	No, Standard Setup	Valid field value

Format of the cost center information (tags)

Projects-and-Activities.xlsx

Code	Name	Project Code
1000	Client AB
1010	Draft	1000
1020	Production	1000
2000	Client CD
2010	Draft	2000
2020	Production	2000

2. Projects and Activities in two separate Excel or CSV-Files;

Projects.xlsx

Projekt Code	Projekt Name
1000	Client AB
2000	Client CD

Activities.xlsx

Activitiy Code	Activitiy Name	Project Code
1010	Draft	1000
1020	Production	1000
2010	Draft	2000
2020	Production	2000