Exporting Data for Import into Microsoft Excel

Excel

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,

  • Create the PRN file without the title, data, header and footer information that normally occurs at the top or bottom of printed reports. You want a PRN file that has a single row of field names (or no field names) at the top and columns of data going down.
  • Most report generators have an option to insert blank lines for page breaks. Do not use page breaks. You want continuous rows of data without blank rows where the page breaks occur.
  • Format data using one of the data formats recognized by Excel. If you are unsure, do not format the data, just use decimal numbers. Use any date or date/time format that you can format a cell for in Excel.
  • If the data is not too wide you can see what it looks like by opening the PRN file in a word processor, formatting the document with Courier font, and then viewing the columns of aligned data. Look for anything that doesn’t align evenly or that you don’t want in Excel. Change the report to correct it. (Courier font is a mono-spaced font designed so that each character has the same width. This enables columns of number and text to align vertically. Most other fonts are proportional so that each character has a different width, and columns of data will not appear aligned.)

Once you have created a good PRN file here is how to import it into Excel,

  1. Import the PRN file in Excel. In Excel 2007 and Excel 2010 click the Data tab on the Ribbon and choose “From Text.” In Excel 2003 use the Data, Import External Data, Import Data command.
  2. When asked for the Data Source, change to the directory containing your PRN file and select the PRN file.
  3. In the Text Import Wizard select Fixed Width since your data is aligned in fixed width columns in the PRN file.
  4. If the data file contains header information use the Wizard to start importing at the first row of field names or data.
  5. Excel will guess at where the data columns are and show you vertical lines in the Wizard where it will separate data. If you want it separated differently, add, move or remove lines.

 

 

Share the power...