Think these articles are too basic?  Want something with more “meat”?  Suggest a topic and test our knowledge!

Article Two: Microsoft Access, a corrupted data’s best friend?

Ever had a data set that you tried to import into MS SQL (or other highly rule governed database), but couldn’t?  Was getting a resupply not an option?  So, what to do…

MS SQL will often advise during the import, what row the corruption occurs at, with this information in hand, carry on.  If you’re this lucky, and can then open the file in something like notepad, then there is a chance that you’ll be able to see the issue, and resolve it there and then.  If not, then your options become limited.  Often notepad will not be able to show you the issues, or it can’t handle the size of the file.  A great support app, is Notepad++.  At the time of writing this article, they were up to 5.7, but check here to download the latest version.

So, notepad not doing it for you?  You’re not alone…

My saviour… MS Access.  It’s an application that cares not so much about the rules, but goes with the flow.  Access will be kind enough to output a column and a row number to tell you where things are going wrong.  But Access, just like Jesus instructed, goes the extra mile and imports your data anyway.  From here, it’s a simple 1,2,3 to export the data out of Access in a format that the highly rule driven MS SQL can accept.  I always use a PIPE “|” delimited file (Pipe is usually the key above the enter key, just be sure to hold down SHIFT), with “TEXT” qualifications.

Import into SQL should now be a breeze.  What about the data that was corrupted I hear you say?  There is every chance that you’ll be able to solve that in Access, but equally the same chance that it’s gone.  If the data is critical and worth your time, then try to restore it, else divert your energies to something you can positively impact.  In my experience it’s often lost for good, and not worth your time trying to recover.