Solved

import-csv into existing worksheet in excel and sort

Posted on 2014-09-13
9
796 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 68

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 68

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
 
LVL 68

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 68

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 68

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel VBA - copying formats 7 21
Can’t delete a file 14 83
Formula to copy cell and its "format" 3 26
Rearrange Macro 7 13
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now