Nested foreach() loop with content.replace does not set-content (or add-content) as expected

Question

I've got the following Powershell script with two nested foreach loops. The script is supposed to grab the content from a simple SQL-file, add prefixes to the table names and write out new SQL-files based on the prefix/student names.

$content = Get-Content "[PATH_FILE].sql"
$students = @('18adadam','18bebert','18dadavi')
# $students = Get-Content "[PATH_FILE].txt"
$tables = @('image','post','user')

foreach ($student in $students) {
  foreach ($table in $tables) {
    $tablename = $student + '_' + $table
    'Table name: ' + $tablename
    $content = $content.Replace("TABLE ``$table``","TABLE ``$tablename``") 
  } 
  $content | Set-Content ("$student.sql")
  'Content: '+ $content
}

The files are created as expected:

18adadam.sql
18bebert.sql
18dadavi.sql

Output from the variable $tablename in the inner loop is fine:

Table name: 18adadam_image

Table name: 18adadam_post

Table name: 18adadam_user

Table name: 18bebert_image

Table name: 18bebert_post

Table name: 18bebert_user

Table name: 18dadavi_image

Table name: 18dadavi_post

Table name: 18dadavi_user

But the content written to the files (and to the console) only contains the corrected tables for the first student (18adadam):

--
-- Table structure for table `image`
--

CREATE TABLE `18adadam_image` (
  `id` int(11) NOT NULL,
  `filename` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `postId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `post`
--

CREATE TABLE `18adadam_post` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `userId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Initially the line content replace line looked like this:

$content = $content.Replace("TABLE ``$table``","TABLE ``$student" + "_" + "$table``")

and I was concerned that the concatenation somehow screwed with writing the content, so I changed it to a single variable for the table name.

$tablename = $student + '_' + $table
$content = $content.Replace("TABLE ``$table``","TABLE ``$tablename``")

I added the

'Table name: ' + $tablename

and

'Content: '+ $content

as simple debug lines to see what was going on at each point in the script.

I also tried to see if changing the output to a single file as follows would change anything:

  $content | Add-Content ("[PATH_FILE]_2.sql")

All it did, as expected, was to create a file with correct sql for 18adadam repeated three times.

score 2 · Accepted Answer · 2018-10-11 12:32:37Z

2

The 2nd $content.Replace( won't find the original value as it is changed in $content.
Save the changes to a different variable.

## Q:\Test\2018\10\11\SO_52758908.ps1
$content = Get-Content ".\template.sql"
$students = @('18adadam','18bebert','18dadavi')
# $students = Get-Content "[PATH_FILE].txt"
$tables = @('image','post','user')

foreach ($student in $students) {
  $Newcontent = $content
  foreach ($table in $tables) {
    $tablename = "{0}_{1}" -f $student,$table
    'Table name: ' + $tablename
    $Newcontent = $Newcontent.Replace("TABLE ``$table``","TABLE ``$tablename``")
  }
  $Newcontent | Set-Content ("$student.sql")
  'Content: '
  $Newcontent
}

edited Oct 11, 2018 at 12:32

answered Oct 11, 2018 at 11:38

user6811411

Sign up to request clarification or add additional context in comments.

1 Comment

Laage Over a year ago

Doh! Thank you! Was thinking it was something along those lines. But I was unable to put my finger on exactly where I was going wrong.

Collectives™ on Stack Overflow

Nested foreach() loop with content.replace does not set-content (or add-content) as expected

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related