At ConstrucTech we are often faced with the challenge of migrating data from a customer's legacy system into Vista or Sage 300. This is typically done via some sort of data extract, a bit of transformation, and then uploading into the destination system.
Often times we run into issues during the transformation step where we need to inspect the data first and Excel is a great tool for looking at columnar data. However, Excel is often "too nice" and automatically does some unwanted data conversions such as stripping leading zero's and automagically converting text that looks like numbers into actual numbers. This certainly does cause problems with things like social security numbers or bank account numbers that have leading zero's.
Microsoft has finally given us the choice on how that behaviour should work for us although it is a little hidden.
***Caution*** You must be on a newer version of Excel to access this little gem.
From Excel, head over to the
File menu. Select the
Options menu. Once in the
Excel Options menu, select the
Data option. Notice the
Automatic Data Conversion section. Voila! Not only is there an option to not remove leading zeros and convert to a number, but there is also the ability to be notified of any data conversions that have happened. You can even re-enable some of the legacy data import wizards that no longer show up in the Data Toolbar Ribbon.
Hopefully this little tip can help you with exporting data into Excel.
While exporting data from Vista/Sage into Excel is handy, it can be tedious. If you often have to export/transform data and would like a custom automated solution, reach out! We'd love to talk to you about the undiscovered capabilities of your ERP system.
