GreatMagusKyros GreatMagusKyros - 1 year ago 89
PowerShell Question

Iterate through Rows in SQL to Output to Text File

I have a SQL table that contains several hundred rows of data. One of the columns in this table contains text reports that were stored as plain text within the column.

Essentially, I need to iterate through each row of data in SQL and output the contents of each row's report column to its own individual text file with a unique name pulled from another column.

I am trying to accomplish this via PowerShell and I seem to be hung up. Below is what I have thus far.

foreach ($i=0; $i -le $Reports.Count; $i++)
$SDIR = "C:\harassmentreports"
$FILENAME = $Reports | Select-Object FILENAME
$FILETEXT = $Reports | Select-Object TEXT
if (!([System.IO.File]::Exists($NAME))) {
Out-File $NAME | Set-Content -Path $FULLFILE -Value $FILETEXT

Answer Source

Assuming that $Reports is a list of the records from your SQL query, you'll want to fix the following issues:

  • In an indexed loop use indexed access to the elements of your array:

    $FILENAME = $Reports[$i] | Select-Object FILENAME
    $FILETEXT = $Reports[$i] | Select-Object TEXT
  • Define variables outside the loop if their value doesn't change inside the loop:

    $SDIR = "C:\harassmentreports"
    foreach ($i=0; $i -le $Reports.Count; $i++) {
  • Expand properties if you want to use their value:

    $FILENAME = $Reports[$i] | Select-Object -Expand FILENAME
    $FILETEXT = $Reports[$i] | Select-Object -Expand TEXT
  • Use Join-Path for constructing paths:

    $NAME = Join-Path $SDIR "$FILENAME.txt"
  • Use Test-Path for checking the existence of a file or folder:

    if (-not (Test-Path -LiteralPath $NAME)) {
  • Use either Out-File

    Out-File -FilePath $NAME -InputObject $TEXT

    or Set-Content

    Out-File -Path $NAME -Value $TEXT

    not both of them. The basic difference between the two cmdlets is their default encoding. The former uses Unicode, the latter ASCII encoding. Both allow you to change the encoding via the parameter -Encoding.

You may also want to reconsider using a for loop in the first place. A pipeline with a ForEach-Object loop might be a better approach:

$SDIR = "C:\harassmentreports"
$Reports | ForEach-Object {
  $file = Join-Path $SDIR ($_.FILENAME + '.txt')
  if (-not (Test-Path $file)) { Set-Content -Path $file -Value $_.TEXT }
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download