Shortest Path from CSV to Excel

So, there are lots and lots of options out there these days for converting from CSV to Excel. You can find dozens of helpful scripts and functions with little more than a quick Google search. So, already you’re probably thinking that, with so many pre-built options why on earth he is writing this blog post. Well, I’m glad you asked (even if you don’t, just go with me on this).

What I noticed about most of these available options is a complete lack of consistency in their approach of how they used the ‘excel.application’ com object. Some methods involved creating new worksheets and copying content in, while others used cell based approaches amongst many others. Another thing these all had in common was how very LONG all these functions were, even when they had minimal error handling. This was somewhat confusing to me since I could open Excel and do a CSV import fairly quickly without needing to do copies or create worksheets, so there had to be a faster way. Again, I’m sure you’re sitting there thinking that you could just grab a script from online and carry on, and you could…but what happens if you don’t have internet access, or you are in a hurry and don’t want to copy a script for a simple execution? What? These thoughts didn’t occur to you, or you really don’t care? Well they occurred to me and, because I’m a compulsive sharer, I’m now writing this article.

So, let’s break it down to the bare basics of what we are trying to accomplish, namely:

1. Initiate Excel
2. Import our CSV file, complete with cells and columns
3. Save the file out to the latest Excel format
4. Close out Excel
5. Move on with our lives…oh, and we want to do all this without having to see Excel, deal with any prompts, and without having to resort to a script

Now, of course, this all assumes nothing with regards to pretty formatting, or auto-fitting, or anything fancy…just dump to Excel and move on.

First, of course, we have to bind to Excel like so:

$excel = New-Object –com excel.application

Easy enough. Now, the next part is to suppress any Excel alerts that might bother us with things we don’t care about, which isn’t strictly necessary when interactive, but it is if you are batching, so we do that like so:

$excel.DisplayAlerts = $false

Now for the usually tricky part, as this is where most scripts start doing all kinds of crazy things…open the csv file in Excel and save it out as a true Excel file, which we can do in just TWO lines, like so:

$excel.Workbooks.OpenText(“C:\mypath\myfile.csv”,2,1,1,1,$false,$false,$false,$true)

$exce.ActiveWorkbook.SaveAs(“C:\mypath\mynewfile.xlsx”,51)

Finally, we close out the file and exit Excel like so:

$excel.ActiveWorkbook.close()

$excel.quit()

So, now we’ve taken what generally seems to take 30+ lines in most scripts I’ve seen, and cut it down to just 6. Granted, the two middle lines may seem to be a little daunting to remember, but they do have a nice little pattern to them. Now, at this point you’re probably wondering why there are so many little extra things…I mean, can’t we just open the file, save it, and quit it? Well, unfortunately, the answer is not so much. The difficulty here is in making sure we get true Excel formatting and end up with a true Excel file type. If we just open the file without any of the additional options, we end up without any columns just as if we had simply double-clicked on the CSV file. In theory, we don’t actually need ALL of the items, but we still have to provide them because of the way COM objects work. Unlike when you deal with a true object, COM objects don’t allow you to name your parameters as you pass them and thus each one must be provided in order to avoid confusing things which, in turn, means we have to have more parameters than we would really like or need. For instance, in our case we theoretically would only need to specify the filename, the data type, the text qualifier, and an indication the file is comma separated, but these are the first, fourth, fifth, ninth items, so we have to specify everything in between as well.

In case you’re wondering what the other items are, you can review all of the available object properties and value options here – http://msdn.microsoft.com/en-us/library/office/ff837097(v=office.15).aspx

This all said, I do feel it’s important to point out something at this point. The above example works with the default output options from PowerShell objects that used the Export-CSV cmdlet with the UTF8 encoding and the ‘-NoTypeInformation’ switch specified. If you are exporting type information, but want to skip those lines, you will need to modify the third parameter to the appropriate row number from which you wish to start to avoid confusing Excel. In addition, if you are using some delimiter other than comma separated, you will want to adjust the true and false entries accordingly based on the link provided above.

Now, on to the saving portion. You’ve probably noticed that this piece is MUCH shorter than the opening line. Again, it’d be lovely if we could just specify the filename and call it a day, but this results in an empty file (not even an empty worksheet, just a workbook with no worksheets in it) which is decidedly NOT what we are looking for. The reason is that the second parameter is what actually specifies the file format that, for whatever reason, Excel can’t figure out for itself based on the file extension, but I’ll leave that minor rant for another day. When you go and do a file-saveas manually, you obviously have lots of choices within the drop down to select from, and it’s important that we properly match up our file extension with the intended file type. In the example above, the value of ’51’ is actually a reference to ‘xlWorkbookDefault’ which will match the default save option specified in your installation of Office. In my case, I’m using Office 2013 with the default save option set to the new file format with the ‘.xlsx’ file extension, but if you were using an older version of Excel, or one that is set to save in the old ‘.xls’ format by default instead, you would want to make sure your file extension for the destination file matched. Just on the off chance you might want to use a different file format, you can see a full list of the available options and their corresponding numbers from here – http://msdn.microsoft.com/en-us/library/ff198017(v=office.15).aspx

There are, of course, lots of other parameters that you can specify during saving, such as setting passwords or access modes, but our goal here was to find the least number of characters to produce the desired result rather than to explore all the options. If you’d like to check out all the parameters because you have different needs, or if you just have a burning desire to know all the things (like I do), then you can review the full list of parameters here – http://msdn.microsoft.com/en-us/library/ff841185(v=office.15).aspx

So, now we’ve explored all the bits and pieces, but the reality is that this was mostly just an exercise because, at the end of the day, we all know you’ll probably not commit this to memory and you are more likely to go for the script angle. Of course, you could take all of the above and readily add in all the possible options and error handling yourself (and I thoroughly encourage you to do so), but because I’m a nice guy I think I’ll give you a quick and dirty version you can use to start from. To that end, and in keeping with the premise of this post, you’ll find a short scripted version without options or error handling from which you can base a more comprehensive script.

Function Convert-CSVtoExcel {

    Param(

        [string]$srcfile,

        [string]$destfile

    )

    $excel = New-Object -com excel.application

    $excel.DisplayAlerts = $false

    $file = (Resolve-Path $srcfile).path

    if(!($file)){throw “No valid source file found!!”}

    $excel.Workbooks.OpenText($file,2,1,1,1,$false,$false,$false,$true)

    $excel.ActiveWorkbook.SaveAs($destfile,51)

    $excel.ActiveWorkbook.close()

    $excel.quit()

}

Well folks, that’s about all I’ve got time for at the moment. I hope it brought you some new knowledge and, until next time, always remember that fear is the mind-killer and PowerShell is life!!