Insert data from SQL Server into Excel crosstab

In this article, I will show you how to insert data into an Excel spreadsheet and safely keep your content. Indeed, if you try to insert data into Excel, which contains a crosstab, you will corrupt your spreadsheet, and remove all your crosstabs !
As I faced this, I created a script in PowerShell to insert data safely into an Excel spreadsheet, without breaking it.

In Excel, crosstabs are still often used. Thus you may need to feed data into a spreadsheet, in order to update the data used by this crosstab. Depending on your security requirement, you will not be able to create a connection to a database, which is native in Excel. You can either use an ETL tool or use a simple script, which can be more reusable, to load your data into your spreadsheet.

To insert data into a spreadsheet, you will have to use a simple command :

$sqlserver_query = "SELECT GETDATE()"
Send-SQLDataToExcel -MsSQLserver -Connection $instance -SQL $sqlserver_query -Path $destination_file -Worksheetname $sheetname -StartRow $row -StartColumn $col -NoHeader

This module is pretty useful here is the code of the Send-SqlDataToExcel !

But if you try to open your Excel file, you will encounter an error :

Basically, while you inject you data, the « Send-SQLDataToExcel » command will try to rewrite your Excel file. This module uses « Export-Excel«  as an underlying data inserter.
You will not be able to recover your crosstab and have to recreate them for each update. This is not acceptable.

Firstly, this solution only works with XLSX files !
Secondly, if you do not know this yet, XLSX files are essentially Zip files with XML files inside. In other words, your crosstab definition will be stored inside this Zip.
So the workaround is fairly simple (in theory) :

• extract and save the files defining your crosstab from the Zip
• insert your data
• zip back your crosstab definition inside the XLSX !

But this script comes with some tricky parts !

First, there’s some requirements you need to have :

• an empty template XLSX file stored somewhere
• a working directory
• 7zip installed and in your $PATH variable

Here is the script to inject data inside your Excel file :

##== Fetching parameters ==##
param(
    [string]$filename,
    [string]$sheet,
    [string]$instance,
    [string]$database,
    [string]$table,
    [string]$fields,
    [int]$row,
    [int]$col
)

Start-Transcript
try{

    ##== Creating working variables ==##
    $tmp_dir = "C:\Temp"
    $excel_template_dir = "C:\Reference_files\ExcelTemplate"
    $starting_dir = $PWD
    $unique_id = Get-Date (Get-Date).ToUniversalTime() -UFormat %s
    $filepath = [System.Text.Encoding]::UTF8.GetString([System.Text.Encoding]::UTF8.GetBytes("$excel_template_dir\$filename"))
    $sheetname = [System.Text.Encoding]::UTF8.GetString([System.Text.Encoding]::UTF8.GetBytes("$sheet"))
    
    ##== Check if template exists ==##
    if((Test-Path -Path $filepath) -eq $true){
        Write-Host "The Excel template exists. Creating a working copy ..."
        $filepath_copy = [System.Text.Encoding]::UTF8.GetString([System.Text.Encoding]::UTF8.GetBytes("$excel_template_dir\Working_dir\$filename"))
        if((Test-Path -Path $filepath_copy) -eq $true){
            Write-Host "Replacing existing working copy !"
            }
        else{
            Write-Host "Creating the working copy !"
            }
        Copy-Item $filepath -Destination $filepath_copy
        }
    else{
        Write-Host "The Excel template doesn't exist. Exiting ..."
        Stop-Transcript
        exit 1
        }
        
    ##== Extracti of pivotCharts ==##
    Write-Host "Extracting Pivot Charts ..."
    7z.exe e -aoa $filepath_copy -o"$tmp_dir\$unique_id\xl\pivotCache" pivotCacheDefinition*.xml -r
    Write-Host "Pivot Charts extracted !"
    Start-Sleep -s 3
    
    
    ##== Fetch data and insert into Excel file copy ==##
    Write-Host "Sending data to  $filepath_copy\$sheetname ..."
    $sqlserver_query = "SELECT $fields FROM [$database].[dbo].[$table]"
    Write-Host $sqlserver_query
    Send-SQLDataToExcel -MsSQLserver -Connection $instance -SQL $sqlserver_query -Path $filepath_copy -Worksheetname $sheetname -StartRow $row -StartColumn $col -NoHeader
    Write-Host "Data sent !"
    Start-Sleep -s 3
    
    ##== Replacing crosstab definitions and caches ==##
    cd "$tmp_dir\$unique_id\"
    7z.exe a -aoa "$filepath_copy" "xl\pivotCache\pivotCacheDefinition*.xml"
    
    Start-Sleep -s 3
    
    ##== Cleanup ==##
    cd $starting_dir
    rm -r "$tmp_dir\$unique_id\"
}
catch{
    echo $_.Exception.GetType().FullName, $_.Exception.Message
    $filepath_copy.Dispose()
    Stop-Transcript
    exit 1
}
Stop-Transcript

Extracting the pivotCharts (or crosstabs) :

Write-Host "Extracting Pivot Charts ..."
7z.exe e -aoa $filepath_copy -o"$tmp_dir\$unique_id\xl\pivotCache" pivotCacheDefinition*.xml -r
Write-Host "Pivot Charts extracted !"

The 7zip command if fairly easy :

1. Calling the exe « 7z » (which should be in the $PATH)
2. ‘e’ to say you want to extract
3. ‘-ao’ is a switch for overwriting and ‘a’ is for naming the following archive
4. Then you input your file
5. ‘o’ stands for output folder
6. Then you need to give your target folder
7. Then you give a list of file to fetch on a given pattern
8. ‘r’ stands for recursive, which is not mandatory here

Something important while extracting, is that you must keep the folder hierarchy of your zip, otherwise you won’t be able to zip it again.

Quick insights :

1. A unique ID is generated to allow concurrent usage of this script, until a certain point of course
2. All sheet names and filename are converted to UTF8 to avoid discrepancies between files and system
3. You can adapt the script to launch a Stored Procedure in order to fill your table
4. This script uses AD login, so you need to authorize a service account to access this data
5. In this script, the storage of templates is static, but you’re free to make it variable or change it

If you ever tried to insert some data into an Excel file to refresh a crosstab, you will have faced this issue. This script allows you to insert data without corrupting the template file. Your crosstab are safe with this script and it’s reusable across multiple usage.

However, remember that allowing users to get their hands on data in Excel might not be the safest an reliable method. This article just provides you a solution to a specific problem.