Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Imports Slack files into a "Posts" table in SQL Server.
# 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
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.