How do I split results into their own cell in Excel?

Question

question

DavidA-7648 asked May 12, '22 KyleXu-MSFT commented Jun 6, '22

How do I split results into their own cell in Excel?

I'm working on a script that outputs conference room meeting information into an excel file. My goal is that the results output into their own cell instead of a single cell separated by a comma. Any help is appreciated.

$conf = @() $meeting = @() $results = @() $output = @() $conf = get-mailbox -RecipientTypeDetails roommailbox -ResultSize unlimited -Identity "conferenceroom" | select displayname, primarysmtpaddress foreach ($c in $conf){ $meeting = Get-CalendarDiagnosticObjects -Identity $c.primarysmtpaddress -ResultSize unlimited $result = ([PSCustomObject]@{ OriginalLastModifiedTime = $meeting.OriginalLastModifiedTime -split "," StartTime = $meeting.starttime EntTime = $meeting.endtime ResponsibleUserName = $meeting.ResponsibleUserName }) $results = $results + $result } $results | Export-Csv -Delimiter ';' -NoTypeInformation c:\scripts\confroommeetings.csv

windows-server-powershell office-exchange-online-itpro

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KyleXu-MSFT · May 18 at 06:21 AM

@DavidA-7648

I am writing here to confirm with you any update about this thread now.
If the suggestion below helps, please feel free to accept it as an answer to close this thread. It also could be beneficial to other community members reading this thread.

0 ·

KyleXu-MSFT · Jun 06 at 12:15 AM

@DavidA-7648
Haven't received your update about this thread for a long time, any update about it now?
If the suggestion below helps, please be free to mark it as an answer for helping more people.

0 ·

Answer 1 · 2022-05-12T19:11:56.203Z

RichMatheisen-8856 answered May 12, '22

Can you provide an example of the OriginalLastModifiedTime property?

What is the object type of that property? Since you're using the -split operator I'm going to guess it's a string (because the Get-CalendarDiagnosticObjects object is only available in the cloud-based service, and that is most likely going to return a string instead of a "live" object).

If it's a string, are there always the same number of items as a result of the -split operator, or do you sometimes get more, or fewer?

If you want the results in different cells, by what names would the cells be called?

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-05-12T19:18:47.547Z

DavidA-7648 answered May 12, '22 RichMatheisen-8856 commented May 13, '22

OriginalLastModifiedTime StartTime EntTime Respons
ibleUse
rName

{10/06/2020 23:25:49, 10/06/2020 23:25:49, 10/06/2020 23:25:49, 10/06/2020 23:25:49...} {12/12/2016 02:00:00, 12/05/2016 00:00:00, 11/29/2016 02:30:00, 11/29/2016 02:00:00...} {12/12/2016 03:00:00, 12/05/2016 06:00:00, 11/29/2016 03:00:00, 11/29/2016 02

· 5

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichMatheisen-8856 · May 12 at 07:37 PM

Is it safe to assume that this:

{10/06/2020 23:25:49, 10/06/2020 23:25:49, 10/06/2020 23:25:49, 10/06/2020 23:25:49...} {12/12/2016 02:00:00, 12/05/2016 00:00:00, 11/29/2016 02:30:00, 11/29/2016 02:00:00...} {12/12/2016 03:00:00, 12/05/2016 06:00:00, 11/29/2016 03:00:00, 11/29/2016 02

repesents the contents of "OriginalLastModifiedTime"?

Are there always four date/time strings between each "{" "}" pair? As you can see, they all end with "..." which means "there's more to see".

How many "{...}" element are there? I count three (but the string looks to be truncated after the "hour" in the third one, and there's no way to tell if there are more that follow.

Are you interested only in data from the first "{...}" pair?

The reason all this is important is that a valid CSV file should have the same number (and name) of columns in each row. If each meeting has a variable number of "{...}" pairs then ALL meetings would have to be examined to determine the number of columns each row needs (whether they.re empty or not). The same is true for the number of items found within each "{...}" pair.

0 ·

DavidA-7648 RichMatheisen-8856 · May 12 at 08:07 PM

I'm interested in all the data. For example OriginalLastModifiedTime output 10/06/2020 23:25:49, 10/06/2020 23:25:49, 10/06/2020 23:25:49, 10/06/2020 23:25:49...

The idea would be in this example, this is "meeting A". In excel we would list:

                       SubjectProperty          OriginalLastModifiedTime         StartTime                                EntTime 
                           Meeting A             10/06/2020 23:25:49              12/12/2016 02:00:00              12/12/2016 03:00:00
                           Meeting A             10/06/2020 23:25:49              12/13/2016 02:00:00              12/13/2016 03:00:00
                            Meeting B             10/06/2020 23:25:49              1/13/2016 02:00:00              1/13/2016 03:00:00

Each entry for a meeting would be listed over and over until there were no other entries for that meeting then it would move on to the next meeting.

0 ·

RichMatheisen-8856 DavidA-7648 · May 12 at 09:39 PM

That's a little clearer. You want multiple rows (not cells) for each meeting. There would be as many rows as there are elements in the 1st array (which is the "OriginalLastModifiedTime"). The second array represents the 'StartTime' with one StartTime for each OriginalLastModifiedTime. The third array represents the 'EndTime' for each OriginalLastModifiedTime.

In other words, the three arrays in your original post are actually a single two-dimensional array that would look like this:

 [array]$times = @(@("10/06/2020 23:25:49","10/06/2020 23:25:49","10/06/2020 23:25:49","10/06/2020 23:25:49"),
                   @("12/12/2016 02:00:00","12/05/2016 00:00:00","11/29/2016 02:30:00","11/29/2016 02:00:00"),
                   @("12/12/2016 03:00:00","12/05/2016 06:00:00","11/29/2016 03:00:00","11/29/2016 02:00:00")
 )

0 ·

Show more comments

Answer 3 · 2022-05-13T08:15:17.797Z

KyleXu-MSFT answered May 13, '22 KyleXu-MSFT commented May 16, '22

@DavidA-7648

Do you want to export meetings in one room mailbox into a CSV file? If so, you could use the command below directly:

 Get-CalendarDiagnosticObjects -Identity OR1@domain.onmicrosoft.com | select SubjectProperty,OriginalLastModifiedTime,starttime,endtime,ResponsibleUserName | export-csv c:/temp/1.csv -NoTypeInformation

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

qa-kyle-16-10-57.png (42.7 KiB)

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DavidA-7648 · May 13 at 01:46 PM

That is what I was looking to do but to automate it for all the meeting rooms I have.

0 ·

KyleXu-MSFT DavidA-7648 · May 16 at 12:22 AM

You could add a loop for it:

 $Mailboxes = Get-Mailbox -RecipientTypeDetails RoomMailbox -ResultSize Unlimited
 $Data = @()
    
 foreach ($mailbox in $Mailboxes){
     $data += Get-CalendarDiagnosticObjects -Identity $mailbox.PrimarySmtpAddress | select @{Expression={$mailbox.PrimarySmtpAddress};Label="RommMailbox";},SubjectProperty,OriginalLastModifiedTime,starttime,endtime,ResponsibleUserName
 }
    
 $data | export-csv c:/temp/2.csv -NoTypeInformation

0 ·

question