My First (Automated) Dashboard! The SQL [Part 2]

The (almost) finished dashboard!

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!