Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DeepDive
Helper IV
Helper IV

SFTP/FTP files access

Hello all,

 

Is there any direct way, that I could connect my PBI to access files placed in SFTP/FTP. Also I want to enable scheduled refresh.

ps : files will be updated on daily basis in SFTP folder.

 

Any reference link/tutorial , pls share. thanks.

 

 

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

Hi, @DeepDive 

 

Based on my research, there are several ways that are offered.

1. You can use FTP commands or third-party tools to bring files down to local folder, then connect to the folder by using the Get Data ->Folder option in Power BI Desktop.

2. You can use Microsoft Flow to automate the extraction of your data from the FTP server to your OneDrive or SharePoint.

 

See the links below for more information:

https://community.powerbi.com/t5/Power-Query/connection-with-ftp/td-p/91792

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=3313bc81-...

https://www.linkedin.com/pulse/how-connect-microsoft-power-bi-data-sftp-ftp-marcos-fattibello

https://community.powerbi.com/t5/Desktop/Connect-Power-BI-to-FTP/m-p/503802

https://www.cdata.com/kb/tech/ftp-odata-power-bi.rst

https://www.cdata.com/kb/tech/sftp-powerbi-dataflows.rst

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

3 REPLIES 3
mahmut
Frequent Visitor

Power BI does not have a built-in feature to connect natively to SFTP servers. However, you can use a 3rd party connector, a custom data connector or other methods such as downloading files to your local machine or using a script to import the data from the SFTP server. It's worth noting that while Power BI doesn't have a built-in SFTP connector, it does have a variety of built-in connectors and data import options that allow you to connect to different types of data sources, such as Python.

You can use Python to write a script to connect to an SFTP server and import the data. Python has several libraries that can be used to interact with SFTP servers, such as Paramiko, pysftp, and SSH.NET. These libraries provide the necessary functions and methods to connect to the SFTP server, authenticate, and retrieve the data.

Here is an example of how you can use the 'pysftp' library to connect to an SFTP server using Password authentication. It's also worth noting that, you may need to have python and the related libraries installed on your machine, and the script needs to be executed by a user who has the permissions to access the SFTP server.

In this example I'm connecting to an SFTP server with multiple CSV files updated daily, I'm using the 'csv' library to read, parse and append the CSV data and use 'Pandas' library to work with the data in a tabular format. I'm also using "io" library to read the CSV binary data and hand it over to "pandas" library in order to parse it as tabular. I'm also using "os" library to get the file names and merge it with the data.

With this code you do not need to download a local copy of the data or stage it, all will be handled in the Power Query buffer. It also supports data refreshes in the service, you need to have data gateway installed and configured in "personal" mode.

From "Power Query Editor" -> New Source -> Python Script.

 

import pysftp
import pandas as pd
from io import BytesIO
import os

# Parameters to change
server_ip = 'YOUR SERVER IP OR URL'
server_port = PORT NUMBER #for sftp use 22
user_name='YOUR USER NAME'
server_password='YOUR PASSWORD'
remote_directory = '/path/to/data/directory'


# Connect to SFTP server
with pysftp.Connection(server_ip, port=server_port, username=user_name, password=server_password) as sftp:
    
    # Get the files under remote directory
    files = sftp.listdir(remote_directory)
    data_files = [file for file in files if file.endswith('.csv')]
    
    # Download each file as binary and append
    content = []
    for file in data_files:
        data = remote_directory + file
        with sftp.open(data, 'rb') as f:
            df = pd.read_csv(BytesIO(f.read()), index_col=None, header=0)
            df['FILE NAME'] = file
            content.append(df)

# Close the SFTP connection
sftp.close()

# Output frame
frame = pd.concat(content, axis=0, ignore_index=True)

 

 

erricax
New Member

Well, I will try to resolve this problem right now, it seems to me that you need to have a look at this source on https://www.turboftp.com/turboftp/schedule-file-transfer.html  and try that TurboFTP client. It looks and works quite good so I hope that you will find everything that you need in case you are looking for something that will schedule transfer and synchronization with the help of task scheduler.

v-angzheng-msft
Community Support
Community Support

Hi, @DeepDive 

 

Based on my research, there are several ways that are offered.

1. You can use FTP commands or third-party tools to bring files down to local folder, then connect to the folder by using the Get Data ->Folder option in Power BI Desktop.

2. You can use Microsoft Flow to automate the extraction of your data from the FTP server to your OneDrive or SharePoint.

 

See the links below for more information:

https://community.powerbi.com/t5/Power-Query/connection-with-ftp/td-p/91792

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=3313bc81-...

https://www.linkedin.com/pulse/how-connect-microsoft-power-bi-data-sftp-ftp-marcos-fattibello

https://community.powerbi.com/t5/Desktop/Connect-Power-BI-to-FTP/m-p/503802

https://www.cdata.com/kb/tech/ftp-odata-power-bi.rst

https://www.cdata.com/kb/tech/sftp-powerbi-dataflows.rst

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.