My First (Automated) Dashboard! The SQL [Part 2]
In case you missed the first blogpost, here is a link to The Overview
In case you missed the second blogpost, here is a link to The SQL [Part 1]
So far, I created a database in SSMS, a couple of tables, a trigger, and a stored procedure. Then I used all of them when importing my personal finance data from a csv file. If you missed it, make sure to check out my previous post using the link above.
In this post I’m going to focus on exporting the data out of SSMS and into an Excel file that will house the data that will be linked to my dashboard. There are three components to this process:
- The first is to export the data out of SSMS in the form of a csv file.
- The second is using PowerShell to convert that csv file to an xlsx file.
- The third is using PowerShell again, this time turning the data in the Excel file into a table that will act as the source for the dashboard.
At this point I have my table, CreditCardTransactions, populated with data that I would like to export out of SSMS. Here’s a preview of the data:
I need to get this data out of my table and into a csv file. I’ve written a post about how to do this very thing before, you can check it out here. I’ve since updated the script to include additional parameters, let’s check it out below:
USE [PersonalFinanceV2]
GO
/****** Object: StoredProcedure [dbo].[ExportCSVReport] Script Date: 12/16/2022 4:00:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ExportCSVReport] (@dbName NVARCHAR(100),
@includeHeaders BIT,
@filePath NVARCHAR(512),
@tableName NVARCHAR(100),
@reportName NVARCHAR(100),
@delimiter NVARCHAR)
AS
SET NOCOUNT ON
-- Created by Tyler T Updated on 12/12/2022
BEGIN
DECLARE @sqlCommand NVARCHAR(2000)
DECLARE @headerfileName NVARCHAR(100)
DECLARE @serverName NVARCHAR(100)
--DECLARE @delimiter NVARCHAR(4)
DECLARE @datafileName NVARCHAR(100)
DECLARE @removePrevious NVARCHAR(1000)
DECLARE @renameCommand NVARCHAR(1000)
SET @serverName = '(localdb)\TylerPF' --@@SERVERNAME
IF @delimiter IS NULL SET @delimiter = '|'
IF @includeHeaders = 1
BEGIN
SET @headerfileName = 'headers.csv'
----------------------------------------------------------
-- creates the header file
SET @sqlCommand = 'bcp "SELECT Column_Name FROM '+@dbName+'.information_schema.columns where TABLE_NAME IN ('''+@tableName+''')" queryout "' + @filePath + '\' + @headerfileName +'" -S ' + @serverName + ' -c -t "' + @delimiter + '" -T -r "' + @delimiter + '"'
Print @sqlCommand
EXEC master..xp_cmdshell @sqlCommand
DECLARE @addNewLine NVARCHAR(1000)
-- Adds a new line char to end of header file
SET @addNewLine = 'echo. >> "' + @filePath + '\' + @headerfileName + '"'
Print @addNewLine
EXEC master..xp_cmdshell @addNewLine
--------------------------------------------------
-- creates the data file
SET @datafileName = 'exported_data.csv'
SET @sqlCommand = 'bcp ' + @dbName +'..' + @tableName + ' out "' + @filePath + '\' + @datafileName + '" -S ' + @serverName + ' -c -t "' + @delimiter + '" -T'
Print @sqlCommand
EXEC master..xp_cmdshell @sqlCommand
----------------------------------------------------
-- merges the data and header file
DECLARE @appfileName NVARCHAR(100)
SET @appfileName = @tableName + '_' + CONVERT(VARCHAR(30), GETDATE(), 23) + '.csv' --112 works, but 23 has dashes in date > 01-01-2022 vs 20220101
SET @sqlCommand = 'copy /b "' + @filePath + '\' + @headerfileName + '" + "' + @filePath + '\' + @datafileName + '" "' + @filePath + '\' + @appfileName + '"'
Print @sqlCommand
EXEC master..xp_cmdshell @sqlCommand
----------------------------------------------------
-- deletes the data & header files
DECLARE @deleteCommand NVARCHAR(1000)
SET @deletecommand = 'del "' + @filePath + '\' + @datafileName + '"'
Print @deleteCommand
EXEC master..xp_cmdshell @deleteCommand
SET @deletecommand = 'del "' + @filePath + '\' + @headerfileName + '"'
EXEC master..xp_cmdshell @deleteCommand
------------------------------------------------------
-- deletes the previous version of file
SET @removePrevious = 'if exist "' + @filePath + '\' + @reportName + '" del "' + @filePath + '\' + @reportName + '"'
Print @removePrevious
EXEC master..xp_cmdshell @removePrevious
------------------------------------------------------
-- renames the files
SET @renameCommand = 'rename "' + @filePath + '\' + @appfilename + '" ' + @reportName
Print @renameCommand
EXEC master..xp_cmdshell @renameCommand
END
ELSE IF @includeHeaders = 0
BEGIN
SET @datafileName = 'exported_data.csv'
SET @sqlCommand = 'bcp ' + @dbName +'..' + @tableName + ' out "' + @filePath + '\' + @datafileName + '" -S ' + @serverName + ' -c -t "' + @delimiter + '" -T'
Print @sqlCommand
EXEC master..xp_cmdshell @sqlCommand
----------------------------------------------------
-- deletes the previous version of file
SET @removePrevious = 'if exist "' + @filePath + '\' + @reportName + '" del "' + @filePath + '\' + @reportName + '"'
Print @removePrevious
EXEC master..xp_cmdshell @removePrevious
------------------------------------------------------
-- renames the files
SET @renameCommand = 'rename "' + @filePath + '\' + @datafileName + '" ' + @reportName
Print @renameCommand
EXEC master..xp_cmdshell @renameCommand
END
END
This stored procedure takes advantage of the xp_cmdshell function and passing commands to the Windows Command Prompt through SSMS. Using BCP, the table headers are first exported into a headers csv file, then the data is exported into a separate data csv file. Then the two files are merged together with the copy command, and the file is renamed with the rename command.
The stored procedure takes a list of parameters, they are:
- @dbName > This is the name of the database housing the data
- @includeHeaders > This is a flag used to determine if headers will be included in the exported report
- @filePath > This is the export location for the report
- @tableName > This is the name of the table housing the data
- @reportName > This will be the name of the final exported report
- @delimiter > This will be the data’s delimiter in the exported report
By executing the stored procedure, I now have the data from my table in a csv file exported to a directory where I can view it with Windows file explorer. Let me show you what that looks like:
The next step is converting this csv file to an Excel file. Here’s a look at the PowerShell script I use to accomplish that:
# parameters
$csv = $args[0]
$xlsx = $args[1]
# define csv files - used for testing
#$csv = "C:\Users\tyler\Favorites\Example_Report.csv"
# define the xlsx files - used for testing
#$xlsx = "C:\Users\tyler\Favorites\Example_Report.xlsx"
#define the csv files delimiter
$delim = "|"
Function Convert($csv_file, $xlsx_file) {
# Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)
# Build the query tables > add command and reformat the data
$TextConnector = ("TEXT;" + $csv_file)
$Connector = $worksheet.QueryTables.add($TextConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
$query.TextFileOtherDelimiter = $delim
$query.TextFileParseType = 1
$query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1
$query.TextFileCommaDelimiter = 1
# Execute & delete the import query
$query.Refresh()
$query.Delete()
# Save and close the workbok as an Excel file
$workbook.SaveAs($xlsx_file,51)
$excel.quit()
}
#delete the previous xlsx files if they exist
if (Test-Path $xlsx) { del $xlsx }
# convert the csv file to xlsx
Convert $csv $xlsx
I found this script via Google on Adam Dimech’s website. With some small modifications it has worked perfectly for me. The best part? I execute it inside of SSMS via xp_cmdshell. Here’s a look at that below:
In the SQL code above, I am assembling the string that will be passed to the Windows Command Prompt. This string is a NVARCHAR variable that will act as the command_string argument to be passed to the xp_cmdshell function.
In the string I am specifying the file location of a PowerShell script to execute using the -File parameter. The PowerShell script requires two input parameters, those are defined as the fullCsvPath & fullXlsxPath. These variables are set prior in the SQL script. The fullCsvPath is the file path of the exported csv to be converted, and the fullXlsxPath will be the file path of the new Excel file.
By executing this SQL code above from within SSMS I now have a new file, with the same data, in xlsx format. Here’s a look at the new xlsx file below:
With the data now in xlsx format, the last thing I need to do is format the data inside the Excel file as a table. I’ll use another PowerShell script to accomplish that. I found the script below here on stack overflow and made some minor modifications so I could pass in additional parameters.
# path to excel file
$openPath = $args[0]
$savePath = $args[1]
$row = $args[2]
$range ="A1:J$row"
$Excel = New-Object -ComObject excel.application
$Workbook = $Excel.Workbooks.Open($openPath)
$Worksheet = $Workbook.worksheets.Item(1)
$Table = $Worksheet.ListObjects.Add(
[Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, # add a range
$Worksheet.Range($range), # set the region
$null, #"C:\Users\tyler\Favorites\Tables.csv",
[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes # yes, to headers
)
$excel.DisplayAlerts = $false # Ignore / hide alerts
$Worksheet.SaveAs($savePath,51,$null,$null,$null,$null,$null,$null,$null,'True')
$Excel.Quit()
While( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) ){}
I can also execute this PowerShell script from within SSMS using xp_cmdshell. This way the entire export and conversion process can be handled inside Sequel Server Management Studio. Here is a look at all these pieces put together so that I can export and convert the data with the execution of this one script in just a few seconds:
Additionally, by using PRINT statements I can visualize the arguments passed to the Windows Command Prompt with xp_cmdshell. Here is a look at them below:
bcp “SELECT Column_Name FROM PersonalFinanceV2.information_schema.columns where TABLE_NAME IN (‘CreditCardTransactions’)” queryout “C:\Users\tyler\A_Personal_Finance_Project\Article\headers.csv” -S (localdb)\TylerPF -c -t “,” -T -r “,”
echo. >> “C:\Users\tyler\A_Personal_Finance_Project\Article\headers.csv”
bcp PersonalFinanceV2..CreditCardTransactions out “C:\Users\tyler\A_Personal_Finance_Project\Article\exported_data.csv” -S (localdb)\TylerPF -c -t “,” -T
copy /b “C:\Users\tyler\A_Personal_Finance_Project\Article\headers.csv” + “C:\Users\tyler\A_Personal_Finance_Project\Article\exported_data.csv” “C:\Users\tyler\A_Personal_Finance_Project\Article\CreditCardTransactions_2022–12–21.csv”
del “C:\Users\tyler\A_Personal_Finance_Project\Article\exported_data.csv”
if exist “C:\Users\tyler\A_Personal_Finance_Project\Article\ArticleCCData.csv” del “C:\Users\tyler\A_Personal_Finance_Project\Article\ArticleCCData.csv”
rename “C:\Users\tyler\A_Personal_Finance_Project\Article\CreditCardTransactions_2022–12–21.csv” ArticleCCData.csv
powershell.exe -file “C:\Users\tyler\A_Personal_Finance_Project\SQL Scripts\Article 2\PS_Conv_wParams.ps1” “C:\Users\tyler\A_Personal_Finance_Project\Article\ArticleCCData.csv” “C:\Users\tyler\A_Personal_Finance_Project\Article\ArticleCCData.xlsx”
(2 rows affected)
powershell.exe -file “C:\Users\tyler\A_Personal_Finance_Project\SQL Scripts\Article 2\CreateTable.ps1” “C:\Users\tyler\A_Personal_Finance_Project\Article\ArticleCCData.xlsx” “C:\Users\tyler\A_Personal_Finance_Project\Article\Table.xlsx” “57”
(1 row affected)
And a look at my result:
With my table created I now have the dynamic data source in place for my dashboard! In my next post I’ll cover the beginning steps of creating the dashboard and linking it to this Excel table, and how the dashboard visuals and data will update when new data is exported to the table.
Thank you for reading! Any questions or feedback is greatly appreciated!