0

A little background on my intent:

The purpose of the first function, GetFileNames, is to read through directories in a main folder that contains digital information on a number of facilities, and return the list of files with their BaseName.

The second function, SplitFileNames, takes the output of GetFileNames, and splits the name into 3 parts using the underscore as a delimiter. The names of the files are all structured like this; SITECODE_FACNUM_FILETYPE, so for example, DKFX_00099_MAP. Each of the 3 parts form individual columns that I then import into a database in Access.

Also, I've never used Powershell before this, and the experience I've had so far is basically a combination of reverse engineering and splicing code from a number of sources, and obviously some of my own writing.

My questions/respectful requests are:

  1. I'm almost certain there's got to be a better way to do what I'm trying to accomplish, and that I just don't have a solid enough understanding with the information I've gone through to make it happen. So I would definitely appreciate any recommendations at all for improvement.

  2. I also need the hyperlink information contained in FullName as column as well, but unfortunately I could never get it to work correctly since I have to split only the BaseName up into 3 pieces.

Thank you!

$targetPath = "C:\Users\mattm\Google Drive\TestDatabase\"
$outputPath_1 = "C:\Users\mattm\Google Drive\Powershell Scripts\Facilities Database Scanner\outputScan_1.csv"
$outputPath_2 = "C:\Users\mattm\Google Drive\Powershell Scripts\Facilities Database Scanner\outputScan_2.csv"
$delimPath = "_"


Function GetFileNames([string]$path, [string]$outputFile) {
  $list = Get-ChildItem $path -Recurse | where {!$_.PSIsContainer}
  $list | Select-Object BaseName | Export-Csv -NoTypeInformation $outputFile 
}

GetFileNames $targetPath $outputPath_1


Function SplitFileNames([string]$inputFile, [string]$outputFile) {

    $inputData = Get-Content $inputFile | select -Skip 1
    $array = @()
    $outArray = @()

    $inputData | Foreach{
                    $elements = $_.split($delimPath)
                    $array += ,@($elements[0], $elements[1], $elements[2])
                 }


    Foreach($value in $array){
        $outArray += New-Object PSObject -Property @{
        'SiteCode' = $value[0]
        'FacilityNumber' = $value[1]
        'FileTypeCode' = $value[2]
        }


    }

$outArray | Select-Object "SiteCode","FacilityNumber","FileTypeCode" | ConvertTo-Csv -NoTypeInformation | % {$_ -replace '"',""} | Out-File $outputFile -fo -en ascii

}

SplitFileNames $outputPath_1 $outputPath_2
7
  • What do you mean actually with: "the hyperlink information contained in FullName"? Commented Feb 24, 2018 at 15:44
  • It's a property of the FileInfo class, it's returned when the Get-ChildItem cmdlt is called. It gets the full path of the file or directory. Commented Feb 24, 2018 at 18:59
  • Now I'm even more confused. What kind of files are you looking for? I don't have a property with this name on my client. Commented Feb 25, 2018 at 0:27
  • That's strange, I believe I'm running Powershell 5.0 on my desktop, and I'm looking for all file types. What I have is basically 11k+ documents and pictures (Like maps, site inspections, etc..) that are all sorted into folders based on the facility they belong to. As they continue to add new digital documents to these folders I want them to be able to execute this script and return the list of all the files in the folders, and I need the output to have 4 columns; Site Code, Facility Number, Type of File (Map, Picture, Deed, etc..), File Path (Which I use to create hyperlinks in Access). Commented Feb 26, 2018 at 2:12
  • I apologize, I realize now that my FileTypeCode is very misleading. I'm not actually concerned with whether it's a PDF, XML, JPEG, or whatever else, in my context it refers to a shorthand contraction of what the document is. So for example the Floor Plan file name for a specific facility might be LUAJ_00090_FLRPLN.pdf Commented Feb 26, 2018 at 2:16

2 Answers 2

1

You might take a little time to learn the basics of Powershell. Reverse Engineering might not be the best teacher for this. ;-) Start with something like this:

$Path = 'Enter your path here'
Get-ChildItem -Path $Path -Recurse -File | 
    ForEach-Object{
        $One,$Two,$Three = $_.BaseName -split '_' 
        [PSCustomObject][ordered]@{
            SiteCode = $One
            FacNum = $Two
            FileType = $Three
            BaseName = $_.BaseName
            Hyperlink = $_.FullName
        }
    }
Sign up to request clarification or add additional context in comments.

3 Comments

Haha, I definitely plan to! I was just in a time crunch to demonstrate a workable solution, given that I'm also restricted to the applications that can be used on their machines. I certainly would have preferred to use Python. Thank you for the reply as well, I'm going to start with that and do a little more research. Do you also have any recommendations on books/resources that would be good starting points?
There are lots of ways to learn. You just have to commit to something that you do. Start with videos via Microsoft Virtual Academy and YouTube. Just search for Beginning PowerShell at either site. Read the built-in help files. Then look to the TechNet blog on the Windows PowerShell Survival Guide or even suggestions like this discussion. reddit.com/r/PowerShell/comments/7oir35/…
Thank you for your suggestions, I'll be sure to look into those as well as the book I've started on today. In terms of commitment, I certainly have the capability, it will just be a gradual process of learning given I only have spare time to devote to studying it. I'm definitely excited at the possibilities.
0

I'm extremely happy with the results from this new version!

$moduleCustomUIMessage ='path to UI module'
$outputFile_3 = 'output path'
$Path = 'target path of directory to scan'

Import-Module $moduleCustomUIMessage

$results = Get-ChildItem -Path $Path -Recurse | where {!$_.PSIsContainer} | 
    ForEach-Object{
        $One,$Two,$Three = $_.BaseName -split '_' 
        [PSCustomObject]@{
            SiteCode = $One
            FacNum = $Two
            FileType = $Three
            BaseName = $_.BaseName
            Hyperlink = $_.FullName
        }
    } | Select-Object SiteCode, FacNum, FileType, BaseName, Hyperlink | Export-Csv -NoTypeInformation $outputFile_3 

    CustomUIMessage $outputFile_3

6 Comments

Actually you would not need the Select-Object part. There should be only the specified properties anyway. But if it does the job - great. :-D
I'll have to try removing that part then. I'd read a different post last week that said invoking Select-Object forces the order you want the columns in when it's exported. Not that it matters to the database. I absolutely appreciate the help in solving this!
If you have a fairly up-to-date version of Powershell you can get rid of the Where-Object about the 'container' when you use the parameter -File for your Get-ChildItem. And if you like to have a specific order of your object properties you can use the accelerator [ordered]. I updated my answer.
Sorry for the delay @Olaf, I confirmed that I have version 5.1 installed currently. I just made the changes you suggested, and it works perfectly! I do have a follow up question on importing the output CSV produced by this script into the appropriate table in Access, but I should probably post that as a separate question. I've so far managed to execute this script from within Access at least.
I'm glad it was helpful. I would expect that Access is able to import a valid CSV file by itself. ;-)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.