How to import text files into Microsoft Excel

Text, ASC or CSV files are compressed formats for data and are commonly used because they give smaller file sizes.  If you use the measurement log in Photoshop you will export a Text file.  If you are looking at palaeoclimate and archaeology for example you may wish to download climate data from something like Pangea or NOAA.   Here is how you do it.

“I don’t know this function in Excel, panic!”

“Can’t I just type the data in again?”

“It’s beyond my comfort zone!”

 

Importing data

In Microsoft Excel open a new sheet or workbook and navigate to the Data-tab (Fig. 1).

ex1

Figure 1: Blank Excel spread sheet.  Note the Data tab along the top

Click on the Data tab and on the left you will see an icon listed ‘From text’ click on that and a dialogue box will open (Fig. 2).  Use this to select the text file or any other similar data file.

ex2

Figure 2: Opening a text file or any other similar data file.

When you open the selected file a Wizard will open like that in Figure 3.  Use this to makes sure the data imports correctly.  In the case in Figure 3 I have checked ‘Delineated by’ and press OK.  The next window (Fig. 4) allows me to select what I think each row is delineated by.  In this case it is a space.  When I check this the preview shows me that each column will be imported into its own column.  I can now continue with the wizard and the data will be imported.

If the data won’t delineate nicely in the preview use the ‘Back’ key to navigate to the first window on the Wizard try using the ‘fixed width’ check box.  You can drag the column breaks into place using this method.

ex3

Figure 3: Data import wizard in Excel.

ex4

Figure 4: In the previous box (Fig. 3) I checked ‘delineated by’ and now it is asking me by what.  I have checked space and the preview shows me that each column will end up in its own column when imported.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s