• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1044
  • Last Modified:

import-csv into existing worksheet in excel and sort

hi,
this powershell code below prepares me headache. Could anybody assist?

What it should do?

1.Open a csv (fileDialog) with following colums included:
 "UserID"  "Name" "Location" "Employee"  "IdentNr" "ANo" "Interface"  "Date"  "Action" "Product" "Version""SMTP""smtp2"


2.Import into a existing excel file / worksheet "TEST"  from Cell:A1

3.Sort everything by column  action

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

4.If possible sort the 3 different values of column Action (delete,new,we) to different rows like "new" 1-150 "Delete" row "151" and "WE" row 180


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
$WorkBook = $objExcel.Workbooks.Open($strXlsFile)
$worksheet = $workbook.sheets.item("TEST")

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


$excel.cells.item(1,1) = "UserID" 
$excel.cells.item(1,2) = "Name" 
$excel.cells.item(1,3) = "Location" 
$excel.cells.item(1,4) = "Employeetyp" 
$excel.cells.item(1,5) = "IdentNr" 
$excel.cells.item(1,6) = "ANo" 
$excel.cells.item(1,7) = "Interface" 
$excel.cells.item(1,8) = "Date" 
$excel.cells.item(1,9) = "Action" 
$excel.cells.item(1,10) = "Product" 
$excel.cells.item(1,11) = "Version"
$excel.cells.item(1,12) = "SMTP"
$excel.cells.item(1,13) = "smtp2"
}  
$workbook.saveas($path) 
$Excel.Quit() 

Open in new window

0
Mandy_
Asked:
Mandy_
  • 5
  • 4
1 Solution
 
QlemoDeveloperCommented:
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 | % {

Open 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() 

Open in new window

0
 
Mandy_Author Commented:
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?
0
 
QlemoDeveloperCommented:
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.
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
QlemoDeveloperCommented:
Line 32 has a typo. Strange that causes no syntax error while parsing ...
$rowxmax = @{new = 150; delete = 151; we = 180}

Open in new window

0
 
Mandy_Author Commented:
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()

Open in new window

0
 
QlemoDeveloperCommented:
Some hints:
$map=@{}
$map['NEW'] = @{}
$map['DELETE'] = @{}
$map['WE'] = @{}
$map['New']=2
$map['DELETE']=151
$map['WE']=180

Open 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}

Open in new window

Next point: Small enumerations like in
$_.PSObject.Properties | %{
	$worksheet.cells.item($rowNum,$col++) = $_.value
}

Open 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
}

Open 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 ;-).
0
 
Mandy_Author Commented:
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}

Open 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]

Open in new window

0
 
QlemoDeveloperCommented:
You should get a lot of errors that way. Line 8 of the second code snippet belongs to the commented IF, and so needs to be commented too.
My $map suggestion in http:#a40321630 has been wrong (comma instead of semi-colon), but I see you have corrected that already.

Note: You might have spotted the difference in your code and mine regarding usage of .value2. Indeed it works in PowerShell, at least here, to omit that default property (as you can do in VBA), but that is not reliable.
0
 
Mandy_Author Commented:
Thank you so much for your help
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now