csv

17 Topics

Error when importing CSV file
Hi, I am new at MS SQL and have the community edition in my local machine. Trying to import a CSV file but getting the following error message: Total Defect Qty:=SUM([Defect Qty]) TITLE: Microsoft SQL Server Management Studio ------------------------------ Error inserting data into table. (Microsoft.SqlServer.Import.Wizard) ------------------------------ ADDITIONAL INFORMATION: Error inserting data into table. (Microsoft.SqlServer.Prose.Import) ------------------------------ The given value '[name of the column]' of type String from the data source cannot be converted to type nvarchar for Column 4 [Label]. (Microsoft.Data.SqlClient) ------------------------------ String or binary data would be truncated in table '[dbo].[name of the file]', column 'Label'. Truncated value: '[name of the column '. (Microsoft.Data.SqlClient) ------------------------------ BUTTONS: OK ------------------------------ Do you think you can help me please? Thanks, Naveen
Solved
naveen73
Nov 20, 2024 Place SQL Server
350Views
0likes
5Comments
Powershell Script to list ALL videos in your 365 Stream environment
I hope this is useful to everyone. My goal was to get a list of all videos in my stream so that I could contact each video creator about the changes that are coming to stream. Also so I could figure out how much work there was to do in moving, and how much video is created and not used. My original solution (posted here 7 Oct 2020) I've retired, as thanks to Twan van Beers code here https://neroblanco.co.uk/2022/02/get-list-of-videos-from-microsoft-stream/ I've been able to build a single ps1 script that does all I need. I'll leave the the original code at the bottom of this post. Take a look a the comments I've put into the code for how to use this script. I found that my original script gave me information about the videos but it was hard to use AND didn't tell me which videos were in which channels. This new version of Twan van Beers script gives me both. Save the new code Oct 2022 as a PowerShell script e.g. get-stream-video-info.ps1 Then open a powershell screen, navigate to the folder get-stream-video-info.ps1 is in. To run it enter .\get-stream-video-info.ps1 "C:\Temp\streamanalysis\stream7Oct22-getnbstreamvideoinfo.csv" Follow the on screen prompts. >>> New Code Oct 2022 <<< using namespace System.Management.Automation.Host [CmdletBinding()] param ( [parameter(Position=0,Mandatory=$False)] [string]$OutputCsvFileName, [parameter(Position=1,Mandatory=$False)] [switch]$OpenFileWhenComplete = $False ) # ---------------------------------------- # How to use <#-- Open a powershell window then type in the following and click enter .\get-stream-video-info.ps1 "C:\Temp\streamanalysis\stream7Oct22-getnbstreamvideoinfo.csv" You'll then be prompted for 3 options [V] Videos [C] ChannelVideos [A] All [?] Help (default is "V"): V - videos, which will get a list of all the videos in your STREAM environment. NOTE you may need to alter the variables in the code if you have more than 1000s of videos C - ChannelVideos, will get a list of all the videos and the channels they are in. NOTE this returns a filtered view of all the videos associated with a channel A - All, returns both the Videos and the ChannelVideos. You'll then be prompted for a user to login to the STREAM portal with, this is so the script can get a security token to do it's work with. Choose/use an account with full access to STREAM. If you used a CSV file path after the script name, then this powershell script will export one or two CSV files based on the option chosen <your folder path, your filename>-videos<your file ending> and or <your folder path, your filename>-channelVideos<your file ending> If you don't want to export file names, this powershell creates objects you can use in other ways V or A - will create an object $ExtractData, which is a list of every video and key properties for each video. C or A - wil create an object $videosPerChannel, which lists key information about each video AND the channel they are part of. ---------------------------------------------------------------------------------------------- original source my script https://techcommunity.microsoft.com/t5/microsoft-stream-classic/powershell-script-to-list-all-videos-in-your-365-stream/m-p/1752149 which inspired Twan van Beers to write https://neroblanco.co.uk/2022/02/get-list-of-videos-from-microsoft-stream/ I've then taken Twan's script and modified it to do what I require in my environment. Namely - get the video information AND the channels they are part of. For my 1000 or so videos and 35 channels, it takes about 1 min to run using the All option. This meant I was able to setup an intranet video library with a channel metadata column, folders per channel (so i could give edit rights to channel owners, without opening up the entire library or having to use multiple libraries), and eventually download the videos, then upload them into the library using ShareGate to reinstate some of the key metadata, i.e. created date, person who created them etc --#> # ---------------------------------------------------------------------------------------------- function Show-OAuthWindowStream { param ( [string]$Url, [string]$WindowTitle ) $Source = ` @" [DllImport("wininet.dll", SetLastError = true)] public static extern bool InternetSetOption(IntPtr hInternet, int dwOption, IntPtr lpBuffer, int lpdwBufferLength); "@ $WebBrowser = Add-Type -memberDefinition $Source -passthru -name $('WebBrowser'+[guid]::newGuid().ToString('n')) $INTERNET_OPTION_END_BROWSER_SESSION = 42 # Clear the current session $WebBrowser::InternetSetOption([IntPtr]::Zero, $INTERNET_OPTION_END_BROWSER_SESSION, [IntPtr]::Zero, 0) | out-null Add-Type -AssemblyName System.Windows.Forms $Form = New-Object -TypeName System.Windows.Forms.Form -Property @{Width = 600; Height = 800 } $Script:web = New-Object -TypeName System.Windows.Forms.WebBrowser -Property @{Width = 580; Height = 780; Url = ($URL -f ($Scope -join "%20")) } $Web.ScriptErrorsSuppressed = $True $Form.Controls.Add($Web) $Featured = { $Head = $Web.Document.GetElementsByTagName("head")[0]; $ScriptEl = $Web.Document.CreateElement("script"); $Element = $ScriptEl.DomElement; # Javascript function to get the sessionInfo including the Token $Element.text = ` @' function CaptureToken() { if( typeof sessionInfo === undefined ) { return ''; } else { outputString = '{'; outputString += '"AccessToken":"' + sessionInfo.AccessToken + '",'; outputString += '"TenantId":"' + sessionInfo.UserClaim.TenantId + '",'; outputString += '"ApiGatewayUri":"' + sessionInfo.ApiGatewayUri + '",'; outputString += '"ApiGatewayVersion":"' + sessionInfo.ApiGatewayVersion + '"'; outputString += '}'; return outputString; } } '@; $Head.AppendChild($ScriptEl); $TenantInfoString = $Web.Document.InvokeScript("CaptureToken"); if( [string]::IsNullOrEmpty( $TenantInfoString ) -eq $False ) { $TenantInfo = ConvertFrom-Json $TenantInfoString if ($TenantInfo.AccessToken.length -ne 0 ) { $Script:tenantInfo = $TenantInfo; $Form.Controls[0].Dispose() $Form.Close() $Form.Dispose() } } } $Web.add_DocumentCompleted($Featured) $Form.AutoScaleMode = 'Dpi' $Form.ShowIcon = $False $Form.Text = $WindowTitle $Form.AutoSizeMode = 'GrowAndShrink' $Form.StartPosition = 'CenterScreen' $Form.Add_Shown( { $Form.Activate() }) $Form.ShowDialog() | Out-Null write-output $Script:tenantInfo } # ---------------------------------------------------------------------------------------------- function Get-RequestedAssets([PSCustomObject]$Token, [string]$Url, [string]$Label) { $Index = 0 $MainUrl = $Url $AllItems = @() do { $RestUrl = $MainUrl.Replace("`$skip=0", "`$skip=$Index") Write-Host " Fetching ... $($Index) to $($Index+100)" $Items = @((Invoke-RestMethod -Uri $RestUrl -Headers $Token.headers -Method Get).value) $AllItems += $Items $Index += 100 } until ($Items.Count -lt 100) Write-Host " Fetched $($AllItems.count) items" $Assets = $AllItems | Select-Object ` @{Name='Type';Expression={$Label}},` Id, Name,` @{Name='Size(MB)';Expression={$_.AssetSize/1MB}}, ` PrivacyMode, State, VideoMigrationStatus, Published, PublishedDate, ContentType, Created, Modified, ` @{name='Media.Duration';Expression={$_.Media.Duration}},` @{name='Media.Height';Expression={$_.Media.Height}},` @{name='Media.Width';Expression={$_.Media.Width}},` @{name='Media.isAudioOnly';Expression={$_.media.isAudioOnly}},` @{name='Metrics.Comments';Expression={$_.Metrics.Comments}},` @{name='Metrics.Likes';Expression={$_.Metrics.Likes}},` @{name='Metrics.Views';Expression={$_.Metrics.Views}}, ` @{name='ViewVideoUrl';Expression={("https://web.microsoftstream.com/video/" + $_.Id)}}, ` @{name='VideoCreatorName';Expression={$_.creator.name}}, ` @{name='VideoCreatorEmail';Expression={$_.creator.mail}}, ` @{name='VideoDescription';Expression={$_.description}} write-output $Assets } function Get-VideoChannels([PSCustomObject]$Token, [string]$Url, [string]$Label) { #this will get the list of channels $Index = 0 $MainUrl = $Url $AllItems = @() do { $RestUrl = $MainUrl.Replace("`$skip=0", "`$skip=$Index") Write-Host " Fetching ... $($Index) to $($Index+100)" $Items = @((Invoke-RestMethod -Uri $RestUrl -Headers $Token.headers -Method Get).value) $AllItems += $Items $Index += 100 } until ($Items.Count -lt 100) Write-Host " Fetched $($AllItems.count) items" #to add properties to this section look at https://aase-1.api.microsoftstream.com/api/channels?$skip=0&$top=100&adminmode=true&api-version=1.4-private $Channels = $AllItems | Select-Object ` @{Name='Type';Expression={$Label}},` Id, Name, Description,` @{Name='MetricsVideos';Expression={$_.metrics.videos}} #write-host $channels.count write-output $Channels } function Get-channelVideos([PSCustomObject]$Token, [PSCustomObject]$allChannels, [string]$Label) { #this will get the list of channels $MainUrl = "https://aase-1.api.microsoftstream.com/api/channels/ChannelIDToSwap/videos?`$top=50&`$skip=0&`$filter=published%20and%20(state%20eq%20%27completed%27%20or%20contentSource%20eq%20%27livestream%27)&`$expand=creator,events&adminmode=true&`$orderby=name%20asc&api-version=1.4-private" #for each channel URL go through all the videos, capture the channel name against the video id and name $allVideosPerChannel = @() foreach($chan in $allChannels) { $thisChannelid = $chan.id $chanUrl = @( $MainUrl.Replace("ChannelIDToSwap", $thisChannelid) ) $chanName = $chan.name $AllItems = "" $items = "" $thischanvideos = "" $Index = 0 #write-host $chanUrl #loop the index do { $RestUrl = $chanUrl.Replace("`$skip=0", "`$skip=$Index") #write-host $restUrl #Write-Host "$chanName | Fetching ... $($Index) to $($Index+50)" $Items = @((Invoke-RestMethod -Uri $RestUrl -Headers $Token.headers -Method Get).value) $allItems = $items | select id,name, @{Name='Channel';Expression={$chanName}},@{Name='Type';Expression={$Label}} #write-host $allItems.count #foreach($x in $items ) { # write-host $x.name #write-host $x.id #write-host $label #write-host $chanName #} $Index += 50 } until ($Items.Count -lt 100) #got videos into $items, now mist with $chan info and put into $allVideosPerChannel object $allVideosPerChannel += $AllItems $AllItems = "" $items = "" } Write-Host " Fetched $($allVideosPerChannel.count) videos in $($allChannels.count) channels" #to add properties to this section look at https://aase-1.api.microsoftstream.com/api/channels?$skip=0&$top=100&adminmode=true&api-version=1.4-private write-output $allVideosPerChannel } # ---------------------------------------------------------------------------------------------- function Get-StreamToken() { $TenantInfo = Show-OAuthWindowStream -url "https://web.microsoftstream.com/?noSignUpCheck=1" -WindowTitle "Please login to Microsoft Stream ..." $Token = $TenantInfo.AccessToken $Headers = @{ "Authorization" = ("Bearer " + $Token) "accept-encoding" = "gzip, deflate, br" } $UrlTenant = $TenantInfo.ApiGatewayUri $ApiVersion = $TenantInfo.ApiGatewayVersion $UrlBase = "$UrlTenant{0}?`$skip=0&`$top=100&adminmode=true&api-version=$ApiVersion" $RequestToken = [PSCustomObject]::new() $RequestToken | Add-Member -Name "token" -MemberType NoteProperty -Value $Token $RequestToken | Add-Member -Name "headers" -MemberType NoteProperty -Value $Headers $RequestToken | Add-Member -Name "tenantInfo" -MemberType NoteProperty -Value $TenantInfo $Urls = [PSCustomObject]::new() $RequestToken | Add-Member -Name "urls" -MemberType NoteProperty -Value $Urls $RequestToken.urls | Add-Member -Name "Videos" -MemberType NoteProperty -Value ($UrlBase -f "videos") $RequestToken.urls | Add-Member -Name "Channels" -MemberType NoteProperty -Value ($UrlBase -f "channels") $RequestToken.urls | Add-Member -Name "Groups" -MemberType NoteProperty -Value ($UrlBase -f "groups") $UrlBase = $UrlBase.replace("`$skip=0&", "") $RequestToken.urls | Add-Member -Name "Principals" -MemberType NoteProperty -Value ($UrlBase -f "principals") write-output $RequestToken } function New-Menu { [CmdletBinding()] param( [Parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string]$Title, [Parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string]$Question ) $videos = [ChoiceDescription]::new('&Videos', 'Videos') $channelvideos = [ChoiceDescription]::new('&ChannelVideos', 'All Videos by Channel') $all = [ChoiceDescription]::new('&All', 'All videos AND all videos by channel') $options = [ChoiceDescription[]]($videos, $channelvideos, $all) $result = $host.ui.PromptForChoice($Title, $Question, $options, 0) switch ($result) { 0 { 'Videos' } 1 { 'ChannelVideos' } 2 { 'All' } } } $menuoutome = New-Menu -title 'Stream videos' -question 'What do you want to output?' #write-host $menuoutome $StreamToken = Get-StreamToken $urlQueryToUse = $StreamToken.Urls.Videos #default $StreamToken.Urls.Videos is something like https://aase-1.api.microsoftstream.com/api/videos?$skip=900&$top=100&adminmode=true&api-version=1.4-private #To get creator and event details you need to add $expand=creator,events to the URL , not to do that you need to use &`$expand=creator,events with out the ` powershell thinks $expand is a variable. # e.g. use $urlQueryToUse = $StreamToken.Urls.Videos+"&`$expand=creator,events" #Other option # use the following if you want to only see files that have privacymode eq 'organization' i.e. video is visible to EVERYONE in the organisation # Thanks to Ryechz for this # # $urlQueryToUse = $StreamToken.Urls.Videos + "&orderby=publishedDate%20desc&`$expand=creator,events&`$filter=published%20and%20(state%20eq%20%27Completed%27%20or%20contentSource%20eq%20%27livestream%27)%20and%20privacymode%20eq%20%27organization%27%20" if($menuoutome -eq 'Videos' -Or $menuoutome -eq 'All'){ #modify the -URL submitted to get more data or filter data or order the output $ExtractData = Get-RequestedAssets -token $StreamToken -Url $urlQueryToUse -Label "Videos" write-host "" write-host "The `$ExtractData object contains all the details about each video. Use `$ExtractData[0] to see the first item in the object, and it's properties." if( $OutputCsvFileName ) { $thisOutputCsvFileName = $OutputCsvFileName.replace(".csv", '-'+$menuoutome+'-videos.csv') $ExtractData | Export-CSV $thisOutputCsvFileName -NoTypeInformation -Encoding UTF8 write-host "The following file has been created: $thisOutputCsvFileName" if( $OpenFileWhenComplete ) { Invoke-Item $thisOutputCsvFileName } } } if($menuoutome -eq 'ChannelVideos' -Or $menuoutome -eq 'All'){ #Get the list of channels , filter the result for the channel id and name $channelList = Get-VideoChannels -token $StreamToken -Url $StreamToken.Urls.Channels -Label "Channels" #for each channel get the videos that are in that channel, so that we can match them up to the ExtractData , which is the list of all videos) $videosPerChannel = get-channelvideos -token $StreamToken -allChannels $channelList -Label "ChannelVideos" write-host "" write-host "The `$videosPerChannel object contains key information about each video and the channel it is in. Use `$videosPerChannel[0] to see the first video, and it's properties." write-host "The `$channelList object contains a list of channel's and their properties." if( $OutputCsvFileName ) { $thisOutputCsvFileName = $OutputCsvFileName.replace(".csv", '-'+$menuoutome+'-channelVideos.csv') $videosPerChannel | Export-CSV $thisOutputCsvFileName -NoTypeInformation -Encoding UTF8 write-host "The following file has been created: $thisOutputCsvFileName" if( $OpenFileWhenComplete ) { Invoke-Item $thisOutputCsvFileName } } } >>> Original Code Oct 2020 <<< I've left this here in case it is useful to anyone. See the script above for a better solution. That said this solution relies on you knowing a bit about PowerShell, being a Stream Admin and being happy to manually save some files (I couldn't figure out how to do pass through windows authentication for the script) so you have to manually save each paged JSON file of 100 videos. It took me about 20minutes to export information about 591 videos (about 3 hours to make the script). To use the script update each variable marked with #<<<< Update this value in a normal (not admin) powershell window run the script (i copy and paste logical parts into the powershell window) you will be given several urls, to visit and save the JSON files from once you have the JSON files the final part of the script reads those, and exports a CSV file with a row per video in STREAM NOTE : as an admin you see all videos, so before you share the CSV with others be aware that there may be sensitive information in it that most users can't see due to STREAM's in built security. I don't have much time, hence I made this script so please don't expect quick answers to any questions. This script is rough, use it if it helps, but ... be professional and check it before you use it. ##>> Update 5 Aug 2021 <<## Thanks to everyone who has commented, I've updated the code below with your suggestions You still have to manually save the JSON browser tabs that show up as JSON files into a folder, but other than that I hope it is now easier for you to use 🙂 #reference https://techcommunity.microsoft.com/t5/microsoft-stream-forum/powershell-script-to-audit-and-export-channel-content-details-of/m-p/354832 # goal of this script #- get list of all videos in stream for analysis #- it takes about 20 minutes to do this for 500 stream videos. #First # find out what your api source is # go to the following URL in chrome as an admin of Stream https://web.microsoftstream.com/browse # using Developer tools look at the "console" search for .api.microsoftstream to find out what is between https:// and .api.microsoftstream in my case https://aase-1.api.microsoftstream.com/api/ [string]$rootAPIlocation = "aase-1" #<<<< Update this value to the one you find in the console view #[string]$rootAPIlocation = "uswe-1" # use this for Western US #[string]$rootAPIlocation = "euno-1" # use this for the Europe North region #enter where you on your computer you want the files to go [string]$PowerShellScriptFolder = "C:\Temp\streamanalysis" #<<<< Update this value #json files will be saved into "VideosJSON" folder [string]$streamJSONfolder = Join-Path -Path $PowerShellScriptFolder -ChildPath "VideosJSON" #<<<< Update this value if you want a different folder name #>>> REMOVES all exiisting JSON files <<<< #remove all JSON items in this folder Remove-Item -path $streamJSONfolder\* -include *.json -Force -Recurse #guess approx number of videos you think you have divide by 100 e.g. 9 = 900 videos [int]$Loopnumber = 9 #<<<< Update this value #put in your stream portal url [string]$StreamPortal = "https://web.microsoftstream.com/?NoSignUpCheck=1" #put in the url where you see all videos from in stream [string]$StreamPortalVideoRoot = "https://web.microsoftstream.com/browse/" #$StreamPortalChannelRootForFindingVideos [string]$StreamPortalVideoViewRoot= "https://web.microsoftstream.com/video/" # for watching a video #this builds from the info you've put in a URL which will give back the JSON info about all your videos. [string]$StreamAPIVideos100 = "https://$rootAPIlocation.api.microsoftstream.com/api/videos?NoSignUpCheck=1&`$top=100&`$orderby=publishedDate%20desc&`$expand=creator,events&`$filter=published%20and%20(state%20eq%20%27Completed%27%20or%20contentSource%20eq%20%27livestream%27)&adminmode=true&api-version=1.4-private&`$skip=0" #$StreamAPIVideos100 # use the following if you want to only see files that have privacymode eq 'organization' i.e. video is visible to EVERYONE in the organisation #Thanks to Ryechz for this # # [string]$StreamAPIVideos100 = "https://$rootAPIlocation.api.microsoftstream.com/api/videos?NoSignUpCheck=1&`$top=100&`$orderby=publishedDate%20desc&`$expand=creator,events&`$filter=published%20and%20(state%20eq%20%27Completed%27%20or%20contentSource%20eq%20%27livestream%27)%20and%20privacymode%20eq%20%27organization%27%20&adminmode=true&api-version=1.4-private&`$skip=0" [int]$skipCounter [int]$skipCounterNext = $skipCounter+100 [string]$fileName = "jsonfor-$skipCounter-to-$skipCounterNext.json" #next section creates the URLS you need to manually download the json from , it was too hard to figure out how to do this programatically with authentication. Write-Host " Starting Chrome Enter your credentials to load O365 Stream portal" -ForegroundColor Magenta #Thanks Conrad Murray for this tip Start-Process -FilePath 'chrome.exe' -ArgumentList $StreamPortal Read-Host -Prompt "Press Enter to continue ...." Write-host " -----------------------------------------" -ForegroundColor Green Write-host " --Copy and past each url into chrome-----" -ForegroundColor Green Write-host " --save JSON output into $streamJSONfolder" -ForegroundColor Green for($i=0;$i -lt $Loopnumber; $i++) { $skipCounter = $i*100 if($skipCounter -eq 0) { write-host $StreamAPIVideos100 Start-Process -FilePath 'chrome.exe' -ArgumentList $StreamAPIVideos100 } else { write-host $StreamAPIVideos100.replace("skip=0","skip=$skipCounter") #following code opens browser tabs for each of the jsonfiles #Thanks Conrad Murray for this tip Start-Process -FilePath 'chrome.exe' -ArgumentList $StreamAPIVideos100.replace("skip=0","skip=$skipCounter") } } Write-host " --save each browser window showing JSON output into $streamJSONfolder" -ForegroundColor Green Write-host " -----------------------------------------------------------------------------------" -ForegroundColor Green Write-host " -----------------------------------------" -ForegroundColor Green Read-Host -Prompt "Press Enter to continue ...." Write-host " -----------------------------------------" -ForegroundColor Green $JSONFiles = Get-ChildItem -Path $streamJSONfolder -Recurse -Include *.json [int]$videoscounter = 0 $VideosjsonAggregateddata=@() $data=@() foreach($fileItem in $JSONFiles) { Write-host " -----------------------------------------" -ForegroundColor Green Write-Host " =====>>>> getting content of JSON File:", $fileItem, "- Path:", $fileItem.FullName -ForegroundColor Yellow $Videosjsondata = Get-Content -Raw -Path $fileItem.FullName | ConvertFrom-Json $VideosjsonAggregateddata += $Videosjsondata Write-host " -----------------------------------------" -ForegroundColor Green #Write-Host " =====>>>> Channel JSON Raw data:", $Videosjsondata -ForegroundColor green #Read-Host -Prompt "Press Enter to continue ...." } write-host "You have " $VideosjsonAggregateddata.value.count " videos in Stream , using these selection criteria" foreach($myVideo in $VideosjsonAggregateddata.value) { $videoscounter += 1 $datum = New-Object -TypeName PSObject Write-host " -----------------------------------------" -ForegroundColor Green Write-Host " =====>>>> Video (N°", $videoscounter ,") ID:", $myVideo.id -ForegroundColor green Write-Host " =====>>>> Video Name:", $myVideo.name," created:", $myVideo.created,"- modified:", $myVideo.modified -ForegroundColor green Write-Host " =====>>>> Video Metrics views:", $myVideo.metrics.views, "- comments:", $myVideo.metrics.comments -ForegroundColor Magenta Write-Host " =====>>>> Video Creator Name: ", $myVideo.creator.name , " - Email:", $myVideo.creator.mail -ForegroundColor Magenta Write-Host " =====>>>> Video Description: ", $myVideo.description -ForegroundColor Magenta $datum | Add-Member -MemberType NoteProperty -Name VideoID -Value $myVideo.id $datum | Add-Member -MemberType NoteProperty -Name VideoName -Value $myVideo.name $datum | Add-Member -MemberType NoteProperty -Name VideoURL -Value $($StreamPortalVideoViewRoot + $myVideo.id) $datum | Add-Member -MemberType NoteProperty -Name VideoCreatorName -Value $myVideo.creator.name $datum | Add-Member -MemberType NoteProperty -Name VideoCreatorEmail -Value $myVideo.creator.mail $datum | Add-Member -MemberType NoteProperty -Name VideoCreationDate -Value $myVideo.created $datum | Add-Member -MemberType NoteProperty -Name VideoModificationDate -Value $myVideo.modified $datum | Add-Member -MemberType NoteProperty -Name VideoLikes -Value $myVideo.metrics.likes $datum | Add-Member -MemberType NoteProperty -Name VideoViews -Value $myVideo.metrics.views $datum | Add-Member -MemberType NoteProperty -Name VideoComments -Value $myVideo.metrics.comments #the userData value is for the user running the JSON query i.e. did that user view this video. It isn't for information about all users who may have seen this video. There seems to be no information about that other than, total views = metrics.views #$datum | Add-Member -MemberType NoteProperty -Name VideoComments -Value $myVideo.userData.isViewed $datum | Add-Member -MemberType NoteProperty -Name Videodescription -Value $myVideo.description #thanks Johnathan Ogden for these values $datum | Add-Member -MemberType NoteProperty -Name VideoDuration -Value $myVideo.media.duration $datum | Add-Member -MemberType NoteProperty -Name VideoHeight -Value $myVideo.media.height $datum | Add-Member -MemberType NoteProperty -Name VideoWidth -Value $myVideo.media.width $datum | Add-Member -MemberType NoteProperty -Name VideoIsAudioOnly -Value $myVideo.media.isAudioOnly $datum | Add-Member -MemberType NoteProperty -Name VideoContentType -Value $myVideo.contentType $data += $datum } $datestring = (get-date).ToString("yyyyMMdd-hhmm") $csvfileName = ($PowerShellScriptFolder + "\O365StreamVideoDetails_" + $datestring + ".csv") #<<<< Update this value if you want a different file name Write-host " -----------------------------------------" -ForegroundColor Green Write-Host (" >>> writing to file {0}" -f $csvfileName) -ForegroundColor Green $data | Export-csv $csvfileName -NoTypeInformation Write-host " ------------------ DONE -----------------------" -ForegroundColor Green Disclaimer : You can use that solution as you want and modify it depending of your case. Many thanks to Fromelard and his https://techcommunity.microsoft.com/t5/microsoft-stream-forum/powershell-script-to-audit-and-export-channel-content-details-of/m-p/354832 which gave me enough to figure out how to do this.
Solved
Dorje-McKinnon
Dec 04, 2023 Place Microsoft Stream (Classic) and migration
67KViews
18likes
73Comments
Decimal separator after CSV import
Hi, I need some help, please. I'm trying to import a csv file. The problem is that numbers are written with comma decimal separator, but my PC is working with dots as decimal separator. In the previous Excel versions, there was the possibility to convert them during the import wizard. Unfortunately, I'm not able to find the same option in the new import wizard (Power Query). Thanks for all your answers Luca
Luca Rivoira
Sep 08, 2023 Place Excel
164KViews
0likes
9Comments
PowerShell script to audit and export Channel content details of your Office 365 Stream
Following the previous script developed to audit Office 365 Video Portal: https://techcommunity.microsoft.com/t5/Office-365-Video/PowerShell-script-to-audit-and-export-all-content-details-of/m-p/352594#M830 I worked to retrieve something equivalent with Office 365 Stream. There is not yet public API available into Office 365 Stream, MS communicated that API delivery Q2 or Q3 2019. But O365 Stream portal (https://web.microsoftstream.com) is using internal API used into Stream internal pages like the following examples: List of O365 Groups: Page: https://web.microsoftstream.com/browse?view=group API: https://euno-1.api.microsoftstream.com/api/groups?xxx List of videos for one O365 Group: Page: https://web.microsoftstream.com/group/{groupid}?view=videos API: https://euno-1.api.microsoftstream.com/api/groups/{groupid}/videos?xxx List of Channels Page: https://web.microsoftstream.com/browse?view=channel API: https://euno-1.api.microsoftstream.com/api/channels?xxx Those API, giving results in JSON format, can be used only if you connect O365 Stream first to start your web session, and because that solution is only to wait official Public API, I decided to stay in that Web Browser usage mode. The process to respect step by step (steps are placed into the PowerShell script): Open O365 Stream Portal into your Web Browser (with your admin credentials) - https://web.microsoftstream.com/?NoSignUpCheck=1 With the same Web browser (other tab or other windows), open the following links: FROM 0 to 100: https://euno-1.api.microsoftstream.com/api/channels?$top=100&$orderby=metrics%2Fvideos%20desc&$expand=creator,group&api-version=1.3-private&$skip=0 FROM 100 to 200: https://euno-1.api.microsoftstream.com/api/channels?$top=100&$orderby=metrics%2Fvideos%20desc&$expand=creator,group&api-version=1.3-private&$skip=100 ... : Stop when you have no value into the page Save each of that JSON Results into the work folder (C:\PSScript\ChannelsJSON\) channels100.json channels200.json … PowerShell script will generate an Aggregated CSV file containing O365 Stream Channel details Before execute the script, you have to set the parameters with the correct value (depending on total channels you have into your O365 Stream and where you place the PowerShell script): [int]$Loopnumber = 2 # << 2 indicates between 100 and 200 channels [string]$PowerShellScriptFolder = "C:\PSScript" [string]$PowerShellScriptFolder = "C:\PSScript" [string]$streamJSONfolder = Join-Path -Path $PowerShellScriptFolder -ChildPath "ChannelsJSON" Remove-Item -path $streamJSONfolder\* -include *.json -Force -Recurse [string]$StreamPortal = "https://web.microsoftstream.com/?NoSignUpCheck=1" [string]$StreamPortalChannelRoot = "https://web.microsoftstream.com/channel/" [string]$StreamAPIChannels100 = "https://euno-1.api.microsoftstream.com/api/channels?NoSignUpCheck=1&`$top=100&`$orderby=metrics%2Fvideos%20desc&`$expand=creator,group&api-version=1.3-private&`$skip=" [int]$Loopnumber = 2 Write-host " -----------------------------------------" -ForegroundColor Green Write-Host " =====>>>> PortalURL:", $StreamPortal Start-Process -FilePath 'iexplore.exe' -ArgumentList $StreamPortal Write-Host " Enter your credentials to load O365 Stream portal" -ForegroundColor Magenta Read-Host -Prompt "Press Enter to continue ...." for($i=0;$i -lt $Loopnumber; $i++) { Write-host " -----------------------------------------" -ForegroundColor Green $StreamAPIChannels100 = $StreamAPIChannels100 + $($i*100) Write-Host " =====>>>> First 100 channels (from", $($i*100), "to", $(($i+1)*100), "):", $StreamAPIChannels100 Start-Process -FilePath 'iexplore.exe' -ArgumentList $StreamAPIChannels100 Write-Host " Save the 100 channels (from", $($i*100), "to", $(($i+1)*100), ") into the folder $streamJSONfolder respecting the name channels100.json" -ForegroundColor Magenta Read-Host -Prompt "Press Enter to continue ...." } Write-host " -----------------------------------------" -ForegroundColor Green $ChannelJSONFiles = Get-ChildItem -Path $streamJSONfolder -Recurse -Include *.json [int]$channelscounter = 0 $ChanneljsonAggregateddata=@() $data=@() foreach($channelsjson in $ChannelJSONFiles) { Write-host " -----------------------------------------" -ForegroundColor Green Write-Host " =====>>>> JSON File:", $channelsjson, "- Path:", $channelsjson.FullName -ForegroundColor Yellow $Channeljsondata = Get-Content -Raw -Path $channelsjson.FullName | ConvertFrom-Json $ChanneljsonAggregateddata += $Channeljsondata Write-host " -----------------------------------------" -ForegroundColor Green #Write-Host " =====>>>> Channel JSON Raw data:", $Channeljsondata -ForegroundColor green #Read-Host -Prompt "Press Enter to continue ...." } foreach($myChannel in $ChanneljsonAggregateddata.value) { if($myChannel.metrics.videos -gt -1) { $channelscounter += 1 $datum = New-Object -TypeName PSObject Write-host " -----------------------------------------" -ForegroundColor Green Write-Host " =====>>>> Channel (N°", $channelscounter ,") ID:", $myChannel.id, "- isDefault Channel:", $myChannel.isDefault -ForegroundColor green Write-Host " ---- Channel Name:", $myChannel.name,"- Channel Portal URL:", $($StreamPortalChannelRoot + $myChannel.id) Write-Host " ---- Channel CreationDate:", $myChannel.created,"- Channel ModificationDate:", $myChannel.modified Write-Host " =====>>>> Channel Metrics Followers:", $myChannel.metrics.follows, "- Video Total:", $myChannel.metrics.videos -ForegroundColor Magenta Write-Host " =====>>>> O365 Channel Creator Name: ", $myChannel.creator.name , " - Email:", $myChannel.creator.mail -ForegroundColor Magenta Write-Host " O365 GROUP Name:", $myChannel.group.name, "- ID:", $myChannel.group.id -ForegroundColor Yellow Write-Host " =====>>>> O365 Group ID: ", $myChannel.group.id , " - Group Email:", $myChannel.group.aadGroup.mail -ForegroundColor Magenta Write-Host " =====>>>> O365 Group Metrics Channel total:", $myChannel.group.metrics.channels, "- Video Total:", $myChannel.group.metrics.videos -ForegroundColor Magenta $datum | Add-Member -MemberType NoteProperty -Name ChannelID -Value $myChannel.id $datum | Add-Member -MemberType NoteProperty -Name ChannelName -Value $myChannel.name $datum | Add-Member -MemberType NoteProperty -Name ChannelURL -Value $($StreamPortalChannelRoot + $myChannel.id) $datum | Add-Member -MemberType NoteProperty -Name ChannelDefault -Value $myChannel.isDefault $datum | Add-Member -MemberType NoteProperty -Name ChannelFollowers -Value $myChannel.metrics.follows $datum | Add-Member -MemberType NoteProperty -Name ChannelVideos -Value $myChannel.metrics.videos $datum | Add-Member -MemberType NoteProperty -Name ChannelCreatorName -Value $myChannel.creator.name $datum | Add-Member -MemberType NoteProperty -Name ChannelCreatorEmail -Value $myChannel.creator.mail $datum | Add-Member -MemberType NoteProperty -Name ChannelCreationDate -Value $myChannel.created $datum | Add-Member -MemberType NoteProperty -Name ChannelModificationDate -Value $myChannel.modified $datum | Add-Member -MemberType NoteProperty -Name O365GroupId -Value $myChannel.group.id $datum | Add-Member -MemberType NoteProperty -Name O365GroupName -Value $myChannel.group.name $datum | Add-Member -MemberType NoteProperty -Name O365GroupEmail -Value $myChannel.group.aadGroup.mail $datum | Add-Member -MemberType NoteProperty -Name O365GroupTotalChannels -Value $myChannel.group.metrics.channels $datum | Add-Member -MemberType NoteProperty -Name O365GroupTotalVideos -Value $myChannel.group.metrics.videos $data += $datum } } $datestring = (get-date).ToString("yyyyMMdd-hhmm") $fileName = ($PowerShellScriptFolder + "\O365StreamDetails_" + $datestring + ".csv") Write-host " -----------------------------------------" -ForegroundColor Green Write-Host (" >>> writing to file {0}" -f $fileName) -ForegroundColor Green $data | Export-csv $fileName -NoTypeInformation Write-host " -----------------------------------------" -ForegroundColor Green You can use that solution as you want and modify it depending of your case and request. Fabrice Romelard French version: http://blogs.developpeur.org/fabrice69/archive/2019/02/21/office-365-script-powershell-pour-auditer-le-contenu-de-son-office-365-stream-portal.aspx Update March 22 2019: Stream root URL used for Internal API can be different based on tenant region. I collected some URLs as much as I can (if you have anyone new, please to add it in comment to update that list) Europe: https://euno-1.api.microsoftstream.com Asia: https://aase-1.api.microsoftstream.com
Fromelard
Sep 11, 2022 Place Microsoft Stream (Classic) and migration
28KViews
0likes
22Comments
Powershell bulk creation - error with UserPrincipalName
Hi I use a simple script (and it works) to bulk create new users on my M365 tenant (cloud based only). When I set the UserPrincipalName to e.g. [jan . jensen @ DOMAIN . onmicrosoft . com] it works (disregard the spaces, if I didn't use spaces in the example, the email gets removed by the editor). When I set the UserPrincipalName to e.g. [mailto:Email address removeddk] it doesn't work. I'm missing something here, because, I assumed that the UserPrincipalName also could be the new email address. What do I need to in order to also create a valid email address as well? Below is the online script, and the CSV file it references. Import-Csv -Path "C:\Users\Jens Jakobsen\users.csv" | ForEach {New-MsolUser -DisplayName $_.DisplayName -FirstName $_.FirstName -LastName $_.LastName -UserPrincipalName $_.UserPrincipalName -UsageLocation $_.UsageLocation -LicenseAssignment $_.AccountSkuId} | Export-Csv -Path "C:\Users\Jens Jakobsen\Results.csv" Thanks.
Solved
jensjakobsen1966
Jan 29, 2022 Place Windows PowerShell
3.5KViews
0likes
7Comments
Excel Language Options
Hello everyone, Hopefully someone might have an answer for my situation. Any feedback or suggestions are welcome. I work as a designer for a company that produces document books within a variety of languages, including english, french, greek, Russian, Arabic and Hebrew. Currently, I use a variety of programs and files to create translation documents for each other the languages. What I am trying to do is create one Excel document with all of my languages and translations and export that as a CSV to import into Indesign with Data Merge. My current problem is that I can't seem to change between writing styles for different languages, such as from left-to-right typing to right-to-left typing. Any advice or potentially an easier way to do what I am trying to do is appreciated. Thank you.
Mike Watts
Jun 09, 2021 Place Excel
1.4KViews
0likes
2Comments
New Password Import feature natively available on Edge Canary Version 90.0.817.0
Microsoft Edge Version 90.0.817.0 (Official build) canary (64-bit) you need to enable this new flag first: edge://flags/#PasswordImport There is also another way to do this which was explained here Happy importing!
HotCakeX
Mar 05, 2021 Place Discussions
1.8KViews
2likes
0Comments
Flow Exporting .CSV File from Azure Blob to SharePoint site (Power BI Data Export)
Hello, First of all sorry if this question was already asked. I was searching about this on Tech community and Google but nothing helped me. We worked on a simple Flow that export .CSV data File from Azure Blob to SharePoint site. With 3 of 4 exports it works just fine ! The last one is showing an error (screenshot below) We tried to remake it from scratch but nothing changed. Have you any idea on what the problem can be ? Thanks a lot in advance, Regards
juliendadoun
Dec 17, 2020 Place Power Apps and Power Automate in SharePoint
3.6KViews
0likes
1Comment
Export Import CSV - help please :)
Hi Everyone I am currently creating a script that consists of a lot of small tasks, ending with the result being an Excel Report for the management team. But one part is giving me some trouble, so new i seek your help. I have a number of CSV filers with the following rows: "computer";"lastseen";"doccount" I am only interested in the "computer" row, which contains computer names on client machines. I need to end up with a CSV file with only that row + the column data. I have been trying to do the following: Get-content -Path Pathtosource\sourcefile.csv | Select-Object -Property computer | Export-Csv -path Pathtodestination\destinationfile.csv -NoTypeInformation To get it done in one command. But all i get is a CSV file with the text "computer" and nothing else. All the column data is missing. I have been trying this also: $ElasticBDContent = Get-content -Path PathtoSource\Sourcefile.csv $ElasticBDContent = $ElasticBDContent | Select-Object -Property computer $ElasticBDContent | Export-Csv -path PathtoDestination\Destinationfile.csv -NoTypeInformation Giving me the exact same result, a CSV file with the following content: "computer" This is the content i am looking for: "computer" "computer1" "computer2" "computer3" Can anyone give an example on how to achieve this? Any help is much appreciated. Thanks in advance.
Solved
Kennethjn
Nov 04, 2020 Place Windows PowerShell
1.5KViews
0likes
3Comments
Creating Private Channels from a CSV file
Hi all, I have existing teams that I'd like to create plus thirty private channels for. I have these private channel names in a CSV file with headers such as: cname ctype Group 001 Private I tried using this code: Import-csv -path "C:\xxxx" | foreach{New-TeamChannel -GroupId GROUPID -DisplayName $_.cname -MembershipType $_.ctype} However, the error that keeps being return is: New-TeamChannel : A parameter cannot be found that matches parameter name 'MembershipType'. At line:1 char:159 + ... -4f95-af21-868941f09af1 -DisplayName $_.cname -MembershipType $_.ctyp ... + ~~~~~~~~~~~~~~~ + CategoryInfo : InvalidArgument: (:) [New-TeamChannel], ParameterBindingException + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.TeamsCmdlets.PowerShell.Custom.NewTeamChannel Please can someone assist me here.
Solved
adamsonc
Oct 15, 2020 Place Microsoft Teams
3.2KViews
0likes
3Comments