Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to find all Microsoft Access databases on a drive. The query below works until the path gets too long and then dies with:
[DataFormat.Error] The file name Desk\..._5387851 d-7b20-44f7-a750-74e3552d 1 is longer than the system-defined maximum length.
Is there a work-around for this? I get confused when Windows allows files to be created, and then complains that the path is too long. For what it's worth, the path abbreviated above is 285 characters, and there are more folders deeper than this one. And also I can't change files on this drive, only report on them.
let
Source = Folder.Files("H:\"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Extension] = ".accdb" or [Extension] = ".mdb"))
in
#"Filtered Rows"
Solved! Go to Solution.
hi @Rudz
Open a command prompt and then run a dir command to search for the files. Output the search results to a file that you can open in PQ.
Example command, change the file extension accordingly
dir *.accdb /s /b > dbfiles.txt
This will give you all files with the extension .accdb in all subdirectories off the directory the command was executed in, and stores the results in the file dbfiles.txt
Results in the file look like this
d:\main.accdb
d:\temp\myfile.accdb
d:\temp\backup\accounts.accdb
Regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
hi @Rudz
Open a command prompt and then run a dir command to search for the files. Output the search results to a file that you can open in PQ.
Example command, change the file extension accordingly
dir *.accdb /s /b > dbfiles.txt
This will give you all files with the extension .accdb in all subdirectories off the directory the command was executed in, and stores the results in the file dbfiles.txt
Results in the file look like this
d:\main.accdb
d:\temp\myfile.accdb
d:\temp\backup\accounts.accdb
Regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
That is odd with that file name @Rudz - Is it some sort of "God Mode" folder or other folder made with a GUID that has that really long incomprehensible name? Otherwise, I am not sure why a folder on the desktop would have such a name.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans Good thought, but it's not the recycle bin, and I don't have any way to filter folders out. I tried adding a filter where Text.Length([Folder Path]) < 256 but it dies before the filter is applied.
@v-yingjl The point of my query was to traverse the whole drive. I was trying to a quick (ha!) search to find all the Access files on the drive.
@Jimmy801 This is the best idea here. Do you have any sample code to get me started?
Hello @Rudz
here the function to read files in VBA. You can implement in Excel, and if you need the file names in Excel, then this is some integrated approach. If you need the data in Power BI, then the approach of @PhilipTreacy looks really nice too.
Option Explicit
'*
'* Properties that will be collected for each found file
'*
Type FoundFileInfo
sPath As String
sName As String
End Type
Function FindFiles(ByVal sPath As String, _
ByRef recFoundFiles() As FoundFileInfo, _
ByRef iFilesFound As Integer, _
Optional ByVal sFileSpec As String = "*.*", _
Optional ByVal blIncludeSubFolders As Boolean = False) As Boolean
'
' FindFiles
' ---------
' Finds all files matching the specified file spec starting from the specified path and
' searches sub-folders if required.
'
' Parameters
' ----------
' sPath (String): Start-up folder, e.g. "C:\Users\Username\Documents"
'
' recFoundFiles (User-defined data type): a user-defined dynamic array to store the path
' and name of found files. The dimension of this array is (1 To nnn), where nnn is the
' number of found files. The elements of this array are:
' .sPath (String) = File path
' .sName (String) = File name
'
' iFilesFound (Integer): Number of files found.
'
' sFileSpec (String): Optional parameter with default value = "*.*"
'
' blIncludeSubFolders (Boolean): Optional parameter with default value = False
' (which means sub-folders will not be searched)
'
' Return values
' -------------
' True: One or more files found, therefore
' recFoundFiles = Array of paths and names of all found files
' iFilesFound = Number of found files
' False: No files found, therefore
' iFilesFound = 0
'
' Using the function (sample code)
' --------------------------------
' Dim iFilesNum As Integer
' Dim iCount As Integer
' Dim recMyFiles() As FoundFileInfo
' Dim blFilesFound As Boolean
'
' blFilesFound = FindFiles("C:\Users\MBA\Desktop", _
' recMyFiles, iFilesNum, "*.txt?", True)
' If blFilesFound Then
' For iCount = 1 To iFilesNum
' With recMyFiles(iCount)
' MsgBox "Path:" & vbTab & .sPath & _
' vbNewLine & "Name:" & vbTab & .sName, _
' vbInformation, "Found Files"
' End With
' Next
' Else
' MsgBox "No file(s) found matching the specified file spec.", _
' vbInformation, "File(s) not Found"
' End If
'
'
' Constructive comments and Reporting of bugs would be appreciated.
'
Dim iCount As Integer '* Multipurpose counter
Dim sFileName As String '* Found file name
'*
'* FileSystem objects
Dim oFileSystem As Object, _
oParentFolder As Object, _
oFolder As Object, _
oFile As Object
Set oFileSystem = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
Set oParentFolder = oFileSystem.GetFolder(sPath)
If oParentFolder Is Nothing Then
FindFiles = False
On Error GoTo 0
Set oParentFolder = Nothing
Set oFileSystem = Nothing
Exit Function
End If
sPath = IIf(Right(sPath, 1) = "\", sPath, sPath & "\")
'*
'* Find files
sFileName = Dir(sPath & sFileSpec, vbNormal)
If sFileName <> "" Then
For Each oFile In oParentFolder.Files
If LCase(Left(oFile.Name, 16)) = "procurement plan" Then
If LCase(oFile.Name) Like LCase(sFileSpec) Then
iCount = UBound(recFoundFiles)
iCount = iCount + 1
ReDim Preserve recFoundFiles(1 To iCount)
With recFoundFiles(iCount)
.sPath = sPath
.sName = oFile.Name
End With
End If
End If
Next oFile
Set oFile = Nothing '* Although it is nothing
End If
If blIncludeSubFolders Then
'*
'* Select next sub-forbers
For Each oFolder In oParentFolder.SubFolders
FindFiles oFolder.Path, recFoundFiles, iFilesFound, sFileSpec, blIncludeSubFolders
Next
End If
FindFiles = UBound(recFoundFiles) > 0
iFilesFound = UBound(recFoundFiles)
On Error GoTo 0
'*
'* Clean-up
Set oFolder = Nothing '* Although it is nothing
Set oParentFolder = Nothing
Set oFileSystem = Nothing
End Function
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hi @Rudz ,
You can create parameters to save your folder location to search files instead of traversing the whole drive.
Change the source like this:
let
Source = Folder.Files(""&Parameter1),
...
in
...
Refer this simliar issue: Import Folder long path
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @Rudz
I had the same issues. When your folders containing zip.files then things got nasty. So it's difficult to use Power Query on a big drive to search for files.
My work around was always to run a makro in Excel, that writes me the file names in a table. Then I used Power Query to access to the content of this files
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
That folder name looks like a path in the recycle bin. You should filter that subfolder/object out.
The max path/filename is 255 chars, but there are cases when Windows allows more, but not all apps can handle it. The files in the recycle bin are such an instance.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.