Thanks for reading. So, to clarify, I know how to do this code in Python and it works perfectly fine in Jupyter, but I'm getting problems trying to import the code in Power BI.
Overall, what I'm trying to do is to automatically go into a folder and pull the most recent CSV file.
Here is my code:
import os
import pandas as pd
path = r'C:/Users/myname/OneDrive/folder/'
files = os.listdir(path)
print("Path", path)
csv_files = [file for file in files if file.endswith('.csv')]
most_recent_file = max(csv_files, key=os.path.getmtime)
input_file = str(os.path.join(path, most_recent_file))
input_file
The error I'm receiving is...
We encountered an error while trying to connect.
Details: "ADO.NET: Python script error.
<pi>FileNotFoundError: [WinError 2] The system cannot find the file specified: 'CSV File.csv'
</pi>"
The code ultimately extends one more line to a simple pandas read_csv command, but the error occurs before I even get to this line.
One more thing I realized is that if I change my code so that I import a specific path instead of a variable, it will work without an issue, but I'm trying to create an automated task without having to change the code each time I run this.
Thanks for your help.
The problem is that os.listdir() returns only filenames; it does not return the full path.
So when os.path.getmtime() is called, it can't find the file, because it received only the filename instead of the full path.
I'm guessing it worked in Jupyter because your current directory just happened to be the OneDrive folder, but in powerBI your current directory is something else.
You can fix this by making files contain the full path to each file, instead of just the filename:
files = [os.path.join(path,filename) for filename in os.listdir(path)]