SFTP – Excel

The following sections explain how you can utilize SFTP- Excel Connector to connect to an on-premise database system, for example, SQL Server.

In order to utilize On-Premise SQL Server data for creating dashboards via Lumenore self-service functionality, the following sequential steps need to be followed:

1. Step 1: Export data from SQL database to excel files.
2. Step 2: Format excels files as per the guidelines.
3. Step 3: Upload the exported data to the SFTP location.
4. Step 4: Connect to SFTP via Lumenore SFTP Excel connector.

Step1: Export data from SQL database to excel files.

In order to export data from the SQL server, please follow the below instructions:

1. Launch Excel, open an Excel file in which you want to export SQL Server data. On the toolbar, click the “Data” tab
2. Select “From Other Sources”
3. Choose “From SQL Server”.

---

Following is the screenshot of the exported tables.

---

Step 2: Format excels files as per the guidelines.

1. The file extension should be XLSX.
2. Header names should not have any punctuations and new line characters.
3. The first row of excel should be a header to identify column names.
4. Restrict the number of columns as per usefulness for faster processing.
5. Please remove all formulas and custom formatting from the excel sheet.
6. If any of the cells contain newline characters or special characters, the load process may likely reject the file. Please remove all cells which contain special or newline characters.
7. All date columns should be formatted with formats provided by excel (not any custom formatting).
8. Allowed formatting are following
   1. Number (Integer or decimals)
   2. Date (Custom formatting not allowed)
   3. General

Step 3: Upload the exported data to the SFTP location.

For transferring the file from the client’s location systems into Netlink’s SFTP site, below are the prerequisites:

1. Software
2. SFTP Site and Credentials

Software

An SFTP client software tool is required to transfer the files to a remote location

• Few of the Graphical SFTP Client Software tools such as FileZilla (Recommended*) for Windows.
• FileZilla for MAC systems are available for free download and can be used with ease.

Note: Lumenore is compatible with FileZilla.

SFTP site and credentials

To upload the files requires the SFTP site and the Credentials to access the SFTP site. The credentials can be generated by following the below steps:

1. Log in Lumenore
   
   
   
   

   

2. 
   
   
   
   Click Self Service.
   
   
   
   

   

3. 
   
   
   
   Click Connect.
   
   
   
   

   

4. 
   
   
   
   Click Excel from SFTP.
   
   
   
   

   

5. 
   
   
   
   Click Generate SFTP Account.
   
   
   
   

   

6. 
   
   
   
   SFTP credentials are generated as highlighted below:
   
   
   
   

   

For illustration, the following are the instructions to upload files on SFTP via FileZilla.

Connect to the SFTP site

1. Open Site Manager
   
   
   
   

   

2. 
   
   
   
   Click New Site
   
   
   
   

   

3. 
   
   
   
   Select SFTP from the dropdown menu
4. Enter Host Name
5. Enter Port
6. Enter User Name
7. Enter Password
   
   
   
   

   

8. 
   
   
   
   UnderTransfer Settings, and set the limit to 1 as shown below:
9. Click Connect.
   
   
   
   

   

10. 
    
    
    
    Click OK.
    
    
    
    

    

---

Transfer the files into the SFTP site provided by Lumenore

11. Select the file to be uploaded and drag it to the right side for upload.
    
    
    
    

    

Step 4: Connect to SFTP via Lumenore SFTP Excel connector.

One Logged in Lumenore follows the below steps to connect to the SFTP-Excel connector to access the SQL server data upload in the previous step for self-service.

1. Click Self Service.
   
   
   
   

   

2. 
   
   
   
   Click Connect.
   
   
   
   

   

3. 
   
   
   
   Click Excel from SFTP.
   
   
   
   

   

4. 
   
   
   
   Click Select File.
   
   
   
   

   

5. 
   
   
   
   Select File.
6. Click Next.
   
   
   
   

   

7. 
   
   
   
   Click the check box(es) to Select Tables.
8. Click the check box(es) to select columns.
9. Click preview.
10. Click Save.
    
    
    
    

    

11. 
    
    
    
    Name the dataset.
12. Click OK.