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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mkurup12
Helper I
Helper I

Connect PowerBI to FTP

Hi, 

How do I get power bi to pull data from an FTP folder? Is there a way to refresh the data as well? (Refreshing through power bi scheduled refresh is not working with this). I also tried creating a batch file that copies and pastes all the files from the FTP folder into another location but this doesn't continue to work when I am signed off of the computer.

 

Thank you!!

1 ACCEPTED SOLUTION
mkurup12
Helper I
Helper I

@v-luwang-msft @Andreas_O @MathiasMÜ Thank you! i will try moving it to a sharepoint folder

View solution in original post

5 REPLIES 5
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 for ftp use 21
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)
mkurup12
Helper I
Helper I

@v-luwang-msft @Andreas_O @MathiasMÜ Thank you! i will try moving it to a sharepoint folder

v-luwang-msft
Community Support
Community Support

Hi @mkurup12 ,

current folder connector not works for remote servers or FTP servers.
Perhaps you can consider creating a local folder that mapping to the remote server then you can use the folder connector to get data that folder:

Connect Power BI to FTP 

 

Best Regards

Lucien

MathiasMÜ
Frequent Visitor

Unfortunately nothing changed in the last 4 years. You can not handle FTP servers as a source.

 

Your options are:

  1. Connect to the FTP with your computer and map the folder as a network drive, as described in the link above. With this and a personal gateway you can access the files and refresh in the service, as long as your computer is running.
  2. If you/ your company is already using Azure services, you could transfer the files (using or example Azure Data Factory, or just a powershell script on any computer) to a Azure Blob Storage/ Data Lake and query them directly with Power BI from there.

Best regards

Mathias

I'd like to add option 3. We watch our SFTP via Power Automate and upload any new incoming file to a SharePoint Folder

 

That works pretty well, but it could certainly be easier

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors