Copy
News and tips for Sage 50 (formerly Peachtree), Excel, and whatever other general nerd knowledge I think will help computer users be more productive.

IQ Accounting Solutions LLC

March 2021 News & Tips

 
NEWS   SAGE 50   EXCEL
World Backup Day March 31st   Hide/Show GL Accounts in Transaction Entry Screens   Dealing With Leading Zeros In a CSV File



News

World Backup Day: March 31st is World Backup Day, a day started to remind people of the importance of backing up their data. As long as there have been computers, backups have been important. But it's more important than ever now because maintaining good backups is the most important step in protecting yourself against ransomware. It's best to have a combination of on-site (for ease of restoring) and off-site (for safety in natural disaster or theft). I recommend IBackup and IDrive because they make it easy to automate backups up both to the cloud and to local storage, and they're very affordable with business plans starting at less than $10/month and personal plans for less than $6 per month. Don't put it off any longer.

Steve Collins
IQ Accounting Solutions LLC
918-851-9713
scollins@iqaccountingsolutions.com
www.iqaccountingsolutions.com


A complete archive of these tips is available at http://www.iqaccountingsolutions.com/blog
 
 

Sage 50/Peachtree Tip - How to Hide or Show GL Accounts in Transaction Entry Screens


I frequently get calls from people who have recently reinstalled Sage 50 or installed on a new computer asking why the GL account field is missing from all of their accounts receivable screens. The fix is very simple. Just go to the Options menu and choose Global. Then clear the checkbox next to Accounts Receivable in the Hide General Ledger Accounts section of the Accounting tab. The same option is there for accounts payable and payroll.

The next question I get is usually "why would they hide the GL account?" Let's start by answering the opposite question. Some people want to show the GL account when entering transactions because they want it to be easy to change. Conversely, not everyone wants it to be easy to change the GL account. Each customer record in Sage 50 has a default sales account, each vendor has a default purchase account, and each inventory item has a sales account and a purchase/inventory account.  Companies that make use of those fields often don't want the data entry person, who may not understand accounting, to be able to override those accounts when entering invoices. For others, if they rarely need to change account numbers (as is usually the case in Payroll) they don't want one more field to move through.

One other side effect of hiding the GL accounts is that cash account fields (as in Receive Money, Payments or Payroll Entry) will only display accounts with an account type of Cash, the AR account field in Sales/Invoicing will only display accounts with the type Accounts Receivable, and the AP account field in Purchases/Receive Inventory will only show accounts with the type set to Accounts Payable.  When Hide GL Accounts is turned off, all accounts will be available in those fields.

 
 

Excel Tip - Preserving Leading Zeros In a CSV File

I originally published this tip in 2015. In the years since then it has become the second most watched video on my YouTube Channel so I thought it would be worth including again in my newsletter.

Many programs export data to CSV (Comma Separated Values) files because they are almost universally recognized. Excel can easily open a CSV file and automatically split it into columns, but there are some common problems you'll run into when doing that. One of the most common problems is leading zeros.

Zip codes (postal codes), invoice numbers, and part numbers are just a few instances in which leading zeros are common. The problem is that true numbers don't begin with a zero. So when Excel sees a string of digits with no letters or other characters, it assumes that string must be a number and, logically, eliminates any zeros from the beginning of the string. 01234 would get converted to 1234. Fortunately there's an easy way around this.

Start by renaming the CSV file to a TXT file. Simply right click on the file, choose Rename and replace the .CSV at the end of the file name with .TXT.  If you don't see the "CSV" follow these steps to turn off the option to hide file extensions.
  • Open the Start menu
  • Type Folder Options and hit Enter
  • When the Folder Options window opens select the View tab
  • Uncheck the box next to Hide extensions for known file types .
  • Click OK
Once you have the file renamed, open Excel. To open your file, go to File > Open and browse to your file's location. At the bottom of the "Open" window, change the file type from "Excel Files" to "Text Files (*.prn; *.txt; *.csv)". Select the file you just renamed and open it.

When you open the file, the Text Import Wizard will appear.
  • On step 1 leave it set to Delimited.
  • At step 2 change the delimiter to Comma
  • On step 3 locate the column that contains the leading zeros, click to select it, and set the "Column data format" to Text.
  • Click Finish
Excel will open the file but now it will leave the leading zeros in place because it thinks they are text instead of numbers. If you want to save the files as a CSV again after working with it, choose Save As and change the file type from "Text (Tab delimited) (*.txt)" to "CSV (Comma delimited) (*.csv).



 

If you know other Sage 50/Peachtree users, do them (and me) a favor and pass this along to them. If you are someone that this has been passed on to, and would like to be added to my email list, you can subscribe for free at www.iqaccountingsolutions.com/signup.html or send me an email at newsletter@iqaccountingsolutions.com. If you need to change your email address or would like to unsubscribe, you can use the links at the end of this email.

Steve Collins
IQ Accounting Solutions LLC
918-851-9713
10611 E 17th Place
Tulsa, OK 74128
 
Copyright © 2021 IQ Accounting Solutions LLC, All rights reserved.
Email Marketing Powered by Mailchimp