Copying text to excel through powershell while keeping the text formatting


  • Hello everyone, 

    I have a .sql file that has SQL code such as





        who cares a,





    I am attempting to port this text into A SINGLE EXCEL CELL. Here is the jist of my current code:

    $excelapp = New-Object -comobject Excel.Application

    $excelapp.DisplayAlerts = $false

    $workbook = $excelapp.Workbooks.Add()

    $worksheet = $workbook.WorkSheets.Item(1)

    ## Do Work

    $directory = "C:\script"

    $files = Get-ChildItem $directory

        $i = 1

        foreach ($file in $files)


            if ($file.BaseName -eq "testfile")


                # Put basename into excel sheet (Row 1, Column A)

                $worksheet.Cells.Item($i, 1) = $file.BaseName

                # Put text file content in correct format

                $contentstring = "$content"

                # Put original content into excel sheet (Row 1, Column B)

                $worksheet.Cells.Item($i, 2) = $content

                # Put correctly-formatted content into excel sheet (Row 1, Column C)

        $worksheet.Cells.Item($i, 3) = $contentstring




    Here's the problem: Excel is not putting anything in CELL B ($content) and it is putting the string into CELL C ($contentstring) but it loses it's formatting (newlines, tabs, etc.)

    I cannot find a way to put ALL the text into a single cell WITHOUT converting to a string and when I convert to a string, it loses its formatting.

    Any help would be appreciated. Thanks!

    Thursday, February 24, 2011 9:03 PM


All replies

  • Script works like you create it. In cell B you put $content but I see it is $null or you put incomplete script.

    If you want get text from file use this
    $content = Get-Content $file.FullName

    Thursday, February 24, 2011 9:53 PM
  • Yea I just didn't include it in the first post. The code that I use to "get-content" and manipulate it i:


    $content = get-content $file (Now I've added $file.FullName, but still no difference)

    $contentstring = "$content"


    The FullName thing didn't help. I just don't understand why I can't directly put $content into a single excel cell.

    Monday, February 28, 2011 5:43 PM
  • Hi,

    You might want to ask that in a Microsoft Office/VBA forum.


    Monday, February 28, 2011 6:07 PM