Greetings, tech navigators! I’m back in the tech captain’s chair, and today, I’m shining a brief light on a peculiar challenge I recently encountered during a SharePoint migration adventure using the trusty ShareGate tool. Now, before I dive into the depths of the issue, let me make it clear—this isn’t a ShareGate bashing session. In fact, it’s quite the opposite. It’s about bringing attention to a hiccup that, despite being documented, might catch you off guard as it did me!
The Date Format Conundrum
So lets set the scene and picture this: You’re tasked with migrating a load of enterprise data from a file server into SharePoint using ShareGate. You have been doing this for weeks, done many GB’s across a load of file server folders, and its all been smooth sailing until… BAM! A lovely error smacks you in the face and disrupts your otherwise perfect sailing tech odyssey with an amazing message of: “The value ‘DD/MM/YYYY’ could not be converted into a valid date because it is not in the expected format. The expected format is ‘M/d/yyyy.” Erm WTF, the client expects the data moved rapidio as it has been working so well up to that point and …. err computer says no mate! EPIC Panic mode engaged #livingthedream…
Now, naturally, the first thing any self respecting Solutions Architect/Distinguished Engineer would do….. I scoured the web and of course found an error page on ShareGates FAQ:
The time zone at the destination is not the same as the source – ShareGate
Now this error hinted at a time zone mismatch between the importing data and the target site collection, however, the data being migrated was definitely in the UK date format i.e “DD/MM/YYYY” and the target site collection was 100% in the UK Locale with the correct Region settings. So that put this out of the equation, so like the proverbial heroic like figure in any fantasy novel “onwards” I bellowed to my imaginary band of followers the quest to find the solution shall not be scuppered at the first hurdle.
Next stop… ShareGate service desk; which I must say were very responsive, even if they are based in Canada and only work on their timezone… the irony is not lost on me here!
So after a tête-à-tête with the ShareGate service desk, the real culprit emerged, importing from excel. The documentation does mention it, but it’s like that fine print you never notice until it slaps you in the face.
The Quirk with Excel Imports
So here’s the twist—it seems this date dilemma is an aficionado of Excel imports specifically. The migration engine stumbles when faced with dates in the UK format when using an Excel import file to perform the migration, even if your target SharePoint site is all about tea and crumpets. Initially, I was scratching my head, thinking it’s a timezone issue, but no, it’s a date formatting fiasco!
Solution? Speak American
So, what’s the workaround? Well, turns out, when using Excel imports in ShareGate, your dates need to put on a Yankee accent – US Locale, baby! Change the date formats on all of the data your are importing, which on 50,000 rows of documents is no mean feat, and voila, the migration engine stops throwing a tantrum.
Now, here’s the perplexing part. I’ve waltzed through many a migration without encountering this quirk. ShareGate, being the trusty companion it is, usually handles these nuances with ease. But, and it’s a big but, with Excel imports, on this particular site collection, it’s like the migration engine becomes a stickler for date accuracy.
Why Share the Saga?
Now, you might wonder, “Gary, why share this saga?” Simple. It’s about community and preventing fellow tech wayfarers from tripping over the same pebble. It’s a shout-out to those who, like me, might have missed a small footnote somewhere in the ShareGate migration manual.
So, the next time you find yourself migrating data into SharePoint using ShareGate and an Excel import, remember the golden rule: Dates speak American, no matter the SharePoint locale.
Parting Thoughts
As we navigate the tech seas, bumps in the road are par for the course. The key is to share our tales, illuminate those dark corners, and ensure our collective journey through the digital landscape becomes smoother for all. So here’s to tech adventures, problem-solving, and always being one step ahead.
Until the next byte-sized tale, stay curious and keep sharing your tech sagas!
Gary
