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

score 0 · Accepted Answer · answered Aug 29 '16 at 21:03

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

Thank you for the in-depth explanation. As someone who is trying to learn, your information here has been invaluable. — GreatMagusKyros, Aug 30 '16 at 12:37

Iterate through Rows in SQL to Output to Text File

1 Answers1