Data exchange via SFTP server

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 and setup an SFTP account?

To authenticate to the server you’ll need to create an RSA key pair. We’ll do this using the free application OpenSSH.

1. Generating SSH Keys

Windows Users :windows:

1. Click on Start, type "Powershell", and press Enter to open a PowerShell terminal.

2. Type the following command and press Enter:

   POWERSHELL

   winget install Microsoft.OpenSSH.Beta

   This command installs OpenSSH, which is used for key generation.

3. Enter the following command to generate your key pair:

   POWERSHELL

   ssh-keygen -t rsa -b 4096 -f $env:USERPROFILE\Downloads\declaree-keyfile -C "your-email@example.com"

   Replace your-email@example.com with your actual email address. You will be prompted to set a passphrase to secure the key, which is recommended but optional. To skip setting a passphrase, press Enter without typing anything.

4. (Optional) If you want to remove OpenSSH after you’ve created your keys, you can do so with the following command:

   POWERSHELL

   winget uninstall Microsoft.OpenSSH.Beta

Mac and Linux Users :macos: :linux:

1. Search for and open Terminal.

2. Skip directly to generating your key pair with the following command:

   BASH

   ssh-keygen -t rsa -b 4096 -f $HOME/Downloads/declaree-keyfile -C "your-email@example.com"

   Replace your-email@example.com with your actual email address. You will be prompted to set a passphrase to secure the key, which is recommended but optional. To skip setting a passphrase, press Enter without typing anything.

Completing the Setup

After running the commands, check your Downloads folder. You will find two files:

• declaree-keyfile: This is your private key.

• declaree-keyfile.pub: This is your public key.

We will upload the public key to Declaree to verify that the private key belongs to your organization.

2. Uploading Your Public Key to Declaree

1. Log In:

   • Access the Declaree website and navigate to Configuration → Import & Export → FTP Account, or directly click on this link.

2. Upload Your Public Key:

   • Click on the Browse… button, locate the public key file in your Downloads folder, and select Open.

3. Save Your Changes:

   • Click Save at the bottom of the page to confirm the upload.

Once uploaded, you should see your new public key displayed on the page. If the upload was successful, ensure to store your keys in a secure location for future reference.

Take a note of the FTP account and Hostname on this page. We will be needing both in a bit.

3. Setting Up FileZilla for Server Connection

1. Download FileZilla:

   • Visit the FileZilla download page by following this link.

   • Choose and download the version appropriate for your operating system.

2. Install FileZilla:

   • Follow the installation prompts to install FileZilla on your device.

3. Launch FileZilla:

   • Open FileZilla once the installation is complete.

   • If the "Site Manager" does not open automatically, you can access it from the "File" menu.

4. Configure Your Server Connection:

   • Click on "New Site" in the "Site Manager."

   • Name your new site and enter the following connection details:

     • Protocol: SFTP - SSH File Transfer Protocol

     • Host: ftp.declaree.com

     • Port: 22

     • Logon Type: Key file

     • User: Enter the FTP account username from the "FTP Account" page on Declaree.

     • Key File: Click "Browse" to locate and select your private key file.

5. Establish Connection:

   • Click on "Connect" to initiate the connection to the server.

Once connected, FileZilla will enable you to manage files on our server.

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 (users), cost center/project (tags), exchange rate (exchange) and creditcard transaction (transactions) data can be added in the /in/* folder. Ensure that the Excel or CSV files include header titles and at least the columns written out 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 the export also includes the proof in the form of a PDF report, these will appear in the /out/reports folder with the filename [ID].pdf, where [ID] corresponds to the unique report number in Declaree.

Frequency migration of files

Files that have been placed in the root folder are automatically processed within one minute. You can track the status of all imports and exports in your Declaree administration by clicking the “More” button on the “Import & Export” page.

Data checks

New files are automatically checked for content. Should any irregularities be discovered, for example, because the number of lines is significantly smaller than what is already known in the administration, a link for verification will be sent by email to the address that is filled in under “Import & Export” → “FTP account”. After verification, the file will then still be processed.

Format of import files

Employee data (users)

Type: Excel or CSV, UTF-8 encoded.
For CSV use the Unix line-endings (LF) and semicolon (;) as column delimiter.

Projects (tags)

Type: Excel or CSV, UTF-8 encoded.
For CSV use the Unix line-endings (LF) and semicolon (;) as column delimiter.

If you use the Project levels module, the names of these columns depend on the name you’ve given to the Project level. For example, if you’ve renamed “Project” to “Cost center”, the column names will be “Cost center Name”, “Cost center Code”, etc.

Upload multiple Project levels

If several Project levels are used, you can upload separate files for each level, or combine all details into a single file. For example. consider an administration with the following project levels:

Top-Project             (<- first level)
  └─ Sub-Project        (<- second level)
      └─ Lower-Project  (<- third level)

These two levels contain the following (sub-)projects, where its code is between [brackets]:

[1000] Main project
  └─ [1010] Main sub-project 1
  └─ [1020] Main sub-project 2
[2000] Alternative project
  ├─ [2010] First alternative sub-project
  │   └─ [2011] Lower 1
  │   └─ [2012] Lower 2
  └─ [2100] Other alternative sub-project
[3000] Simple project

1. Projects and Sub-Projects in a single file

AllProjectLevels.csv

2. Projects and Sub-Projects in three separate files

TopProject.csv

SubProject.csv

LowerProject.csv