When you receive a .csv or .txt file, how do you open it?

.txt files are used a lot in Accounting when there are fields with commas in them and you don’t want them getting split up. For example, $100,000.00 if saved as a csv would come in as 2 fields:  $100 then 000.00. But if saved as Text Tab delimited, comes in as $100,000.00.

Let’s say you receive a customer.csv report from CAL when we are implementing your system and you need to review the file.  Or you get a Historical Transactions 2016.txt report from CAL that you need to review prior to our uploading into your new system.

Here are simple steps to open those files in Excel so that they come up clean and readable.

  1. Open Excel and look for the file. Note: you have to tell it to search for “All Files” as Excel is looking for Excel files only.
  2. Then the Wizard will start:
  3. If your header records are not on line 1.  Tell it which line to start on.Your file is a delimited file.

    Click Next

  4. In this case this is a Text – Tab delimited fileClick NEXT

  5. Now if you have any DATES (Date), or Zip Codes (TEXT) or numbers beginning with Zero (Text) and want to preserve this data go in and assign the Date or Text header to the fields.

    If I had not assigned Text to Jan 2016 it would change that field into a date

    If I had not assigned Date to 01/04/2016 to the Date it would come out as a number 40216

    This preserves your data.

  6. Click Finish and it will go into excel cleanly.

There are some great add-on tools for Dynamics GP that make working with Excel even easier. Check out the Dynamics GP With Excel category on the CAL Marketplace.

For more great Microsoft Dynamics GP Tips visit www.calszone.com/tips.

By Sue Conrod, CAL Business Solutions, www.calszone.com