April 6, 2021 at 5:41 pm
I am having the worst time finding an example of the exact thing I'm looking for in a Google search. I'm hoping someone here can help.
I need to parse a SFTP directory and push the contents into a SQL Server table so I can find the exact file I'm looking for. Because the provider doesn't put the date of the file on the file name, I need to look at the properties and extract the date that way so I only download the most recent file available. I found a nifty function to do the actual pulling of directory data (https://www.opentechguides.com/how-to/article/powershell/154/directory-listing.html), but now I'm lost.
How do I get $filelist loaded into SQL server?
I know what the connection strings look like. It's the actual INSERT bit I'm struggling with. Write-SQLTableData doesn't seem to fit as nothing on that documention looks like an actual powershell variable. I've found information on how to load files, but that also doesn't help.
There's one thread that comes close (https://social.technet.microsoft.com/Forums/windowsserver/en-US/4239f342-56f0-436b-be55-440ab0ea70e4/powershell-script-write-variable-values-to-a-sql-server-table?forum=winserverpowershell) to what I need but it looks really complicated and somehow has multiple columns. My $filelist variable is loading everything as a single column into the SQL table and has multiple rows.
Does anyone have any links they can point me to? Or suggestions?
April 6, 2021 at 6:08 pm
Hmm... it looks like that list directory is just returning an array of strings that you'll have to parse if you want the relevant file information. As for getting it into a database, something like this?
$strings = New-Object System.Collections.ArrayList
[void] $strings.Add("hello")
[void] $strings.Add("there")
ForEach($line in $strings)
{
Invoke-Sqlcmd -Query "INSERT INTO <your table insert>(<column>) SELECT '$line'" -ConnectionString <your connection string>
}
It also might be easier to just do the entire thing in powershell and not mess with the database so something like this, you would just need to parse the actual file name out of the string.
$strings = New-Object System.Collections.ArrayList
[void] $strings.Add("12-03-17 07:28PM file_one")
[void] $strings.Add("11-03-20 08:28AM file_two")
[void] $strings.Add("09-03-18 09:28PM file_three")
$testy = @()
ForEach($line in $strings)
{
$testy += [PSCustomObject]@{"fileDate"=[Datetime]::ParseExact($line.Substring(0, 16), "MM-dd-yy hh:mmtt", $null);"file"=$line.Substring(17, $line.length - 17)}
}
Write-Output ($testy | Sort-Object -Property fileDate -Descending | Select-Object -First 1)
April 12, 2021 at 12:55 pm
The Invoke command looks like what I need after taking a few hours to look it over, but now the problem I'm running into is that I can't install the SQL Server powershell module to actually use it.
My regular login doesn't have admin access to my box, and the account I can use as administrator for some reason can't see the PSGallery repositories that my regular account can.
I guess this means I need to go down another path to get this information via SFTP. Thanks for your help.
April 12, 2021 at 1:15 pm
The second solution I posted does not require pushing anything to SQL Server and should work with the standard PS install.
April 12, 2021 at 1:22 pm
It doesn't, actually, because the output does the date in named format. "Apr 06 01:18 " for example. It's a little frustrating.
I'll keep playing with the code and see if there's another way I can apply it, though.
April 12, 2021 at 1:39 pm
That should just be a matter of changing the date format, I based that off the article you were using to get the directory listing but for the date format you are seeing you would need,
$testy = "Apr 06 01:18"
[datetime]::ParseExact($testy, "MMM dd HH:mm", $null)
April 13, 2021 at 5:29 pm
HA! Finally got someone in corporate who had a clue and pointed me to the way to get the SqlServer module loaded outside of powershell.
Do manual download from https://www.powershellgallery.com/packages/SqlServer/21.1.18246-preview. Save it and rename it, changing extension from .nupkg to .zip. Extract the folder and put the entire folder under C:\Program Files\WindowsPowerShell\Modules.
SQL Server powershell commands will now work. Now I can use the Invoke-Sqlcmd (tested it to be sure) and record the raw directory string in my database for tracking which file dates I've downloaded already.
Whoo Hoo! <insert Snoopy Dance here>
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply