Importing Text Files in Excel-2007

Sometimes you have data that is in an electronic file, but it is not in Excel format. If the data is in raw text format (also called ASCII), you can import it into Excel.

Before you start to try importing the data, you should investigate your data file a bit. First, make sure that it is in fact a raw text file. If it is a raw text file, it should open in a basic editor on your computer, such as Notepad. You can also open it in MS Word, but you will need to confirm its file type (Word can open many different types of files). Go to File > Properties... and click on the General tab. Make sure that the file type is Simple text file.

Next, you will need to see how Excel will figure out how to divide the data up into different cells (i.e., how the fields are delimited). Typically, each row of data is separated by a line return. Less standard is how the individual cells within a row (the column divisions) are separated. Ideally, there will be a standard character dividing the cells. For example, it might be a comma:

0,0.000112,43.469381,-4.974215,-1.745804e-005,695.510095      1,0.000241,50.561808,-4.974215,-2.109513e-005,808.988924      2,0.000368,55.053678,-4.974215,-8.729019e-006,880.858848      3,0.000491,57.304753,-4.974215,-5.819346e-006,916.876042      4,0.000614,61.313516,-4.974215,2.909673e-006,981.016249  

Another common division is a hidden character, such as a space or a tab:

0 0.000112 43.469381 -4.974215 -1.745804e-005 695.510095  1 0.000241 50.561808 -4.974215 -2.109513e-005 808.988924  2 0.000368 55.053678 -4.974215 -8.729019e-006 880.858848      3 0.000491 57.304753 -4.974215 -5.819346e-006 916.876042  4 0.000614 61.313516 -4.974215 2.909673e-006 981.016249

Do not be concerned if all of the numbers that belong in the column do not line up vertically. Excel will be looking for the identified character to separate the cells.

Now that you know the characteristics of your text file, you can import it into Excel. Go to Excel and choose File>Open... You get to the open dialogue box by clicking on the "Squares" icon in the upper righthand cornor of your worksheet.

Open your text file (you may need to change the File Type at the bottom of the File Open Dialogue Box from Microsoft Excel to All Files). Excel will automatically recognize it as a text file and start the Import Wizard:

Now click Next to go to the next window in the Wizard:

Click Next when you are ready to go to the next window:

Here, you can choose how to format each column of data. If it is numeric data, you leave it in the General format. Later in Excel, you can fine tune this format, adjusting things such as the number of decimals to display or whether to show it in scientific notation. The format for each column is given at the top of the preview window. Notice the default it General for all columns.

Now choose Finish:

You'll now see the data placed in cells in an Excel spreadsheet. If you are happy with the result, save the file now in an Excel workbook with File>Save as.... Remember, FIle commands are access using the funky squares icon in the upper righthand corner of the spreadysheet.

If you are not happy with the result, close the file and start the process again. Things to remember to check for: