Solved

import-csv into existing worksheet in excel and sort

Posted on 2014-09-13
9
877 Views
Last Modified: 2014-09-14
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
Comment
Question by:Mandy_
  • 5
  • 4
9 Comments
 
LVL 69

Expert Comment

by:Qlemo
ID: 40320930
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
 
LVL 2

Author Comment

by:Mandy_
ID: 40321057
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 40321124
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

 
LVL 69

Expert Comment

by:Qlemo
ID: 40321138
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
 
LVL 2

Author Comment

by:Mandy_
ID: 40321585
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 40321630
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
 
LVL 2

Author Comment

by:Mandy_
ID: 40321801
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
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 40321831
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
 
LVL 2

Author Closing Comment

by:Mandy_
ID: 40322457
Thank you so much for your help
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question