How can I use Microsoft Excel to tidy my ASCII Text files?
A direct import from the¬†client's accounting package into¬†CaseWare¬†is not always possible. Therefore, it may be necessary to import the trial balance using the¬†ASCII¬†text file method. It should always be possible to export your client’s trial balance into either an ASCII or Excel file.¬†
The ASCII file must be in¬†a satisfactory¬†format before¬†importing into CaseWare.¬†When you receive an ASCII file¬†from your client, the file is likely to be¬†in an undesirable presentation for CaseWare to successfully import.
The best software to use for tidying up ASCII files is¬†Microsoft Excel.
- Open¬†Microsoft¬†Excel¬†and browse for your¬†ASCII¬†file.¬†
- Remember¬†you are not browsing for an excel document. So you need¬†to change the file type to¬†Text Files (*.prm; *.txt; *.csv)
- Select the ASCII file and click¬†Open.
- Whenever you try to open an ASCII file using¬†Microsoft Excel, you will need to complete 3 steps of the¬†Text Import Wizard.
- Step 1: Select¬†Delimited¬† and click¬†Next¬†(If the¬†Preview¬†of the data below is neatly structured then select¬†Fixed).¬†
- Step 2: Select the appropriate¬†Delimiter. Once you do this the data in the¬†Data preview¬†will line up neatly. Click¬†Next.¬†
- Step 3: Always change the¬†Account No¬†column to¬†Text. This is to ensure your account numbers do not change format once they are brought into excel (e.g. decimal points, dates, etc).¬†
- The ASCII Text file has now successfully been imported into Excel.
- It is important to¬†ensure that all information concerning an individual account is contained on one line.
- Delete all extraneous data including subheadings, page numbers, subtotals, dates and blank rows (between the data).
- Save the file after the changes are made. It is important to save the changes as a different file so if you have made an error you can revert back to the original.
Note:¬†The file can then be saved in the following formats before importing into CaseWare:¬†
- Text (tab delimited) (*.txt) with a new file name.
- Excel file because CaseWare is able to import Excel files.