Excel 2010: Exporting or Importing Data to Text Files

You may need to save Excel data in text files in order to be able to use the data in other programs or applications. There are several text file formats that are supported by Excel.  The two commonly used file formats are: delimited text files (.txt) in which tabs separate each field of text, or comma delimited files (.csv) in which commas separate each field. You can easily save Excel data into one of these formats or import these text formats into Excel. Remember, however, that formatting, graphics, and other objects will be lost when saving data in a text file.

Saving as a Comma Delimited File

We will illustrate how to save Excel columns as a comma delimited file. The original data might look something like this:

0000011111         Bill
0000022222         Susie
0000033333         John
0000044444         Mary

NOTE: To display preceding zeros in a column, you can go to the Format Cells dialog box and choose Custom. On the right-hand side, scroll up (if necessary) and click the single 0. In the Type field, enter as many zeros as required digits (in this example, the number of zeros would equal 10).

To specify a file type, click Save as. (Only the active sheet can be saved in the .csv  format). In the Save as type field, choose .CSV Comma delimited (*.csv), and click Save. You may get a warning stating that there may be features incompatible with the comma delimited format, but you can save both versions of the file. (There is also an option to save in .csv format for the Macintosh).

Columns of data are separated by commas, and each row of data ends in a carriage return. If a cell contains a comma, the cell contents are enclosed in double quotation marks. Now when you open the file in an application such as Notepad, it will be a delimited text file with each field separated by a comma:


Transposing the Data in Rows

Another option might be to reorganize the data and place a column (or multiple columns) into rows, separated by commas. Excel can transpose the data to display horizontally rather than vertically. Just copy the data, place the cursor where you want the data pasted, and on the Home tab of the Ribbon, click the drop-down arrow under the Paste icon. Then select Paste Special. In the Paste Special dialog box, click Transpose. The two column(s) now will be displayed in two rows:

0000011111         0000022222         0000033333         0000044444
Bill                          Susie                     John                      Mary

After the file is saved in the .CSV  Comma delimited format, it will look like this:


Importing Text Files

You can open text files directly in Excel; however, if you want to modify some options, then use the Text Import Wizard. To access this Wizard when importing a text file, on the Excel Ribbon, go to the “From Text” command located in the Get External Data group and open the file. Follow instructions as you go through the steps in the Wizard to identify which row at which to begin the import and specify which delimiter is used. There is an option in the Wizard to change the columns of data to text format (instead of general format), which will preserve the preceding zeros.  You can also change data types for certain types of data or you can skip the import for certain columns. You can also preview the data to make sure that it will be imported correctly.

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *