I have the below code written up, I am just curious if I am on the right track or if there is a better way to do this sorta thing in a more programmatic way ?
I am wanting to automate the removal of backslashes in the CSV file header names. so technically only the first row needs modified or looked at. The header string will always be the same and I could also technically replace the first row string with the string without the backslashes but there is a lot of ways to tackle a problem, just trying to find the best way to do it! Thanks all for your possible help!
# Define Server Name
$ComputerName = "HOST ADDRESS"
# Define UserName
$UserName = "USERNAME"
#Define the Private Key file path
$KeyFile = "PATH TO KEYFILE\KEYFILE NAME (OPENSSH FORMAT)"
#Defines to not popup requesting for a password
$nopasswd = new-object System.Security.SecureString
#Set Credetials to connect to server
$Credential = New-Object System.Management.Automation.PSCredential ($UserName, $nopasswd)
# Set local file path and SFTP path
$LocalPath = "E:\ftproot\LocalUser\USERNAME\In"
$SftpPath = 'In/'
# Establish the SFTP connection
$SFTPSession = New-SFTPSession -ComputerName $ComputerName -Credential $Credential -KeyFile $KeyFile
# lists directory files into variable
$FilePath = Get-SFTPChildItem -sessionID $SFTPSession.SessionID -path $SftpPath
#For each file listed in the directory below, modify the CSV header to remove the backslashes then reupload them to the correct folder path
ForEach ($LocalFile in $FilePath)
{
#Ignores '.' (current directory) and '..' (parent directory) to only look at files within the current directory
if($LocalFile.name -eq "." -Or $LocalFile.name -eq ".." )
{
Write-Host "Files Ignored!"
}
else
{
Write-Host $LocalFile
Get-SFTPFile -SessionId $SFTPSession.SessionID -LocalPath $LocalPath -RemoteFile $localfile.fullname -Overwrite
$path ="the downloaded CSVS PATH"
Get-ChildItem -path $path -Filter *.csv |
Foreach-Object {
$content = Get-Content -Path $path
$content[0] = $content[0] -replace '/',""
Set-Content $path -value $content -force
}
# Upload the file to the SFTP path
Set-SFTPFile -SessionId $SFTPSession.SessionID -Path $SftpPath
#Remove-SFTPItem -SessionId $SFTPSession.SessionID -Path $localfile.fullname -Force
}
}
#Terminates the SFTP session on the server
Remove-SFTPSession -SessionId $SFTPSession.SessionID