none
How to add a comma to separate an array list in Powershell RRS feed

  • Question

  • The following script will get me a list of files in a directory and the $dir variable is a system.array.

    $Dir = Get-ChildItem -Path "C:\Windows\"

    $Dir 


    If I pipe the output to the foreach loop like so

    $Dir | % { $_.name }

    I get a nice list of names in the below output.

    win.ini
    WindowsUpdate.log
    winhlp32.exe
    WLXPGSS.SCR
    WMSysPr9.prx
    write.exe

    How can I get that output to include a comma after every name except the last?  

    The overall reason I am trying to do that is to get the output of the array into an IN statement that I can use to query a database.  I will probably also need to get the output enclosed in single quotes as well.

    SELECT * FROM tblVaues WHERE FileName IN ('win.ini','WindowsUpdate.log','winhlp32.exe','WLXPGSS.SCR','WMSysPr9.prx','write.exe')

    Thanks!

    Wednesday, March 5, 2014 5:55 PM

Answers

All replies

  • Hi,

    Give this a try:

    $commaList = (Get-ChildItem -Path 'C:\Windows' ).Name -join ','


    Don't retire TechNet! - (Don't give up yet - 12,700+ strong and growing)

    Wednesday, March 5, 2014 6:00 PM
  • Here's an example that may work for you.

    (Get-ChildItem -Path 'C:\Windows').Name | Foreach {
        $ListVariable += "$_,"
    }
    $ListVariable = $ListVariable.TrimEnd(',')

    Edit: Here's an example that will put each item into single quotes like in your query.

    (Get-ChildItem -Path 'C:\Windows').Name | Foreach {
        $ListVariable += "'$_',"
    }
    $ListVariable = $ListVariable.TrimEnd(',')

    Wednesday, March 5, 2014 6:02 PM
  • Mike,

    .name gave me a NULL or blank array.

    Removing that gave me a good list.

    $commaList = (Get-ChildItem -Path 'C:\Windows' ) -join ','
    Note: list edited for brevity.

    notepad.exe,PFRO.log,Professional.xml,PSEXESVC.EXE,regedit.exe,setupact.log,setuperr.log,splwow64.exe,Starter.xml,system.ini,TSSysprep.log,twain.dll,twain_32.dll,twunk_16.exe,twunk_32.exe,vmgcoinstall.log,VPNInstall.MIF,win.ini,WindowsUpdate.log,winhlp32.exe,WLXPGSS.SCR,WMSysPr9.prx,write.exe

    Going to test that on my full script to see if I can get a good list.  Thanks!

    Wednesday, March 5, 2014 6:06 PM
  • Cheers, you're welcome. This will work for PS 2.0 and will only select files (no folders):

    $commaList = (Get-ChildItem -Path 'C:\Windows' | Where { (!($_.PsIsContainer)) } | ForEach { $_.Name }) -join ','


    Don't retire TechNet! - (Don't give up yet - 12,700+ strong and growing)

    Wednesday, March 5, 2014 6:22 PM
  • In case anyone out there is interested this is how I got it to work along with an excerpt of my code.

    # Create a connection to the SQL Server
    $dataSource = "<instancename>" #Servername and instance
    $database = "<dbname>" #database
    $connectionString = "Server=$dataSource;Database=$database;Integrated Security=SSPI;" #Windows auth
    
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    
    # log all files created before the creation date/time of the oldest file ($TimeLimit set in other parts of the code)
    $LogDels = Get-ChildItem -Path "D:\TEST\TEST\*" -include "test*.txt" | Where-Object {$_.CreationTime -lt $TimeLimit}
    
    # I couldn't get the -join to work in the above statement but when I assigned to a new variable it worked.  To get it on one statement you might need to pipe it.
    $UpdateFile = $LogDels -join "','"
    
    # The -join "','" takes care of in between the names but you need a single quote at the beginning and ending
    $UpdateFile = "'" + $UpdateFile + "'"
    
    $connection.Open()
    
    $query = "UPDATE dbo.FileLog
    		  SET Delete_Date = GETDATE()
    		  WHERE Folder_Name + File_Name IN ($UpdateFile)" 
    
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    
    $result = $command.ExecuteReader()
    
    $connection.Close()
    
    # delete the files
    Get-ChildItem -Path $SubFolderPath -include $FileFilter | Where-Object {$_.CreationTime -lt $TimeLimit} | Remove-Item -Force

    Thanks everyone for responding!

    Wednesday, March 5, 2014 6:39 PM