Styjun

Do Battery Electrons Only Move If There is a Positive Terminal at the End of the Wire?

How could I create a situation in which a PC has to make a saving throw or be forced to pet a dog?

Selecting unique column position of a non-zero value for each row of a matrix

I just entered the USA without passport control at Atlanta airport

Build a scale without computer

Having some issue with notation in a Hilbert space

How do credit card companies know what type of business I'm paying for?

Graphing large functions in LaTeX

Bash function: Execute $@ command with each argument in sequence executed separately

How do I gain the trust of other PCs?

Why swap space doesn't get filesystem check at boot time?

What is this plant I saw for sale at a Romanian farmer's market?

Is a sequel allowed to start before the end of the first book?

Using roof rails to set up hammock

How do I become a better writer when I hate reading?

How did the European Union reach the figure of 3% as a maximum allowed deficit?

Why was New Asgard established at this place?

What are the mechanical differences between Adapt and Monstrosity?

How did Frodo know where the Bree village was?

Will users know a CardView is clickable?

Digital signature that is only verifiable by one specific person

Do my partner and son need an SSN to be dependents on my taxes?

How did space travel spread throughout the Star Wars galaxy?

How can I prevent a user from copying files on another hard drive?

How to read multiline string in Excel Cell in powershell?

How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?How do you convert a byte array to a hexadecimal string, and vice versa?How to run a PowerShell scriptpowershell - paste data into excelHow do you comment out code in PowerShell?How do I concatenate strings and variables in PowerShell?How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loopsRead file line by line in PowerShellError while printing excel cell value in pythonPowershell script- add data to excel

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

I'm trying to use a powershell script to read through a certain column in my excel file and do some text processing to the data in that column. Each cell in that column may/may not contain multiline data.

What I did was loop through a cell, and for each cell I've looped, I've stored the data into a here-string and then loop through that here-string to read through each line in the multi-line text.

I'm able to read all lines in cells which contain multiple line data, however when the loop reaches cells with single line data, it does not display the singleline data out and comes out as a blank. I need some assistance to identify my issue. Would be greatly appreciated, thanks!

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Excel.DisplayAlerts = $false
$xlsPath = "C:UserscsjeemahDocumentsJeevanPowershell Scriptssample.xlsx"
$workbook = $Excel.Workbooks.Open("C:UserscsjeemahDocumentsJeevanPowershell Scriptssample.xlsx")
$workSheet = $workbook.Sheets.Item(1)
$workSheet.activate()
$worksheet.Columns.Replace("","/") 

$maxRows = (($workSheet.UsedRange.Rows).count) - 1
$sourceCol = 2
$sourceRow = 2
 for($souceRow = 1; $sourceRow -lt $maxRows; $sourceRow++) % $_.trim() 
 $lineCounter = 1
 for($src = 0; $src -le $sourceArray.count - 1 ; $src ++)
 echo $sourceArray[$src] 'this is line' $lineCounter 'in cell'
 $lineCounter = $lineCounter + 1
 
 
$workbook.SaveAs($xlsPath)
$workbook.close($false)
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
Remove-Variable Excel

arrays excel powershell

share|improve this question

asked Mar 25 at 4:49

Jeevan Daniel MahtaniJeevan Daniel Mahtani

114

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  why are you putting the data into a here-string? it should work just fine when assigned to a $Var without the here-string stuff.
  
  – Lee_Dailey
  Mar 25 at 5:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Why don't you give a try to Import-Excel module ?
  
  – HariHaran
  Mar 25 at 6:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tried assigning to a regular var but i can't loop through a regular var, won't it have to be stored as an array to be looped through?
  
  – Jeevan Daniel Mahtani
  Mar 25 at 6:10
  

  
  
  
  

add a comment | 

0

I'm trying to use a powershell script to read through a certain column in my excel file and do some text processing to the data in that column. Each cell in that column may/may not contain multiline data.

What I did was loop through a cell, and for each cell I've looped, I've stored the data into a here-string and then loop through that here-string to read through each line in the multi-line text.

I'm able to read all lines in cells which contain multiple line data, however when the loop reaches cells with single line data, it does not display the singleline data out and comes out as a blank. I need some assistance to identify my issue. Would be greatly appreciated, thanks!

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Excel.DisplayAlerts = $false
$xlsPath = "C:UserscsjeemahDocumentsJeevanPowershell Scriptssample.xlsx"
$workbook = $Excel.Workbooks.Open("C:UserscsjeemahDocumentsJeevanPowershell Scriptssample.xlsx")
$workSheet = $workbook.Sheets.Item(1)
$workSheet.activate()
$worksheet.Columns.Replace("","/") 

$maxRows = (($workSheet.UsedRange.Rows).count) - 1
$sourceCol = 2
$sourceRow = 2
 for($souceRow = 1; $sourceRow -lt $maxRows; $sourceRow++) % $_.trim() 
 $lineCounter = 1
 for($src = 0; $src -le $sourceArray.count - 1 ; $src ++)
 echo $sourceArray[$src] 'this is line' $lineCounter 'in cell'
 $lineCounter = $lineCounter + 1
 
 
$workbook.SaveAs($xlsPath)
$workbook.close($false)
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
Remove-Variable Excel

arrays excel powershell

share|improve this question

asked Mar 25 at 4:49

Jeevan Daniel MahtaniJeevan Daniel Mahtani

114

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  why are you putting the data into a here-string? it should work just fine when assigned to a $Var without the here-string stuff.
  
  – Lee_Dailey
  Mar 25 at 5:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Why don't you give a try to Import-Excel module ?
  
  – HariHaran
  Mar 25 at 6:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tried assigning to a regular var but i can't loop through a regular var, won't it have to be stored as an array to be looped through?
  
  – Jeevan Daniel Mahtani
  Mar 25 at 6:10
  

  
  
  
  

add a comment | 

I'm trying to use a powershell script to read through a certain column in my excel file and do some text processing to the data in that column. Each cell in that column may/may not contain multiline data.

What I did was loop through a cell, and for each cell I've looped, I've stored the data into a here-string and then loop through that here-string to read through each line in the multi-line text.

I'm able to read all lines in cells which contain multiple line data, however when the loop reaches cells with single line data, it does not display the singleline data out and comes out as a blank. I need some assistance to identify my issue. Would be greatly appreciated, thanks!

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Excel.DisplayAlerts = $false
$xlsPath = "C:UserscsjeemahDocumentsJeevanPowershell Scriptssample.xlsx"
$workbook = $Excel.Workbooks.Open("C:UserscsjeemahDocumentsJeevanPowershell Scriptssample.xlsx")
$workSheet = $workbook.Sheets.Item(1)
$workSheet.activate()
$worksheet.Columns.Replace("","/") 

$maxRows = (($workSheet.UsedRange.Rows).count) - 1
$sourceCol = 2
$sourceRow = 2
 for($souceRow = 1; $sourceRow -lt $maxRows; $sourceRow++) % $_.trim() 
 $lineCounter = 1
 for($src = 0; $src -le $sourceArray.count - 1 ; $src ++)
 echo $sourceArray[$src] 'this is line' $lineCounter 'in cell'
 $lineCounter = $lineCounter + 1
 
 
$workbook.SaveAs($xlsPath)
$workbook.close($false)
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
Remove-Variable Excel

arrays excel powershell

share|improve this question

asked Mar 25 at 4:49

Jeevan Daniel MahtaniJeevan Daniel Mahtani

114

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Excel.DisplayAlerts = $false
$xlsPath = "C:UserscsjeemahDocumentsJeevanPowershell Scriptssample.xlsx"
$workbook = $Excel.Workbooks.Open("C:UserscsjeemahDocumentsJeevanPowershell Scriptssample.xlsx")
$workSheet = $workbook.Sheets.Item(1)
$workSheet.activate()
$worksheet.Columns.Replace("","/") 

$maxRows = (($workSheet.UsedRange.Rows).count) - 1
$sourceCol = 2
$sourceRow = 2
 for($souceRow = 1; $sourceRow -lt $maxRows; $sourceRow++) % $_.trim() 
 $lineCounter = 1
 for($src = 0; $src -le $sourceArray.count - 1 ; $src ++)
 echo $sourceArray[$src] 'this is line' $lineCounter 'in cell'
 $lineCounter = $lineCounter + 1
 
 
$workbook.SaveAs($xlsPath)
$workbook.close($false)
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
Remove-Variable Excel

share|improve this question

asked Mar 25 at 4:49

Jeevan Daniel MahtaniJeevan Daniel Mahtani

114

share|improve this question

asked Mar 25 at 4:49

Jeevan Daniel MahtaniJeevan Daniel Mahtani

114

asked Mar 25 at 4:49

Jeevan Daniel MahtaniJeevan Daniel Mahtani

114

asked Mar 25 at 4:49

Jeevan Daniel MahtaniJeevan Daniel Mahtani

114