March 2021 News & Tips
|World Backup Day March 31st
||Hide/Show GL Accounts in Transaction Entry Screens
||Dealing With Leading Zeros In a CSV File
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
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.
IQ Accounting Solutions LLC
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
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 firstname.lastname@example.org
. If you need to change your email address or would like to unsubscribe, you can use the links at the end of this email.
IQ Accounting Solutions LLC
10611 E 17th Place
Tulsa, OK 74128