Powershell work with multiline text in Excel cell

Question

I have a fairly simple request (for me it is quite tough task tbh).

I have two CSV files which I want to convert to Excel so each of these two CSV files would occupy one sheet each. So far, I have made it work, but I have this small thing I want to correct.

One of the cells in CSV contains multiple text lines, something like this:

This is entry 1

This is entry 2

I would like to have these two entries to be imported into Excel cell the same way it is in CSV, but when I check my Excel file, the second entry is imported into next row:

Row 1 Cell1 - This is entry 1

Row 2 Cell1 - This is entry 2

I don't know if I should work with .NET class worksheet.UsedRange.EntireRow or worksheet.UsedRange.EntireColumn or something else. I was checking MSDN, but since I am still a noob, I couldn't find anything.

This is the sample of my code:

Function Merge-CSVFiles
{
Param(
$CSVPath = ".\Reports", 
$XLOutput=".\final_final_report.xlsx"
)

$csvFiles = Get-ChildItem ("$CSVPath\*") -Include *.csv
$Excel = New-Object -ComObject excel.application
$Excel.visible = $false
$Excel.sheetsInNewWorkbook = $csvFiles.Count
$workbooks = $excel.Workbooks.Add()
$CSVSheet = 1

Foreach ($CSV in $Csvfiles)

{
$worksheets = $workbooks.worksheets
$CSVFullPath = $CSV.FullName
$SheetName = ($CSV.name -split "\.")[0]
$worksheet = $worksheets.Item($CSVSheet)
$worksheet.Name = $SheetName
$TxtConnector = ("TEXT;" + $CSVFullPath)
$CellRef = $worksheet.Range("A1")
$Connector = $worksheet.QueryTables.add($TxtConnector,$CellRef)
$worksheet.QueryTables.item($Connector.name).TextFileCommaDelimiter = $True
$worksheet.QueryTables.item($Connector.name).TextFileParseType  = 1
$worksheet.QueryTables.item($Connector.name).Refresh()
$worksheet.QueryTables.item($Connector.name).delete()
$worksheet.UsedRange.EntireColumn.AutoFit()
$CSVSheet++

}

$workbooks.SaveAs($XLOutput,51)
$workbooks.Saved = $true
$workbooks.Close()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbooks) | Out-Null
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

}

Answer 1

I am not sure why the method you are using does that but I was able to reproduce on my system as well. I do some similar operations in one of my scripts that builds a large Excel workbook and I have used the following method with success:

1. Import the CSV with Import-CSV
2. Convert it to a multi-dimensional array
3. Write the array to Value2 of the range object

For example, replace your code that looks like this:

$TxtConnector = ("TEXT;" + $CSVFullPath)
$CellRef = $worksheet.Range("A1")
$Connector = $worksheet.QueryTables.add($TxtConnector,$CellRef)
$worksheet.QueryTables.item($Connector.name).TextFileCommaDelimiter = $True
$worksheet.QueryTables.item($Connector.name).TextFileParseType  = 1
$worksheet.QueryTables.item($Connector.name).Refresh()
$worksheet.QueryTables.item($Connector.name).delete()

With this instead:

$CsvContents = Import-Csv $CSVFullPath
$MultiArray = (ConvertTo-MultiArray $CsvContents -Headers).Value
$StartRowNum = 1
$StartColumnNum = 1
$EndRowNum = $CsvContents.Count + 1
$EndColumnNum = ($CsvContents | Get-Member | Where-Object { $_.MemberType -eq 'NoteProperty' }).Count
$Range = $worksheet.Range($worksheet.Cells($StartRowNum, $StartColumnNum), $worksheet.Cells($EndRowNum, $EndColumnNum))
$Range.Value2 = $MultiArray

For that to work you will also need the function I use for converting an object to a multi-dimensional array (based off the one posted here but with some slight modifications):

function ConvertTo-MultiArray
{
    param (
        $InputObject,
        [switch]$Headers = $false
    )
    begin
    {
        $Objects = @()
        [ref]$Array = [ref]$null
    }
    process
    {
        $Objects += $InputObject
    }
    end
    {
        $Properties = $Objects[0].PSObject.Properties | ForEach-Object{ $_.Name }
        $Array.Value = New-Object 'object[,]' ($Objects.Count + 1), $Properties.Count
        $ColumnNumber = 0
        if ($Headers)
        {
            $Properties | ForEach-Object{
                $Array.Value[0, $ColumnNumber] = $_.ToString()
                $ColumnNumber++
            }
            $RowNumber = 1
        }
        else
        {
            $RowNumber = 0
        }
        $Objects | ForEach-Object{
            $Item = $_
            $ColumnNumber = 0
            $Properties | ForEach-Object{
                if ($Item.($_) -eq $null)
                {
                    $Array.Value[$RowNumber, $ColumnNumber] = ""
                }
                else
                {
                    $Array.Value[$RowNumber, $ColumnNumber] = $Item.($_).ToString()
                }
                $ColumnNumber++
            }
            $RowNumber++
        }
        $Array
    }
}