Iterate through Rows in SQL to Output to Text File

Question

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
  $NAME = "$SDIR\$FILENAME.txt"
  if (!([System.IO.File]::Exists($NAME))) {
    Out-File $NAME | Set-Content -Path $FULLFILE -Value $FILETEXT
  }
}

Answer 1

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 }
}