PowerShell v5 looks to vastly increase the performance working with the excel com objects.
________________________________________ From: [email protected] [[email protected]] on behalf of Webster [[email protected]] Sent: Thursday, June 11, 2015 1:13 AM To: [email protected] Subject: [powershell] RE: Hashing Question For Excel, I found creating either a hash or object, then saving to CSV and then importing the CSV was many times faster than doing the work completely in Excel. Webster -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Brown, Ken F. Sent: Wednesday, June 10, 2015 7:26 PM To: [email protected] Subject: [powershell] RE: Hashing Question Thanks...that 'notypeinformation' does the trick! And doing this seems much faster than using an excel object (although with a debug option enabled, it is kinda cool to watch the excel tables get generated while the rows are being populated!) And, from some testing (adding over 200 rows) - it doesn't seem to confuse the columns in the hash (although I did define the columns before using them after creating the table). Like this: $hash=@{} $hash.name="" $hash.phone="" $hash.state="" Then later assigned the values (only I did this with about 15 columns, not 3). $hash.name=$i.name $hash.phone=$i.phone $hash.state=$i.state -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Webster Sent: Friday, May 29, 2015 10:33 AM To: [email protected] Subject: [powershell] RE: Hashing Question This email has an alternate reply address set. ---------------------------------------------------------------------- Sent by an external sender ---------------------------------------------------------------------- $results | export-csv testout.csv -NoTypeInformation Will get rid of the #Type stuff. Thanks Webster > -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf Of Brown, Ken F. > Sent: Friday, May 29, 2015 9:29 AM > To: [email protected] > Subject: [powershell] Hashing Question > > I put together this script as a test so I can take random input, > format it for a CSV file and then export the CSV. (I'm planning to > have 10-15 input fields, but for a test, this works). > > I used to use an Excel COM method to do this - which works for large > datasets - but requires Excel to be installed. When running on > servers, people don't really like having to install Excel (which I > agree) - hence this method. > > My question is...will the columns ever become misaligned when the "hash" > below is created and when it is added to the 'results'? > > I.e. in this example, will the 'name' and 'phone' columns ever get swapped? > Using excel method, I know it won't as I force the data into specific > columns (using numbers, not names) > > Also, not sure why the #TYPE line is added to the output...but an > import-csv of the file ignores it. > > Thanks > > P.S. I'm doing this to get lots of data (15 -20 columns) from > mailboxes and will use for other stuff now, also > > > PS C:\temp\Powershell> type .\testinp.csv The input file > Name,phone,state > Joe,5551212,confusion > Happy,1234567,excited > Tired,5552121,sleepy > > > PS C:\temp\Powershell> .\testexp.ps1 Executing the > script > > PS C:\temp\Powershell> type .\testexp.ps1 The script > $list=import-csv testinp.csv > > $results=@() > > foreach ($i in $list) { > $hash=@{} > $hash.name=$i.name > $hash.phone=$i.phone > $hash.state=$i.state > > $newrecord=new-object psobject -property $hash > > $results+=$newrecord > } > > $results | export-csv testout.csv > > > > PS C:\temp\Powershell> type .\testout.csv The output > file > #TYPE System.Management.Automation.PSCustomObject > "name","phone","state" > "Joe","5551212","confusion" > "Happy","1234567","excited" > "Tired","5552121","sleepy" > > > > ================================================ > Did you know you can also post and find answers on PowerShell in the > forums? > http://www.myitforum.com/forums/default.asp?catApp=1 ================================================ Did you know you can also post and find answers on PowerShell in the forums? http://www.myitforum.com/forums/default.asp?catApp=1 ================================================ Did you know you can also post and find answers on PowerShell in the forums? http://www.myitforum.com/forums/default.asp?catApp=1 ================================================ Did you know you can also post and find answers on PowerShell in the forums? http://www.myitforum.com/forums/default.asp?catApp=1 ================================================ Did you know you can also post and find answers on PowerShell in the forums? http://www.myitforum.com/forums/default.asp?catApp=1
