Gavin Eldred
Gavin Eldred

Reputation: 19

Choose which CSV to import when running a PowerShell script

I get a CSV every week that our finance team puts in a shared drive. I have a script for that CSV that I run once I get it.

The first command of the script is of course Import-Csv.

The problem is, the finance team insists on naming the file differently each time plus they don't always put it in the same location within the drive.

As a result, I have to first hunt for the file, put it into the directory that the script points to and then rename the file.

I've tried talking to the team about putting it in the same location and making sure the filename is the same but they only follow the instructions for a couple of weeks before just doing whatever.

Ideally, I'd like for it so that when I run the script, there would be a popup that would ask me to pick a CSV (Similar to how it looks when you do "Save As" on an Office Document).

Anyway for this to be done within PowerShell?

Upvotes: 0

Views: 718

Answers (3)

Walter Mitty
Walter Mitty

Reputation: 18940

If you are willing to settle for a selection box that doesn't look as nice as the Save As dialog, you can use Out-Gridview. Something along these lines might help.

$filenames =
     @(Get-ChildItem -Path C:\temp -Recurse -Filter  *.csv  |
         Sort-Object LastWriteTime -Descending |
         Out-GridView -Title 'Choose a file' -PassThru)

$csvfile = $filenames[0].FullName

Import-Csv $csvfile | More

The -Path specifies a directory that contains all the locations where your csv file might be delivered. The sort is just to put the recently written files at the top of the grid. This supposedly makes selection easier. The @() wrapper merely makes sure the result stored in $filenames is an array.

You would do something else with the results of Import-Csv.

Upvotes: 1

Gnarlygeek
Gnarlygeek

Reputation: 1

Steven's response certainly satisfies your original question, but an alternative would be to let PowerShell do the work. If you know the drive, and you know the name of the file this week, you can pass the name to your script and let it search the drive filtering on the specific csv file you need. Make it recursive, and open the only file that matches. Sorry, didn't have time yesterday to include code. Here's a function that returns the full file path when provided with a top level search path and a filename with possible wildcards.

function gfp { $result=gci $args[0] -recurse -include $args[1]; return ($result.DirectoryName + "\" + $result.Name) }

Example: gfp "d:\rootfolder" "thisweeksfilename.csv"

Upvotes: 0

Steven
Steven

Reputation: 7087

You can access .Net classes and interface with the forms library to instantiate and take input from the standard FileOpen dialog. Something like below:

Using Namespace System.Windows.Forms

$FileBrowser = [OpenFileDialog]::new()
$FileBrowser.InitialDirectory = 'c:\temp'
$FileBrowser.Filter = 'Comma Separated Values (*.csv) | *.csv'
[Void]$FileBrowser.ShowDialog()

$CsvFile = $FileBrowser.FileName

Then use $CsvFile int he Import-Csv command.

You can change the .InitialDirectory property to make navigating a little more convenient.

Use the .Filter property to limit the file open display to CSV files, to make things that much more convenient.

Also, use the [Void] class to prevent the status return (usually 'OK' or 'Cancel') from echoing to the screen.

Note: A simple Google search will turn up many examples. I refined some of the work from here. That will also document some of the other properties if you want to explore etc.

Upvotes: 2

Related Questions