cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

[DataFormat.Error] The file name is longer than the system-defined maximum length

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"

 

 

1 ACCEPTED SOLUTION
Super User III
Super User III

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.

View solution in original post

7 REPLIES 7
Super User III
Super User III

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.

View solution in original post

Super User III
Super User III

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Frequent Visitor

@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

Community Support
Community Support

Hi @Rudz ,

You can create parameters to save your folder location to search files instead of traversing the whole drive.

parameters.png

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.

Super User III
Super User III

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

Super User III
Super User III

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors