Successfully generated the results from PowerShell, but don't know how to save this to a SQL database table ... as we need info in table for further manipulation.
Get-Content "D:\Files\Legal.doc"
| Select-String -Pattern "Currency term loan in an aggregate amount equal to the"
| Select-Object Path, LineNumber, Line
| Export-Csv -Path D:\Results.csv -NoTypeInformation -Encoding ASCII
Need help for saving about results in SQL table.
While @postanote's answer will work, be aware that it's susceptible to SQL injection errors or compromise because it's concatenating strings, and any of your values which contain a ' will break it.
I recommend using Out-DbaDataTable and Write-DbaDataTable from dbatools.
$database = 'DBName'
$server = '.'
$table = 'DBTableName'
Import-CSV .\yourcsv.csv | out-dbadatatable | write-dbadatatable -sqlinstance $server -table $table -database $database;
This should also run much faster than individual INSERT statements.
Try this approach...
$database = 'DBName'
$server = '.'
$table = 'DBTableName'
Import-CSV .\yourcsv.csv | ForEach-Object {Invoke-Sqlcmd `
-Database $database -ServerInstance $server `
-Query "insert into $table VALUES ('$($_.Column1)','$($_.Column2)')"
}
... as discussed below.
Storing Results from Powershell to SQL Table
How to import data from .csv in SQL Server using PowerShell?
So, this post could be considered a duplicate of the above.
