Imports Slack files into a "Posts" table in SQL Server.

ImportSlack.ps1

# lets just bucket all the comments from all users and then print it out
# for each file, read the file sequentially and add it to a hash table count

$database = "SQLSlack"
$server = "localhost"
$parent_folder = "C:\SlackExport"
Import-Module SqlServer


function Invoke-Sql {
  param ($string)
  if ($null -ne $string.ToString() -and $string.ToString().Length -gt 5) # I have no memory why I did this
  {
    $string.Insert(0,$prefix) | out-null
    $s = $string.ToString()
    $s = $s.Substring(0,$s.Length-1)
    Write-Debug $s
    Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $s | Out-Null
  }
}

$ErrorActionPreference = "Stop"

foreach($folder in (Get-ChildItem $parent_folder))
{
  foreach($jsonFile in (Get-ChildItem $folder.FullName))
  {
    $string = @"
    DECLARE @JSON NVARCHAR(max), @Channel NVARCHAR(255), @Date DATE
    SELECT @Channel = '$($Folder.Name)', @JSON = BulkColumn, @Date = '$($($jsonFile.name).replace(".json", "").replace("-", ""))'
    FROM OPENROWSET(BULK '$($jsonFile.FullName)', SINGLE_CLOB) AS oro
    
    INSERT dbo.Posts ( Channel, Username, Type, Subtype, TS, MessageText, MessageDate )
    SELECT @Channel, [user], type, subtype, ts, text, DATEADD(MILLISECOND, CAST(TS AS NUMERIC(19,0)) % (1000*60*60*24), @Date)
    FROM OPENJSON(@JSON)
    WITH ([user] nvarchar(255), type nvarchar(255), subtype nvarchar(255), ts nvarchar(255), text nvarchar(max)) AS oj
"@
    Invoke-Sql -string $string
  }
    
  }