Thursday, May 14, 2015

Use PowerShell to Save Excel Worksheet as CSV

I'm working on a PowerShell script to launch some virtual servers on VMWare based on input from a CSV file. To create the CSV file I'm using Excel, and I have more than one worksheet in the Excel workbook. Since it's a bit of a hassle to use Excel's GUI to save a given worksheet as a CSV, especially when this has to be done more than once, I set out to find a way to do this programatically. At first I was indifferent on using either a macro within Excel, or a PowerShell method. I decided on PowerShell because I'm already using a PS script to launch the servers, so I decided I would write a block of code to open my spreadsheet and create a CSV from the desired worksheet.

Excel spreadsheet file.
In my quest to figure this out I ran across a lot of pieces, but not a complete solution, and as is often the case on the web I couldn't find explanations of some of the solutions. That's what prompted me to save this all here. I'll step through some of the logic, then post the script in its entirety at the bottom. The best resource I found, although not complete (for my needs), was this great post from LazyWinAdmin.

First thing is to set some variables specific to my environment, the path and name to both my input (xlsx) and output (csv) files, and the name of the tab or worksheet in the spreadsheet.
$Sheet = "CreateVM-CA"
$xls = "C:\Temp\PC\PCTest.xlsx"
$csv = "C:\Temp\PC\$Sheet.csv"

NOTE: Make sure you save your Excel file as this script reads the file from disk & not what's in an open workbook.

Next we will open Excel, the workbook, and the specific worksheet we want to export. I'm also using "Excel.Visible = $False" to prevent the Excel GUI from opening, and "Excel.DisplayAlerts = $False" to prevent messages such as being prompted to overwrite the file if it already exists (see below).
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $False
$objExcel.DisplayAlerts = $False
$WorkBook = $objExcel.Workbooks.Open($xls)
$WorkSheet = $WorkBook.sheets.item("$Sheet")

Prompted to replace existing file (when not using DisplayAlerts=$False)
A useful note (again thanks to LazyWinAdmin) is that we can verify excel has opened, and we are working with the desired workbook using the following, which lists the available worksheets within the opened workbook.
$WorkBook.sheets | Select-Object -Property Name

Listing Excel worksheets with PowerShell.
Saving the file. When saving the file we will, of course specify that it is a CSV, but also the file type (CSV) is designated with the number 6. For some reason you can't pass the number 6 directly in the SaveAs command, so we are using the variable xlCSV. In my discovery I ran across a list of several file types, but cannot find that now. It's out there somewhere....
$xlCSV = 6

Closing/quitting Excel. Initially I was using just the first command to quit Excel. However, I discovered that it remained open and subsequent executions of my script would fail to write the file because the open Excel process had the file locked. So after a little research and testing I found the second command which kills the process, and prevents the CSV file from being locked.

This little script works beautifully now and does just what I need by opening a specific worksheet from an Excel workbook, then saving it as a CSV file. Here's the entire script.
$Sheet = "CreateVM-CA"
$xls = "C:\Temp\PC\PCTest.xlsx"
$csv = "C:\Temp\PC\$Sheet.csv"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $False
$objExcel.DisplayAlerts = $False
$WorkBook = $objExcel.Workbooks.Open($xls)
$WorkSheet = $WorkBook.sheets.item("$Sheet")
$xlCSV = 6

Output CSV file.

Sunday, April 19, 2015

History of Internet Addressing

Volumes have been written about the Internet and its protocols so what I have here is nothing new or ground-breaking. It is, however, a good overview of the history of IP addressing, related Request For Comments (RFC) and other links.

I've been working with the Internet Protocols for a while, including data center builds, network operations and teaching various Cisco (CCNA), Microsoft (MCSE), Novell (CNE), etc. certification and training courses over the years. And although I do know a fair bit about the Internet and IP in particular I actually learned a few new things recently. Or I should say while doing a little research for a new project I had some thoughts gel in my mind which led me to record this information. This will be a useful resource for me, my future students, and hopefully you as well.

If you'd like to cut to the chase scroll down to the summary/timeline at the end.

Origins of The Internet
Host-to-host communications via packet-switched networks was in its infancy in the late 1960's with the Advanced Research Projects Agency Network (ARPANET), from which grew TCP/IP, AKA "The Internet Protocol Suite", with IP and IP addressing at its core. Two key pioneers of the Internet and the godfathers of IP, Bob Kahn and Vint Cerf, had to go through a lot of experimentation and work before finally coming up with what we know today as IPv4. First we'll discuss the IP versions prior to 4, then subsequent enhancements of IPv4 and finally the next generation of IP. The previous versions of IP where part of the initial experimentation. An example of one of these earlier versions is IPv2 as documented in Draft Internetwork Protocol Description Version 2. This and the other preceding versions are listed in table 1 along with the dates they were published.

Table 1
Version  Date
0        March 1977
1        January 1978
2        February 1978 version A
3        February 1978 version B
4        August 1979 version 4

In January of 1980 Jon Postel, who was editor and/or writer of many RFCs, published RFC 760, "DOD STANDARD, INTERNET PROTOCOL," the first IPv4 RFC. At this point it is important to note no concept of address classes, subnetting, CIDR, etc. were defined at this point, therefore they didn't exist. All of those things came along later to enhance the Internet Protocol addresses. So in this original definition IPv4 used the first 8 bits as the network address, and the last 24 bits as the "host" portion of the address, which was termed at the time Source or Destination Local Address (see RFC 760, pages 14 and 22 for exact wording).

In a 2011 interview, Vint Cerf, on of the Internet Founding Fathers said of IPv4, "I thought it was an experiment and I thought that 4.3 billion [IPv4 addresses] would be enough to do an experiment." He said he never thought, "this experiment wouldn't end." So for an "experiment" IPv4 is doing pretty damn good! Albeit with a few enhancements over the next several years.

Looking back to the 1970's computers hadn't yet reached the mainstream so it wasn't imagined by the Pioneers that within a few short years we would see an explosion of networks and devices connecting to the Internet. This is clearly evident in RFC 762 titled "Assigned Numbers," which lists 33 "networks" out of a possible 254 (0 and 255 being reserved). Again, IP address classes hadn't yet been defined.... This RFC was published in January 1980, but it also lists when the 0-3 Assigned IP versions were initially defined, giving us a little more insight into the timeline of IP address versions.

From this we can see that IP addressing began to be defined around March 1977 with IPv0. Then by August 1979 IPv4, the main version on which the Internet was built and is still widely in use today, was defined. Now, over 35 years on, the Internet runs mainly with the "experimental" IP (v4) addressing created by Vint Cerf and Bob Kahn.

Classful IPv4 Addresses
Fairly quickly it was apparent to these Founding Fathers that the intial 8/24 (nnnnnnnn.hhhhhhhh.hhhhhhhh.hhhhhhhh) addressing scheme initially defined by IPv4 needed an enhancement. In September 1981 RFC 791 defined IP address classes in order to "provide for flexibility in assigning address to networks and allow for the large number of small to intermediate sized networks the interpretation of the address field is coded to specify a small number of networks with a large number of host, a moderate number of networks with a moderate number of hosts, and a large number of networks with a small number of hosts." Since this was just an enhancement or further definition of the use of the 32 bit IPv4 address scheme it wasn't necessary to increment the version.

This enhancement to IPv4 is known as classful IP addressing. Table 2 provides a quick view of the classes A, B and C as a reference. Much more about these classes, etc. are available elsewhere on the Internet.

Table 2
Class Leading Bits Network Bits Host Bits Number of networks Addresses per network Start address End address
A 0 8 24 128 (2^7)  16,777,216 (2^24)
10 16 16 16,384 (2^14)  65,536 (2^16)
110 24 8 2,097,152 (2^21)  256 (2^8)
NOTE: classful addressing was superseded by classless (AKA CIDR) addressing. Although this is the case it is common to discuss and have classful addresses referenced in documentation. While this technically shouldn't be done this network engineer hasn't seen much if any decrease in classful address references in my 25 years in the business.

In 1984 another evolution of IPv4 addressing was introduced, Internet Subnets with RFC 917. This explains both the need for and way to use part of the host portion of the address for a subnet, effectively creating something like nnnnnnn.ssssssss.hhhhhhhh.hhhhhhhh, where a "Class A" address is subnetted by taking 8 bits (as an example, more or less could be used of course) of the host address fields to allow for up to 256 subnets. This necessitated the creation of a way for hosts to know which portion of their address was used for the network and subnet vs. the host portion of the address, therefore the birth of the subnet mask. In the case listed above just about everyone familiar with IP addressing will know the mask would be In August 1985 RFC 950, "Internet Standard Subnetting Procedure," further defines subnetting and subnet masks.

NOTE: In several early RFCs you'll see the term "catenet." This depricated term was defined in the early days of packet-switched networking and referred to a network of networks, or an internetwork or internet (lower case "i").

Governing Bodies
By the mid- to late-1980's organizations like the Internet Engineering Task Force (IETF), Internet Assigned Numbers Authority (IANA), its parent Internet Corporation for Assigned Names and Numbers (ICANN), and others were formed to steer, define and coordinate protocols, growth and enhancements of the Internet and Internet Protocols.

Classless IP Addressing - CIDR
In September 1993 IETF introduced Classless Inter-Domain Routing (CIDR) which is a method for allocating IP addresses and routing IP packets. CIDR replaces the previous addressing architecture of classful addresses, with the goal to slow the growth of routing tables on routers across the Internet, and to help slow the rapid exhaustion of IPv4 addresses. Once again the "experimental" IPv4 address space was modified to extend its life. Due to the lack of scalability of classful (A, B, C, etc.) addresses CIDR provides the ability to "subnet" an IPv4 address at virtually any bit value. This is done by appending the number of bits used for the network (technically network/subnet) to the IP address. For example provides for a network with up to 1022 hosts. Much is available on CIDR around the web, including numerous subnetting/CIDR charts, but a few references are Wikipedia's "Classless Inter-Domain Routing", and RFC 1518, "An Architecture for IP Address Allocation with CIDR", and RFC 1519, "Classless Inter-Domain Routing (CIDR): an Address Assignment and Aggregation Strategy."

Private Addresses, NAT & PAT
As early as the 1980's it was apparent that IPv4's 32-bit address space would become exhausted. A variety of factors contributed to this with the main one being the explosive growth of networks and devices worldwide participating in and connecting to the Internet. So in addition to the aforementioned classful, classless and subnetting of IPv4 network address translation (NAT) and port address translation (PAT) were developed. Currently NAT & PAT are widely deployed, so much so that they are the norm. But, these actually break the end-to-end communications originally envisioned with the Internet and introduce other problems or challenges. This is something IPv6 addresses, but let's not get ahead of ourselves. NAT and more specifically PAT  has become a popular and essential tool in conserving global address space allocations in face of IPv4 address exhaustion.

Of course in order to effectively use NAT/PAT a set of private, non-publicly routable IP addresses had to be defined, which was done in February 1996 in RFC 1918, "Address Allocation for Private Internets." (See also, RFC 5735, "Special Use IPv4 Addresses.")

Table 3 - Private IPv4 Address Ranges     -  (10/8 prefix)   -  (172.16/12 prefix)  - (192.168/16 prefix)

This discussion wouldn't be complete without touching on version 5. It was an experimental family of protocols for streaming voice, video, etc. called Internet Streaming Protocol in 1979, but was never fully developed. If you are so inclined see RFC 1190, "Experimental Internet Stream Protocol."

Along with enhancing IPv4, by 1994 IETF began to define the next generation of IP with IPv6, sometimes called IPng. This took several years and in December 1998 RFC 2460, "Internet Protocol, Version 6 (IPv6) Specification" was published. Since IPv6 was defined about 20 years after IPv4 a lot of the former protocol's shortcomings were addressed. In addition to adding native security to IP with IPSec, restoring the end-to-end communications model (doing away with NAT), IPv6 increases the address space. A lot!

By using a 32 bit address IPv4 has a total of about 4.3 billion (2^32) available numbers. IPv6 is 128 bits, which provides a bit over 340 undecillion (2^128) addresses. Table 3 shows just how large this address space is. With such a large address space it is not necessary to define address classes (no "classful") in IPv6, nor is it necessary to use a subnet mask. Rather, since IPv6 was built upon the concept of Classless Inter-Domain Routing IPv6 addresses are written with a trailing /xx (example: 2001:db8:abcd:3f00::/64). In most cases organizations will receive a /64 or /48 address space from either an ISP or IANA, then they will be able to use CIDR to suit their needs.

Table 4
IP4 addresses (2^32)  - 4,294,967,296
IP6 addresses (2^128) - 340,282,366,920,938,463,463,374,607,431,768,211,456

Although IPv4 was an "experimental" addressing scheme born three decades ago it has seen numerous enhancements with classes, subnetting, CIDR and NAT/PAT to extend its lifespan, and it's still going strong. IPv6 has been around for nearly two decades and its use is definitely picking up steam but I believe IPv4 will be around for quite some time. In fact, it's likely the two will run in parallel perhaps for as long as the Internet exists.

  • Mar 1977: IP addressing first defined and documented, starting with IPv0
  • Aug 1979: IPv4 defined (RFC 760)
  • Sep 1981: IPv4 classful addresses defined (RFC 791)
  • Oct 1984: IPv4 subnetting introduced (RFC 917)
  • Aug 1985: IPv4 subnetting further defined (RFC 950)
  • Sep 1993: IPv4 classless (AKA CIDR) addresses defined (RFC 1518, and RFC 1519)
  • Feb 1996  IPv4 private addresses and NAT/PAT  (RFC 1918, and RFC 5735)
  • Dec 1998 IPv6 defined (RFC 2460)