import-csv into existing worksheet in excel and sort

You can't sort a Get-Content result for properties - it consists of just strings, with one line per string. You'll first have to do the convertion to CSV. So line 32 should be:

Get-Content $strCsvFile | % { $_ -replace "\s{2,}",";" } | ConvertFrom-Csv -Delimiter ";" |
  sort action -descending | % {

Select allOpen in new window

Next step is to fill the "correct" rows of the worksheet depening on action. But the sort order is different than the order of rows to populate, so it only serves the purpose of having same action values clustered. IMHO the sort should be different, e.g. action and username, or only username.

Function Get-FormsOpenFileDialog([string]$InitialDirectory, [string]$Filter = 'All files (*.*)|*.*', [uint32]$FilterIndex = 1) {
  [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null
  If ([string]::IsNullOrEmpty($InitialDirectory) -Or (-Not (Test-Path -Path $InitialDirectory))) {
    $InitialDirectory = $ENV:UserProfile
  }
  $OpenFileDialog = New-Object -TypeName System.Windows.Forms.OpenFileDialog
  $OpenFileDialog.InitialDirectory = $InitialDirectory
  $OpenFileDialog.Filter = $Filter
  $OpenFileDialog.FilterIndex = $FilterIndex
  $Result = $OpenFileDialog.ShowDialog()
  If ($Result -eq [System.Windows.Forms.DialogResult]::OK) {
    Return $OpenFileDialog.FileName
  }
}

$strXlsFile = "C:\temp\excel.xlsx"
$strCsvFile = Get-FormsOpenFileDialog -InitialDirectory "C:\Temp" -Filter "Comma separated values file (*.csv)|*.csv"
If (-Not (Test-Path -Path $strXlsFile)) {
  "Script canceled, xls file '$strXlsFile' not found!" | Write-Host -ForegroundColor Red
  Exit 1
}
If (-Not (Test-Path -Path $strCsvFile)) {
  "Script canceled, no csv file selected!" | Write-Host -ForegroundColor Red
  Exit 1
}

$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $false
$ws = $objExcel.Workbooks.Open($strXlsFile).sheets.item("TEST")

$rows    = @{new =   1; delete = 151; we = 180}
$rowxmax = @{new = 150; delete = 151; we = 180]
Get-Content $strCsvFile | % { $_ -replace "\s{2,}",";" } |
  ConvertFrom-Csv -Delimiter ";" |
  sort action -descending | % {
    $row = $rows[$_.action]
    if ($row -le $rowsmax[$_.action])
    {
      $i = 1
      $ws.cells.item($row, $i++).value2 = $_.UserID
      $ws.cells.item($row, $i++).value2 = $_.Name
      $ws.cells.item($row, $i++).value2 = $_.Location
      $ws.cells.item($row, $i++).value2 = $_.Employee
      $ws.cells.item($row, $i++).value2 = $_.IdentNr
      $ws.cells.item($row, $i++).value2 = $_.ANo
      $ws.cells.item($row, $i++).value2 = $_.Interface
      $ws.cells.item($row, $i++).value2 = $_.Date
      $ws.cells.item($row, $i++).value2 = $_.Action
      $ws.cells.item($row, $i++).value2 = $_.Product
      $ws.cells.item($row, $i++).value2 = $_.Version
      $ws.cells.item($row, $i++).value2 = $_.SMTP
      $ws.cells.item($row, $i++).value2 = $_.SMTP2
      $rows[$_.action]++
    }
  }  
$ws.parent.SaveAs($path) 
$objExcel.Quit() 

Select allOpen in new window

Thank you so much for your help.  i'm getting  error messages as follows:
It is not possible to use an index to a null array.
csv2excel.ps1: 38 characters: 9
+ If ($ row -le $ rowsmax [$ _. Action])

Error index operation. The array index was evaluated as zero.
In csv2excel.ps1: 37 characters: 5
+ $ Row = $ rows [$ _. Action]
 
I think the error is not due the CSV-Data. I tried different versions of CSV

UserId;Name;Lokation;Employee;IdentNr;ANo;Interface;Date;Action;Product;Version;SMTP;smtp2
Z123456;DANG, XIUFENG;BJS;I;2476311;O181062-003;Shop LP;2013-07-12 12:03:34;Delete;Premium;MSXC;

i changed  
Get-Content $strCsvFile | % { $_ -replace "\s{2,}",";" } | ConvertFrom-Csv -Delimiter ";" |
sort aktion -descending | % {

to just
Import-Csv -Path $strCsvFile -Delimiter ';' | % {

or
Get-Content $strCsvFile | ConvertFrom-Csv -Delimiter ";" | sort aktion -descending | % {

always the same error. Any idea?

Wait. Why was that replace there in the first place? I was expecting your "CSV" file to contain no real delimiter, just at least two spaces, between columns - and the replace was fixing that.

Line 32 has a typo. Strange that causes no syntax error while parsing ...

$rowxmax = @{new = 150; delete = 151; we = 180}

Select allOpen in new window

Dear qlemo,

i modified your script below and it's working so far. Maybe you could check if anything could be make better?
Thanks in advance.

Function Get-FormsOpenFileDialog([string]$InitialDirectory, [string]$Filter = 'All files (*.*)|*.*', [uint32]$FilterIndex = 1) {
	[System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null
	If ([string]::IsNullOrEmpty($InitialDirectory) -Or (-Not (Test-Path -Path $InitialDirectory))) {
		$InitialDirectory = $ENV:UserProfile
	}
	$OpenFileDialog = New-Object -TypeName System.Windows.Forms.OpenFileDialog
	$OpenFileDialog.InitialDirectory = $InitialDirectory
	$OpenFileDialog.Filter = $Filter
	$OpenFileDialog.FilterIndex = $FilterIndex
	$Result = $OpenFileDialog.ShowDialog()
	If ($Result -eq [System.Windows.Forms.DialogResult]::OK) {
		Return $OpenFileDialog.FileName
	}
}



$map=@{}
$map['NEW'] = @{}
$map['DELETE'] = @{}
$map['WE'] = @{}
$map['New']=2
$map['DELETE']=151
$map['WE']=180


$strXlsFile = "C:\temp\test.xlsx"
$strCsvFile = Get-FormsOpenFileDialog -InitialDirectory "C:\Temp" -Filter "Comma separated values file (*.csv)|*.csv"
If (-Not (Test-Path -Path $strXlsFile)) {
	"Script canceled, xls file '$strXlsFile' not found!" | Write-Host -ForegroundColor Red
	Exit 1
}
If (-Not (Test-Path -Path $strCsvFile)) {
	"Script canceled, no csv file selected!" | Write-Host -ForegroundColor Red
	Exit 1
}

$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $true
$WorkBook = $objExcel.Workbooks.Open($strXlsFile)
$worksheet = $workbook.sheets.item("TEST")

Get-Content $strCsvFile | % { $_ -replace "\s{2,}",";" } | ConvertFrom-Csv -Delimiter "," | % {
$aktion = $_.aktion	
#$ver = $_.version
	#if (!($ver -eq '')) {	
		#$aktion = $_.aktion
		$rowNum = $map[$aktion]#[$ver]
		$col = 1
		$_.PSObject.Properties | %{
			$worksheet.cells.item($rowNum,$col++) = $_.value
		}
		$map[$aktion] = $rowNum+1  #[$ver]
	}
#}




$WorkBook.Save()
$objExcel.Quit()

Select allOpen in new window

Some hints:

$map=@{}
$map['NEW'] = @{}
$map['DELETE'] = @{}
$map['WE'] = @{}
$map['New']=2
$map['DELETE']=151
$map['WE']=180

Select allOpen in new window

is "nonsense", as you are replacing the empty hash tables by values. I suppose that results from keeping the version in the map in your original code not shown here (except in comment fragments). Without version you can write it much shorter, as I've shown already. And be aware that your code is not failsafe - more than 150 "new" entries will result in overwriting areas you do not want them to be in (hence my rowsmax hash table). If you want to write it that way, just remove lines 19 to 21, but this is better:

$map = @{'new' = 2; 'delete' = 151, 'we' = 180}

Select allOpen in new window

Next point: Small enumerations like in

$_.PSObject.Properties | %{
	$worksheet.cells.item($rowNum,$col++) = $_.value
}

Select allOpen in new window

should rather be used with a foreach statement, because that is faster for in-memory operations:

foreach ($prop in $_.PSObject.Properties) {
	$worksheet.cells.item($rowNum,$col++).value2 = $prop.value
}

Select allOpen in new window

But what is dangerous about that way to read out object properties: The sequence of properties is not warranted, and might be different from what you expect. Probably it works here, but don't expect it to be safe.

Lastly, I hate it if intermediate vars are used without need. $aktion (and $ver, if reactivated) don't serve any purpose over $_.aktion and $_.version here. Using intermediate vars might be advisable (or even required) if there are nested foreach-object loops, some additional references needed, or long-winded property names used, but in this "simple" statement block there is no need to do ;-).

i replaced  

$map = @{'new' = 2; 'delete' = 151, 'we' = 180}

and
foreach ($prop in $_.PSObject.Properties) {
      $worksheet.cells.item($rowNum,$col++).value2 = $prop.value

the excel document imports nothing.

#$map=@{}
#$map['NEW'] = @{}
#$map['DELETE'] = @{}
#$map['WE'] = @{}
#$map['New']=2
#$map['DELETE']=151
#$map['WE']=186
#
$map = @{'new' = 2; 'delete' = 151; 'we' = 186}

Select allOpen in new window

$aktion = $_.aktion	
#$ver = $_.version
	#if (!($ver -eq '')) {	
		#$aktion = $_.aktion
		$rowNum = $map[$aktion]#[$ver]
		$col = 1

}
	foreach ($prop in $_.PSObject.Properties) {
	$worksheet.cells.item($rowNum,$col++).value2 = $prop.value
}	
#$_.PSObject.Properties | %{
			#$worksheet.cells.item($rowNum,$col++) = $_.value
		#}
		$map[$aktion] = $rowNum+1  #[$ver]

Select allOpen in new window

Thank you so much for your help