Tag: powershell (Page 6 of 7)

Either I’m missing something or posting code snippets on WordPress sites is just not easy–or, at least, I haven’t yet stumbled upon the trick of it.  So far, I’ve posted PowerShell and Python code snippets and I’ve used the CodeColorer plugin to jazz up the snippets a little.  Initially, this worked fine with my PowerShell snippets:

(I’m wrapping my code with the CodeColorer syntax [ccn lang=”powershell” width=”600″] )

However, Python depends heavily on tabbing to denote if statements, for loops, and so forth and this adds another level of complexity to the snippets you post.  In HTML, to properly preserve tabs and spacing in your code, you generally have to turn the pre tag.  So, for my Python snippets, I turned to both the CodeColorer plugin and the SyntaxHighlighter TinyMCE Button to wrap my snippets in appropriately styled “pre” tags and get this:

1
2
3
4
5
6
7
8
#the old surround-the-string-with-parentheses technique

qry1 = ('SELECT fname ' +

        ',lname ' +

        ',job_title ' +

        'FROM people')



curs.execute(qry1)

curs.fetchall()

(Here, I’m wrapping my snippet with this: <pre class=”brush: python; gutter: true; first-line: 1″>[ccn lang=”python” width=”600″])

What in the world is going on with the large gray box around my code?  It appears to be a background style setting connected to the pre tag, complements of the stylesheet my theme uses. Fortunately, WordPress has a custom CSS feature, so I should be able to overwrite this behavior.

It took me a few attempts, but this is custom CSS I came up with: .preforcode pre {background: transparent}

To implement my custom class, I wrapped my “pre” tag with a div, like so (note that I think all that brush…python stuff that the SyntaxHighlighter TinyMCE Button places in the class attribute of the pre tags is nonsense, so I just removed the attribute altogether):
<div class=”preforcode”><pre>[ccn lang=”python” width=”600″]

1
2
3
4
5
6
7
8
#the old surround-the-string-with-parentheses technique

qry1 = ('SELECT fname ' +

        ',lname ' +

        ',job_title ' +

        'FROM people')



curs.execute(qry1)

curs.fetchall()

Sweet! That gray box is gone! The padding around my snippet is still there: I guess that kind of makes my snippet pop out, but I might now have to mess around with that a little and eventually add any modifications I come up with to my custom CSS. At any rate, at least the snippet looks better so, going forward, hopefully my code snippets will look a little more appealing.

Every-so-often I find myself having to do some processing with a Microsoft Excel file–grouping data, performing different statistical operations, etc. Doing much of this work within Excel itself is just a pain, so I would always open the file and save the applicable worksheets to CSV files.  But even manually saving the worksheets to CSV was a pain, so I devised a way to do the whole thing with PowerShell.

Unfortunately, PowerShell still has no convenient Convert-ExcelToCSV cmdlet…so I wrote my own. For added convenience, I built my function as a module and even added an Import-Module statement in my profile. Here are some quick steps on how I did that:

1. Under your %userprofile%\documents directory, make sure you have a WindowsPowerShell directory. If not, create one.
2. Under that WindowsPowerShell directory, create a modules directory, if one doesn’t already exist.
3. Finally, under the modules directory, create a directory called Convert-ExcelToCSV.
4. In that Convert-ExcelToCSV directory, drop a copy of my Convert-ExcelToCSV.psm1 module.
5. Back under the WindowsPowerShell folder, create a file named Microsoft.PowerShell_profile.ps1.
6. Edit Microsoft.PowerShell_profile.ps1 and add the statement Import-Module Convert-ExcelToCSV

(For more on PowerShell profiles, check out this Scripting Guy post)

So, that’s pretty cool. Now, you can open up a PowerShell command shell and save your Excel files to CSV in a one-liner:

But we can still make this even more convenient: how about, when I right-click on an Excel file, I get the option to convert the sucker to CSV right there? Well, we can do that by editing the Windows Registry.

According to my research, in order to cover the older XLS file versions as well as the newer XLSX versions, you have to edit two slightly different paths under the HKEY_CLASSES_ROOT hive. Check out my registry file for enabling this feature for the older file type and my other file for enabling this feature on new versions of Excel files.

To make these registry modifications, you can download my REG files and run them on your machine.

Warning: editing your Registry is a big deal. I’ve tested these on my system and they work, but make sure you review the modifications first and make sure you’re comfortable with everything before proceeding.

With these modifications, you can now convert your Excel files to CSVs with ease.

Python makes it even easier

So, I had to do a lot of work to get to a point where I can easily convert my Excel files to CSVs for easier processing down the line. Well, guess what? Python makes it even simpler with pandas.  Check out the function read_excel.  That is just awesome!

So, either way you go–PowerShell or Python–you have options to free yourself from some of the frustrations of Excel!