Sign in
 
HomeLibraryLearnDownloadsRepositoryCommunityForumsBlog
Script Center >
> 
> 
Copying text to excel through powershell while keeping the text formatting

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

  • Thursday, February 24, 2011 9:03 PM
     
     
    Sign In to Vote
    0

    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!

     

All Replies

  • Thursday, February 24, 2011 9:53 PM
     
     
    Sign In to Vote
    0

    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

     
  • Monday, February 28, 2011 5:43 PM
     
     
    Sign In to Vote
    0

    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 6:07 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi,

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

    Bill