locked
Script to pull filenames into CSV RRS feed

  • Question

  • Hey Guys,

    I have been attempting to create a script that will pull files into a CSV.  This can be a rather large CSV file due to many files.

    I am new to powershell, but know how to tinker around.. :)

    Here are the details:

    I have a network path that has subdirectories with files in them.  These files have somewhat of consistency.  e.g. "123456 lastname - A.pdf"  Sometimes they will have a character that seperates the number/name/letter at the end.  Sometimes the files have a (" ")  (".")  ("-").  Hope that makes sense...

    Basically what I am looking for is a way to take the information from "each" file and put it into a CSV file.

    I can get the information I need from the files using  "Get-ChildItem", but i am mainly struggling with seperating the file name information.

    I need the above example file name to have seperate columns for the "numbers" "lastname" "letter"

    Here is what i have so far:

    Get-ChildItem -Path $path -File -Recurse | Select-Object BaseName,FullName,LastWriteTime |


    All information is gathered with this string after an Export-CSV , i just dont know what to do about seperating the file names into seperate columns...

    Thank you for all the help with this!

    Thursday, November 2, 2017 8:33 PM

Answers

  • Hi,

    Based on your situation, I recommend that process file name through regular expression firstly. The following screenshot for your reference:


    In addition, the following scripts for your reference, hope it is helpful to you:
    $path = '\\localhost\D$'
    $files = Get-ChildItem -Path $path -File -Recurse
    $info = foreach ($file in $files)
    {
        $columns = $file.BaseName -replace '\.',' '
        $columns = $columns -replace '\-',' '
        $columns = $columns -replace '\s{1,}',' '
        $columns = $columns -replace '\(\S*\)',''
        $columns = $columns -replace ' $',''
        $columns = $columns.Split(' ')
        $file | Select-Object @{n='column1';e={$columns[0]}},@{n='column2';e={$columns[1]}},@{n='column3';e={$columns[2]}},BaseName,FullName,LastWriteTime
    }
    $info | Export-Csv -Path 'D:\info.csv' -NoTypeInformation

    If you need further help, please feel free to let us know.

    Best Regards,
    Albert Ling

    Please remember to mark the replies as an answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Marked as answer by evan.briggs Monday, November 6, 2017 4:07 PM
    Monday, November 6, 2017 9:55 AM

All replies

  • help export-csv -full

    You should start by learning basic PowerShell.

    Get-ChildItem -Path$path -File -Recurse | Select-Object BaseName,FullName,LastWriteTime | Export-CSv myfiles.csv


    \_(ツ)_/

    Thursday, November 2, 2017 8:38 PM
  • Right..

    I have already done the Export-CSV, its exactly what i need.  However, i need the name of the file to be broken up into an additional 3 columns.

    My brain isn't wrapping around what modules to use in order to get them segregated...

    Does that make sense?

    Thursday, November 2, 2017 9:26 PM
  • A file name is a string.  It is composed of a path a base name and an extension.  What more do you want.  Your question is too vague to be answered.


    \_(ツ)_/

    Thursday, November 2, 2017 9:28 PM
  • How would i tell powershell to seperate the name of each file prior to the export?

    123456 lastname - A.pdf

    --------------------------------------

    123456 - seperate column

    lastname - seperate column

    A - seperate column

    Thursday, November 2, 2017 9:48 PM
  • Just split the name on the space.


    \_(ツ)_/

    Thursday, November 2, 2017 9:49 PM
  • Please elaborate...
    Thursday, November 2, 2017 9:58 PM
  • Frist you must learn PowerShell: https://mva.microsoft.com/en-us/training-courses/getting-started-with-microsoft-powershell-8276

    Next use help:
    help select-object -online

    Next expand you knowledge: https://technet.microsoft.com/en-us/library/ff730948.aspx


    \_(ツ)_/

    Thursday, November 2, 2017 10:11 PM
  • Please try and be a little bit more of help...  your responses are not encouraging.. and for a moderator i would have thought a little bit more enthusiasm...

    Thank you for your assistance.

    God Bless.

    Thursday, November 2, 2017 10:26 PM
  • Please try to understand.  This is a forum for technicians and not for free scripts or free consulting.  You need to be a technician and have learned basic PowerShell.  That doe not include just running it with copied scripts.  You need to take the time to learn PowerShell.

    Everything I posted gives you your answer.  It will also help you  learn PowerShell.  If you are not a tech then it is likely you can't understand the links.  If you need a free script then I recommend searching for a script that does what you need. 

    Of course you could just get busy and try to learn PowerShell.

    Here is a link for script requests:

    Script requests: https://gallery.technet.microsoft.com/scriptcenter/site/requests


    \_(ツ)_/

    Thursday, November 2, 2017 11:18 PM
  • Hi,

    Do all files have file name formats like "123456 lastname - A.pdf"? If yes, you can have a try with the following demo scripts. Hope it is helpful to you:
    $path = '\\localhost\D$'
    $files = Get-ChildItem -Path $path -File -Recurse
    $info = foreach ($file in $files)
    {
        $columns = $file.BaseName.Split(' ')
        $file | Select-Object @{n='column1';e={$columns[0]}},@{n='column2';e={$columns[1]}},@{n='column3';e={$columns[3]}},BaseName,FullName,LastWriteTime
    }
    $info | Export-Csv -Path 'D:\info.csv' -NoTypeInformation

    If no, file name formats may need to be filtered by regular expressions firstly, the following example for your reference:


    If you need further help, please feel free to let us know.

    Best Regards,
    Albert Ling
    Friday, November 3, 2017 5:16 AM
  • $variable="123456 lastname - A.pdf"

    $variable -split {$_ -eq "-"}

    $list=$variable -split {$_ -eq " "}

    $list[1]

    lastname


    Thanks & Regards Ramandeep Singh

    Friday, November 3, 2017 5:42 AM
  • $variable="123456 lastname - A.pdf"

    $variable -split {$_ -eq "-"}

    $list=$variable -split {$_ -eq " "}

    $list[1]

    lastname


    Thanks & Regards Ramandeep Singh

    This is how to use split:

    $a,$b,$,$d = $_ -split ' '

    In PowerShell the output of any split or array can be output to multiple values.

    $a,$b,$c = @(1,2,3)


    \_(ツ)_/

    Friday, November 3, 2017 5:46 AM
  • Hi,

    Do all files have file name formats like "123456 lastname - A.pdf"? If yes, you can have a try with the following demo scripts. Hope it is helpful to you:
    $path = '\\localhost\D$'
    $files = Get-ChildItem -Path $path -File -Recurse
    $info = foreach ($file in $files)
    {
        $columns = $file.BaseName.Split(' ')
        $file | Select-Object @{n='column1';e={$columns[0]}},@{n='column2';e={$columns[1]}},@{n='column3';e={$columns[3]}},BaseName,FullName,LastWriteTime
    }
    $info | Export-Csv -Path 'D:\info.csv' -NoTypeInformation

    If no, file name formats may need to be filtered by regular expressions firstly, the following example for your reference:


    If you need further help, please feel free to let us know.

    Best Regards,
    Albert Ling

    Thank you sir, i will check this out.  This was the type of direction i was looking for.  

    The answer to your question about the format is no.  The files vary in format:

    "123456 lastname - A.pdf"
    "123456.lastname.A.pdf"
    "123456 - lastname.A.pdf"
    "123456.lastname - A.pdf"

    Thats a few of them, multple people at my work place create these files so there is no consistency.

    There are also additional characters that i am dealing with as well:

    "123456 lastname - A  (AI).pdf"

    "123456 lastname - A  (DDD).pdf"

    Also looking to figure out how to exclude these additional characters.

     

    Friday, November 3, 2017 3:22 PM
  • Hi,

    Based on your situation, I recommend that process file name through regular expression firstly. The following screenshot for your reference:


    In addition, the following scripts for your reference, hope it is helpful to you:
    $path = '\\localhost\D$'
    $files = Get-ChildItem -Path $path -File -Recurse
    $info = foreach ($file in $files)
    {
        $columns = $file.BaseName -replace '\.',' '
        $columns = $columns -replace '\-',' '
        $columns = $columns -replace '\s{1,}',' '
        $columns = $columns -replace '\(\S*\)',''
        $columns = $columns -replace ' $',''
        $columns = $columns.Split(' ')
        $file | Select-Object @{n='column1';e={$columns[0]}},@{n='column2';e={$columns[1]}},@{n='column3';e={$columns[2]}},BaseName,FullName,LastWriteTime
    }
    $info | Export-Csv -Path 'D:\info.csv' -NoTypeInformation

    If you need further help, please feel free to let us know.

    Best Regards,
    Albert Ling

    Please remember to mark the replies as an answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Marked as answer by evan.briggs Monday, November 6, 2017 4:07 PM
    Monday, November 6, 2017 9:55 AM