Use PowerShell to Import Data From Microsoft Excel

Sometimes when I’m analysing data, we need to use PowerShell to import data from Microsoft Excel.

We generally have two quick options to do this.  Let’s take this sample data:

Save As a Comma Delimited CSV

We can save our data as a comma delimited CSV file – note that in Excel there are several options when saving as a CSV.  So ensure you opt for the comma delimited CSV.

Once saved, and assuming our column header is called “Data” as in the image above, we can loop through each row like so:

$sampledata = import-csv c:\alkanedata.csv

foreach ($entry in $sampledata) {
    write-host $entry.Data
}

Note that if your column name contains a space (such as “Data Column”), you would have to use quotes like so:

$sampledata = import-csv c:\data.csv

foreach ($entry in $sampledata) {
    write-host $entry."Data Column"
}

This is easy enough, but if your Excel file contains charts and other objects you’d need to copy and paste your data into another workbook and save it separately, since you cannot save charts and objects into a CSV file.

Use TEXTJOIN to Create a PowerShell Array

Another option is to use an Excel function to create your array!  Again, using our sample data above, we can paste this formula into a cell:

="$sampledata = @(""" & TEXTJOIN(""",""",TRUE,A2:A8) & """)"

And it will resolve to this:

$sampledata = @("a","b","c","d","e","f","g")

Note that the range of data that we specify includes everything in column A, from rows 2 to 8.  We can then literally copy and paste it into our PowerShell script and loop through it like so:

$sampledata = @("a","b","c","d","e","f","g")

foreach ($entry in $sampledata) {
    write-host $entry
}