Have you ever received a customer report or historical transaction file and wanted to open it in Excel? The last thing you want is for your data to look like a jumbled mess when you open it in Excel. Worse yet, imagine losing important financial information like zeros in zip codes or misinterpreting dates because of automatic formatting. How to open a .CSV or .TXT from Dynamics GP in Excel.

.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 working on a year-end financial review and you receive a text file with customer transactions from 2016. Without the right import technique, a value like “$100,000.00” could be split into two separate columns, or a date like “01/04/2016” might mysteriously transform into a meaningless number. These small formatting errors can lead to significant misinterpretations, potentially causing reporting mistakes, audit issues, or costly miscalculations.

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.
      Open Excel
    2. Then the Wizard will start:
      Text Import Wizard - Step 1 of 3
    3. If your header records are not on line 1.  Tell it which line to start on. Your file is a delimited file.Click NextText Import Wizard - Step 2 of 3
    4. In this case this is a Text – Tab delimited fileClick NEXTText Import Wizard - Step 3 of 3
    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.Text Import Wizard - Step 3 of 3 part 2

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.

Open a .CSV or .TXT from Microsoft Dynamics GP in Excel

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

By CAL Business Solutions Inc., Connecticut Acumatica & Microsoft Dynamics GP / 365 BC Partner, www.calszone.com