How to Convert A Flat Database File (*.txt) Into Microsoft Excel Format

by Dr Azmi Mohd Tamil

As a medical lecturer in a local university, I sometimes have to convert a flat database file (*.txt) into Microsoft Excel. A flat file is a file that contains records, and in which each record is specified in a single line. Fields from each record may simply have a fixed width with padding, or may be delimited by white space, tabs, commas (CSV) or other characters. There are no structural relationships. The data are “flat” as in a sheet of paper, in contrast to more complex models such as a relational database.

For example; during the end of semester examination in my faculty, the students would evaluate whether the examination questions

were in concordance with the objectives,
clear and easy to understand and
enough time was given to answer the questions.

There were only 9 questions for each student to answer but with 240 students per batch, it would be very tedious to enter all these responses into a database for analysis. Instead I asked the students to enter their response onto an Optical Mark Reader (OMR) form. Optical Mark Readers have been used for decades and have been determined to be the fastest, most cost-effective and accurate means for getting data into a computer system. With the cooperation of the computer department (thank you Encik Hamdan!), I ended up with a flat database file in *.txt format, which I easily converted into an Excel file.

Thinking that it was a good idea, I shared the idea with the other module/course coordinators. Suddenly the computer department was overwhelmed with the OMR feedback forms. The department e-mailed the respective flat database file to these module/course coordinators. Instead of being thanked for their effort, the computer department received brickbats since most of the module/course coordinators did not know how to handle a flat database file. I ended up converting all the flat database files for the module/ course coordinators, and rarely do I get a word of thanks. Serves me right for coming up with the “bright idea”!

To ensure that I don’t have to do this thankless job for the rest of my tenure here (I tried to resign earlier but I was told that I have to serve my contract till 2018), I decided to come up with this article as a guide for those who want to convert such files into Excel.

Saving the text file

Upon receiving the text file, please save it at an easily accessible site such as your desktop.

Converting into Excel

Open the Excel application. Click “File” -> “Open”.

Change “Files of type” to “Text Files (*.prn;*.txt;*.csv)”. Select the text file that you saved earlier.

Step 1 – Determine Your File Type

The Text Import Wizard was supposed to help determine your file type. Unfortunately the wizard got it wrong here! Instead of “Fixed Width”, it was detected as “Delimited”. As you can see in the screenshot, there were no commas or tabs separating each field and the fields were aligned in columns with no spaces between each field. Therefore the type is “Fixed Width”. Once done, click on “Next”.

Step 2 – Setting the Column Breaks

This screen lets you set field widths or column breaks. To create a break line, just click at the desired position. To delete a break line, just double click on it. To move a break line, just click on it and drag it. Once done, click on “Next”.