I wrote this PowerShell script and it is working fine, but it takes for example 55 seconds for it to run on one file, but i need to run it for 435 text files where each one is ~650 kB of text.
To complete the 435 files it takes over 7 hours! Is there any way to make this process faster? Maybe cut the process in half? I do not know if the problem is the loop.
$Path = "C:\Users\rfp6fkj\Desktop\Group\*"
#OK so the Get-ChildItem cmdlet in Powershell creates an array to enumerate/loop thru
$Files = Get-ChildItem "$Path.Group"
(Get-Content $Path) -notmatch '^#' | Where { $_.Trim(" `t") } | Set-Content $Path
function GoParseandInsert {
try {
#$connection.Open()
$cmd = $connection.CreateCommand()
$insert_stmt = "INSERT INTO PasswordAge.[dbo].[tblDataParsed]
([Server]
,[Group]
,[UserID])
SELECT I.Server,[Group],F.Val AS TheUserID
FROM PasswordAge.[dbo].[GroupPasswordAge] I
CROSS APPLY PasswordAge.[dbo].ParseDelimValues(CASE WHEN I.UserID = '' THEN 'No Data in Column' ELSE I.UserID END,',') F
WHERE I.UserID <> ''"
$cmd.CommandText = $insert_stmt
#Write-Output $insert_stmt
$cmd.ExecuteNonQuery()
} finally {
if ($connection -and ($connection.State -eq 'Open')) {
$connection.Close()
}
}
}
try {
foreach ($File in $Files) {
$TheFileName = $File.Basename
$StringContent = Get-Content $File
foreach ($Thing in $StringContent) {
$index = $Thing.IndexOf(":")
$GroupName = $Thing.Substring(0, $index)
$cmd = $Connection.CreateCommand()
$BetterThing = $Thing.Split(":")[3]
$insert_stmt = "INSERT INTO [dbo].[GroupPasswordAge]
([Server]
,[Group]
,[UserID])
VALUES
('$thefileName','$GroupName','$BetterThing')" -replace "\s+"," "
$cmd.CommandText = $insert_stmt
Write-Host $insert_stmt -ForegroundColor DarkCyan
$cmd.ExecuteNonQuery()
}
}
} catch {
#if Files dont exist throw a flag
Write-Host "Caught an exception:" -ForegroundColor Red
Write-Host "Exception Type: $($_.Exception.GetType().FullName)" -ForegroundColor DarkRed
Write-Host "Exception Message: $($_.Exception.Message)" -ForegroundColor Red
} finally {
Invoke-Sqlcmd -Query "Truncate table PasswordAge..tblDataParsed"
GoParseandInsert
}
if ($connection -and ($connection.state -eq 'Open')) {
$connection.Close()
}
