Posts Tagged ‘Excel’

Combine CSV Files to XLSX File with Worksheets

Saturday, February 4th, 2017

Three years ago, I posted CombineXLSheets.vbs, a vbScript which allows you to drop and drag Excel spreadsheets onto it and have them combined into a single workbook file with multiple worksheets.  Now that I am working in PowerShell, I have found the need to consolidate CSV files.  Parts of  Combine-CSVFilesToWorksheets.ps1 come from code ported from the vbscript, and this script does a similar job.

I have been experimenting with input validation choices, and you can see some of it here.  One of the things I learned is that a single parameter can have more than one script validations using ValidateScript.  I also decide that the error handling for this is pretty ugly, and moved other input validation into the body of the script.

The script requires that you specify the folder with the CSV files, and the output file for the combined data.  By default the worksheets are named with the base name of the CSV file, and autofit is applied to the columns.  You may optionally turn off autofit, and can delete all of the CSV files when done or the CSV folder itself.  Because this is an advanced function, you can use Get-Help for full help.  The script also supports ShouldProcess (-WhatIf ).

This script requires Excel.

Script Text

Export to XLSX without Excel: Export-XLSX.ps1 Revisited and Tweaked

Monday, January 30th, 2017

I often look at the code of others with respect and admiration. Peter Kriegel (Germany) wrote Export-XLSX.ps1, an amazing script which lets you export data into a real XLSX file without Excel being installed. The script also enables you to directly append worksheets to XLXS files. His website, http://www.admin-source.de, hasn’t been updated in a while, and the automatic translate function is broken. It isn’t clear to me how to reach Peter, as I typically don’t post the code of others with so few changes, so I want to be clear — this isn’t my work.

I converted the original script to an advanced function, and made sure the Help was functioning right.  The only limitation is with formatting, which means it will look pretty much like a file from Export-CSV.  Nonetheless, if you have scripts running on servers where you really don’t want to install Excel, this is an excellent function to use.  The script is heavily commented and has examples in the help.

Script Text

Minimize File Size Bloat with Excel Pivot Tables

Sunday, November 2nd, 2014

I often work with very large spreadsheet, often more than 30 MB in size. I am an Office user, not an Office expert.  A few months ago one of my teammates taught me how to use pivot tables to summarize and present the large data set in a way better than just using filters.  The standard way of creating a pivot table is quite easy, but I was surprised to find that it nearly doubled the size of my files — when it worked  Often I got the error, “Excel cannot complete this task with available resources”.  I found a lot of not very helpful information, until I saw a post from someone saying he put the pivot table in a second spreadsheet, and used Microsoft Query to connect them.  This got me to thinking — why not try it within the spreadsheet itself?  And it works!  Here are the steps:

The technique below requires that you have Microsoft Query installed.  It is an optional component which you may have to add — it is under Office Tools.  Note that this also works with Pivot Charts.  The steps to create the Pivot Table are:

  • Add a or select a new sheet
  • Select Data/Get External Data/From Other Sources
  • Choose “From Microsoft Query”
  • From the database tab, select Excel, then press OK
  • Select the file you are working on.
  • Go to options, and check System Tables
  • Select page with data. Expand to select what want. You can select all
  • Click Next through filter and sort
  • Click Finish “Return Data to Microsoft Excel”
  • At Import Data, choose Pivot Table (or Pivot Chart)  Report.

You will see the data being queried as the pivot table is generated.  Unlike a standard Pivot Table, you will not have a second copy of the data as part of the document.  The new size is only slightly larger than the original document.

Get Holidays for Any Year and Any Country as PowerShell Object

Saturday, March 8th, 2014

This script started out as a project to figure out how to automate Excel web queries in PowerShell, so MS Excel is a prerequisite for this script.  If you have Excel, but have never used query tables, it is accessible by going to the Data tab, and then choosing “From Web”.  An overview from TechRepublic is here.  Web Queries have been around since Excel 2000, but I think it is a pretty obscure feature.

I was hoping to use the functionality for an internal project which had failed to work satisfactorily with any of the standard ways of pulling down web pages.  When I began to automate Excel in PowerShell, I found that things which I had mastered in VbScript were actually a bit harder in PowerShell.  This script shows the techniques of how to force Excel to close, and how to use SaveAs a CSV file.

Using a Web Query did not work for my internal project either, and this languished in my half-written pile for some time.  Earlier in the week, I had to stay late at the office while waiting for an appointment and decided to take the time to make something practical of what I had worked on.

Holidays can be a difficult to work with when calculating workdays.  I use TimeAndDate.com, which not only pulled off a most excellent domain name, but also is a very good resource for time and date information.  I urge you to visit this page often, especially if you plan to use this script which takes holiday data from them. They have put the data in a very friendly format for web queries; go to the website at http://www.timeanddate.com/holidays/ to see the countries available and their URLs.  Visit the URL for your country to see what sort of data is available from the website.

Get-Holidays.ps1 begins by getting a list of already open Excel process IDs and saves them.  It then opens Excel, does the web query and saves the results as a randomly named CSV file.  I try to close Excel gracefully, then delete the Excel PID which was not open when the script started.  The Import-CSV is used to import the data. (Yes, I know I could have collected it from the spreadsheet directly).  The temporary file is deleted.  The data is manipulated so that the text dates become date objects, and some of the fields are renamed.

The demo date stuff at the bottom shows how to select for a particular type of holiday and how to format the date object to show the date without the timestamp.

I have this written for the US http://www.timeanddate.com/holidays/us/, but you can see that the URL for the  UK it is www.timeanddate.com/holidays/uk/,  and for Canada is www.timeanddate.com/holidays/Canada/.  As written you have to change the the URL in the code to change the country.  I have added example code which gets the US National Holidays for 2015.

Script Text

Combine Multiple Excel Spreadsheets

Wednesday, March 5th, 2014

I create a lot of Excel audit reports in a multi-domain environment.  When they go out, I need to combine the reports from each domain into a single Excel workbook with multiple worksheets.  I found some VBA code on mrexcel.com which was pretty easy to port to vbscript.  I put the script on the desktop and then select all of the files which I want combined, and drop them onto the script.  A new workbook opens with each of the original files as a worksheet page.  The original files are not deleted.  Note that if you line the files up in the order you want them before selecting them, you will get them in the order you want.  I hope you find CombineXLSheets.vbs makes this task a little easier. Script Text