You can’t always link Excel worksheets, Pivot Tables and PowerPivots directly to a database. Sometimes you have to go back to the old days of text files to get the data you need for your Excel charts and dashboards.
Most Excel users will at some point use data that is exported from another computer program. The other programs are most often from accounting, manufacturing, project management, or sales. Most programs in the last ten years will export data for Excel use in Comma Separated Value (CSV) or Excel (XLS, XLSX) file format. However, some programs export data using the older TXT or PRN format. Importing these file formats may need a few tricks.
Whenever possible export the data you want to use in Excel using a comma separated value (CSV) format. Open a CSV file as you would any Excel file and Excel automatically separates the data into cells. (This is called parsing.) When you save that worksheet containing the imported data, save it with an Excel file format.
Two text formats used by older systems are TXT and PRN. TXT format uses tabs to separate data into individual cells. PRN format aligns data in columns by inserting spaces between characters so that numbers and data appear in vertical columns.
Data saved with TXT format can be opened and parsed into cells using the Data Import command. During the Data Import process you will be asked if you want to use the Tab character as the delimiter separating pieces of data. Select Tab as the correct delimiter to open a TXT file. Save the file using an Excel file format.
PRN files take extra work to export and import. PRN files are usually saved from older programs by “printing” the data to disk using a report writer program, hence the PRN file extension. Spaces and non-printing characters are used to align data just as they would if the data were printed to paper. These can cause problems when importing.
Here are some tips on creating PRN files and opening them in Excel,
Once you have created a good PRN file here is how to import it into Excel,