Opening and Working with Text Files Using Microsoft Excel

This article will address the proper procedure for opening text files when using Microsoft Excel®. These steps are especially important when creating and working with templates that will be used to import data into Acctvantage.

When opening and working with text files in Excel, the following formatting issues can arise: 

  1. Leading zeroes are dropped.
  2. Date formatting is lost.
  3. General Ledger Account Codes revert to Scientific Notation (ex: 2.10E+10).
  4. Quotes are added to text, unfortunately these quotes are only visible when opened with a text editor or after importing data.

To avoid these issues, open the documents using the following procedure and always review your data before importing using a text editor other than Excel.

Procedure for Opening Text Files with Excel in Order to Maintain Data Integrity.

  1. Launch Microsoft Excel®.
  2. Choose File ➤ Open. \
  • Navigate to find your text file and click Open.
  • A text import wizard will display,  click Next until you are at step 3 of 3 in the wizard.
  • In the data preview pane at the bottom of the wizard, you will be able to click on columns to select them. Hold down the shift key to select multiple columns at one time.
    • Highlight any columns that may contain leading zeroes and change the column data format to text (ex: part numbers).
    • Change the format of any date columns to have a format of date.
    • Change the format of any column with account number to text as these will sometimes revert to scientific notation (ex: 2.5E+10)
  • Click Finish.
  • Review your data to insure integrity. If you're saving a file that will be imported into Acctvantage, be sure to complete the following additional steps every time you make a change to the document in Microsoft Excel®:

    1. Save file as Tab-delimited (.txt) file.
    2. Open with alternate text editor (Text Wrangler, NotePadPro or similar application)
    3. Search for and delete quotes by using the Find or Search function. For most programs, this will involve performing the following steps:
    • Locate the Find or Search Function within the text editor.
    • In the Find or Search Field type a " symbol.
    • Leave the Replace Field blank.
    • Choose to Replace All.
  • Review and Save the file.
  • Important Notes

    Repeat these steps EVERY time you open a text file. The changes are not saved and so one must do this EVERY time you subsequently open the file to ensure that your data is intact.

    Do not just double click on the text file or drag and drop it to launch the file in excel as you will lose formatting such as leading zeroes or date formats.