# 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.

## The issue

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 :

### The script :

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

### Code insights :

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

## Conclusion

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.