none
Copying text to excel through powershell while keeping the text formatting

    Question

  • Hello everyone, 

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

    select 

        xxyyz, 

        whateverhere

    from 

        who cares a,

        okaynow

    where 

        blahblah

     

    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

            }

            $i++

        }

    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

Answers

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.

    Bill

    Monday, February 28, 2011 6:07 PM