Committee Platform

@PhilMcIntosh @SteveIwanowski I was thinking of cut off dates for Socialcast conversion data. Oct. 31 is likely a lighter than usually posting date, at least for a Wednesday. I was thinking of running an export that night. Will one of you have time to pickup file links before they expire in AWS though? I think we have ~ 48 hours. Let me know, thanks.

I can do that

@PhilMcIntosh I have time Thursday evening if you need a hand.

Awesome, thank you guys.

Ran the export at 6:12 PM Pacific. Uploaded the extracted files to DropBox 90 Minds SC Confidential in a new folder EXPORT_SC_OCT_31...The folder also contains the zip of the files. Let me know if you need anything else. Thank you both so much!

I have gotten all the attachments downloaded. Having an issue uploading the to dropbox though (""something went wrong when uploading this file"" - very helpful)

I checked the space @PhilMcIntosh on DropBox, we have used 92 GB of 1 TB, plenty of space. I read a couple of posts in the DropBox forum but don't know if it's a size of upload issue or something else.

I finally got them all into a folder Attach1031. If someone wants to see if those can be moved into the Attachments folder be my guest. reinforced my choice of Google drive for my own stuff...

Thanks @PhilMcIntosh glad to hear we got it at least to a folder. I'll look and see if I can move them to the Attachments folder.

Looks like we are in the post-Thanksgiving week for conversion, unless someone ahead of us drops out or reschedules. @SteveIwanowski did you have a chance to look at a Power Shell solution for file attachments to threads, @PhilMcIntosh I may not be saying that exactly right, please correct me. Good news, they didn't find any issues with our sample files. We have all the communities set up in HL by the way, and am working on the rules for members so we'll be able to make a first pass at users soon. @ThereseLogeais the mentorship info you wanted me to send to you is available to all members, in the Helpful Links section of the Home Page in Socialcast, at the top of the list. @MichelleForsey did you test Evernote login we can start move docs ahead of the SC conversion.

@MoiraGoggin my regular evernote email is mforsey@eidebailly.com the ""note email"" from my account section is shellfishy.a85d1@m.evernote.com I believe that was the one that you needed. Do you want to try again? Maybe it's more friendly now?

Which is more convenient for you the @eidebailly or @m.evernote.com?

Tonight's my night to work on it.

eidebailly for sure!

thanks you two :)

It looks like the exports only use 5 tables (attachment, group, socialobject, user, comment), so I got about 90% of the way through a script to turn those CSVs into sql INSERT records based on the created date. I need to add the SQL CR/LF substitution lines and create sql UPDATE records for those that have changed. And then some more testing. Here's the script so far (kind of brute-forced).

Aweseom code work @SteveIwanowski thank you.

I added a new_name field to the Group table, and changed the export views to use that instead of the original name field. A new backup has been put in dropbox.

Can we modify the User.passions field to be a varchar(MAX) instead of varchar(182)?

I have done it on my copy - feel free to change yours

Thanks Phil, here's the latest version of the script with the CR/LF UPDATE statement and some more comments. I'm not sure if we want to create UPDATES for older records modified since the last import or just re-import everything fresh. The latter is simpler, but then I'll need to clean up some records, so it'll prob end up taking the same amount of time. Everything since halloween comes in clean, but in my testing, I had an issue with two records since 10/2017.

Do records really get changed months later?

Found another one, can we change User.resume to varchar(max)? I'm now able to import every column from every record of the 5 main tables since the beginning of time except for Attachment.Text for 13 records (which we don't use in the export to Higher Logic). That should be enough to get us 'live' and then I can work on updating the non-essential tables.

Change made to database

A few more to varchar(MAX): poll.question, invite.message, challenge.description, linkedaccount.preferences and project.description.

Here's the latest code (mostly adding braces to the table and column names). NonEssentials is my generic script to convert CSV to INSERT statements. It'll do all the CSVs in a folder by default and seems to import all data cleanly into SQL. So I'd like to try to re-import everything from scratch this weekend, just for fun. The only remaining thing I'd like to do would be to break the .SQL files into 10000 row chunks, but I'll try to add that later this week. Enjoy!

Schema changes made

The conversion team had the following feedback, but we are yet to have a call with them directly. 1.There is only a timestamp in the PostDate field we need a date, as well. We cannot move forward without this. _(Please add the date)_ 2.This seems to be a subset of their data as there is only one unique value (* Sage 100 Routine) in the GroupName field, and there are some ParentMessageIDs that do not have corresponding MessageIDs. Do they have a complete data set? (_I think we didn't have the group cross references in place on the sample)_ i.We need a mapping of GroupName to link to community the messages should be migrated to. ii.We will check if there are messages where the ParentMessageID is not 0 and there is no corresponding MessageID before moving forward. 3.We need all of the files referenced in filename1, filename2, filename3. you can provide these via OneDrive, Dropbox, Google Drive, FTP, etc. Please ensure the files provided have the exact same filenames as those provided in the csv. _(I'll drop it for them via share to our DropBox)_ 4.The subject seems to be a substring of the body was this intentional? _(Yup, I'll let them know)_ We have two rounds of migration. They prefer that the second round is a total data set again, to catch any changes, for example a post from the past being edited. They only run migration on business days. I have plan for this so it will work. THANK YOU!!!!

Regarding #1, the PostDate field did have the date and timestamp, so I'm guessing they were using Excel and just have to set the column to Short Date.

2 - The group name mapping has been added since we generated that data for them. I just did a check for orphan comments and did not find any

Phil, can you post an updated SQL backup as I don't think I have those mappings. Quick question about #3 above, what about the comments with >3 attachments? Do we have to have additional records, or can we just add more Filename4/5/6/etc?

Backup done and uploaded. @MoiraGoggin - can you check with Higher Logic and see if we can add attachments fields? There are a few that go as high as 37

Thanks @PhilMcIntosh and @SteveIwanowski I'll get this over to them. Dates - likely correct on the Excel issues Groups - right I did notify them we had a cross-ref. update post sending them a sample. @PhilMcIntosh I'll ask them about he limits on attachments.

@PhilMcIntosh I need a full conversion file set ASAP - they sent a document contract yesterday with a deadline for the first conversion data to be set delivered at 9:00 am Eastern today. Sorry for the short notice,

Oh boy, i hope they meant Hawaiian time...

nope the Eastern was pretty clearly written. I'm a little panicked and peeved too that Evan our contact didn't say on our call Monday, okay you ready for Friday conversion?

Community list from HL

We had some issues on the files I'll go over details with you tomorrow. In the meantime @PhilMcIntosh and @SteveIwanowski I've made a more comprehensive cross reference for the groups in Socialcast and their Conversion to Community aka GroupName in Higher Logic. It includes the exact community name in Higher Logic (requirement) and the list of groups to be deleted too. I'm putting it here so you can copy/move it to where you need to in the SQL DB.

Status - we have converted discussions on the site now along with their library entries (file attachments from threads). There are a number of issues, mostly cosmetic, but we know that some discussion threads didn't convert over and the Superman Team of @PhilMcIntosh and @SteveIwanowski worked through those issues so that the final conversion will be fabulous. All the users have been added, but the work-flow automation rules need to be finished in order for our team and others to see the various communities. We will have at least an hour to an hour and half meeting tomorrow, please make sure you attend we need everyone to help get us over the finish line. @MichelleForsey @RobertWood @PhilMcIntosh @SteveIwanowski @ThereseLogeais Thanks.

FINAL CONVERSION DATE - Our first available time slot with Higher Logic @PhilMcIntosh @SteveIwanowski **Deliver the data by 9 AM ET on Thursday, January 10 and we'll have it live by 5 PM ET Friday, January 11.** I've accepted this date - if it's a problem let me know immediately. I also need to know exactly what time I should run the export from Socialcast. .

I would think Wednesday the 9th afternoon for the export, then we import everything, create the export files, give them a sanity check and break out the margaritas.

From today's meeting: 1/8/19 6:00 pm Pacific/9:00 pm Eastern Final export from Socialcast- Moira to post to DropBox and email Phil and Steve 1/9/19 Discussion File created (library not required if file names are in discussion export) and link sent to Moira evening/afternoon of January 9, 2019 1/10/19 6:00 am Pacific or earlier, Moira send file to Higher Logic conversion team 1/11/19 2:00 pm Pacific/5:00 pm Eastern Higher Logic final conversion completed - Live on Higher Logic 1/11/19 to approx. 1/19/19 Socialcast still running but no discussion should be posted - leave a redirect message up as persistent message in each ""group"" 1/20/19 (Sunday) shut-down Socialcast

For these file extensions, we'll need to convert the file to .zip and update to the new file name with .zip to the Discussion export. I'd be happy to help convert to zip unless you two @PhilMcIntosh and @SteveIwanowski can automate it. bat, .dotm, .exe, .HEIC, .kse, .M4P, .pvx, .vbs

we can't just fake it out with an additional suffix, like .TXT?

No, they said we would have to convert the files to TXT, we couldn't 'fake' and make it. Robert suggested we zip the ones that aren't HL compatible, which looks like it is acceptable to HL conversion team.

As long as the links in the posts are converted to .zip it should work.