Learning Excel - Lesson 11: Importing and exporting text files

There are two ways to import data from a text file (.txt or .csv) into Excel. You can open the file in Excel or import it as an external data range. To export data from Excel to text file, use the Save As command and change the file type from the drop-down menu.

Microsoft Excel Microsoft Excel for Android Microsoft Excel for iOS Microsoft Excel Online

Two commonly used text file formats include:

Simple text file (.txt), in which TAB characters (ASCII code characters 009) usually separate each text field.

A text file (.csv) is a file in which text values ​​are usually separated by commas.

You can change the delimiters used in .txt or .csv files. This is necessary to ensure the import or export operation works as you wish.

Note: You can import or export up to 1,048,576 rows and 16,384 columns.

Learning Excel - Lesson 11: Importing and exporting text files

Enter the text file by opening it in Excel

Import & export text files in Office versions 2017 and above

Enter the text file by connecting it (Power Query)

Export data to text file by saving it

Import & export text files in Office 2016-2010 version

Enter the text file by connecting it

Export data to text file by saving it

Tips to know when importing or exporting text files in Excel

Change the separator used when importing text files

Change list separator for new text file (file you can create when exporting using Save As)

Enter the text file by opening it in Excel

Enter the text file into Excel

You can open text files created in other programs as Excel workbooks with the Open command . Opening the text file in Excel does not change the file format. You can see it in the Excel title bar, where the file name contains the text file name extension. (e.g. .txt. .csv).

Go to File> Open and navigate to the location where that text file is located.

Select Text Files in the type of file drop-down list in the Open box .

Locate and double click the text file you want to open.

If it is a .txt file, Excel starts the Import Text Wizard process. When you have completed these steps, click Finish to complete the data entry process.

If it's a .csv file, Excel automatically opens the text file and displays the data in a new workbook.

Note : When opening a .csv file, it uses the current default data format setting to explain how to import each column of data. If you want to convert the column more flexibly to other data formats, you can use the Import Text Wizard. For example, the column data format in the .csv file is MDY, but Excel's default data format is YMD or you want to convert a column of numbers with the first zero leading to text so you can keep the same. this form. To force Excel to run the Import Text Wizard, you can change the file name extension from .csv to .txt before opening it. Alternatively, you can enter the text file by connecting it.

Import & export text files in Office versions 2017 and above

Enter the text file by connecting it (Power Query)

You can import data from a text file into the current worksheet.

Open the Data tab , in the Get & Transform Data group> click From Text / CSV .

In the Import Data box , locate and double-click the text file you want to import> Click Import .

In the preview dialog, you have a few options:

Select Load , if you want to load the data directly into the new worksheet.

Alternatively, select Load to if you want to load data into a table, a PivotTable / PivotChart , an existing / new Excel worksheet, or just make a connection. You also have the option of adding data to the Data Model .

Select Transform Data if you want to upload this data to Power Query and edit it before putting it into Excel.

If Excel doesn't convert a column of data to the desired format, you can convert that data after importing it.

Export data to text file by saving it

You can convert Excel worksheet to text file by using the Save As command .

Go to File> Save As .

Click Browse .

Go to Save As> Save as type , choose the text file format for the worksheet. For example, click Text (Tab delimited) or CSV (Comma delimited). Note: The feature set is supported depending on the format.

Browse to the location where you want to save the new text file, then click Save .

A dialog box appears, reminding you that only the current worksheet is saved to the new file. If you are sure the current worksheet is the only thing you want to save as a text file, click OK . You can save other spreadsheets as separate text files by repeating the above process.

You may also see a message below the ribbon that some features may be lost if you save the workbook in CSV format.

Import & export text files in Office 2016-2010 version

Enter the text file by connecting it

You can import data from the text file into the current worksheet.

Click the cell you want to put data for that text file.

On the Data tab , in the Get External Data group group> click From Text .

In the Import Data box , locate and double-click the text file you want to import, click Import . Follow the instructions in the Text Import Wizard . Click Help on any page of the Text Import Wizard to learn more information about using this wizard. After completing the steps, click Finish to finish the import process.

In the Import Data box , follow these instructions:

In Where do you want to put the data? , do the following:

To return the data to the position you selected, click Existing worksheet .

Return the data to the upper left corner of the worksheet, click New worksheet .

Optionally, click Properties to set the refreshing, formatting and layout options for the imported data.

Click OK .

Excel places the external data range in the position you select.

If Excel does not convert the data column to the format you want, you can convert the data after importing it.

Export data to text file by saving it

You can convert Excel worksheet to text file by using the Save As command .

Go to File> Save As .

The Save As box is displayed.

In the Save as type box , select the text file format for that worksheet. For example, click Text (Tab delimited) or CSV (Comma delimited).

Go to the location where you want to save the new text file, then click Save .

A box appears, reminding you to save the current worksheet only to a new file. If you are sure about this action, click OK .

A second dialog box appears, reminding you that the worksheet may contain features that are not supported by the text file format. If you just want to save the data in the worksheet as a new text file, click Yes . If you are unsure and want to know more about unsupported Excel features, click Help to learn more.

Tips to know when importing or exporting text files in Excel

Change the separator used when importing text files

The way you change the separator when importing depends on how you import the file.

If using Get & Transform Data> From Text / CSV , after selecting the text file and clicking Import , select the characters used from the list in Delimiter . You can see the effect of the new option immediately in the data preview window. This way you can be sure of your decision before continuing.

If using the Text Import Wizard to import text files, you can change the separator used for import operation in step 2 of the Text Import Wizard. In this step, you can also change the way you handle consecutive delimiters.

Change list separator for new text file (file you can create when exporting using Save As)

When saving the workbook as a .csv file, the default list separator is a comma. You can change it via Windows Region settings.

Windows 10

In Microsoft Windows 10 , right-click the Start button > click Settings .

Click Time & Language> Region in the left panel.

In the main control panel, go to Regional settings> click Additional date, time, and regional settings .

In Region , click Change date, time, or number formats .

In Region , on the Format tab , click Additional settings .

In the Customize Format dialog box , on the Numbers tab , type the character that uses the delimiter in the List separator box .

Click OK twice.

Note : After changing the delimiter character for a computer, the entire program uses this character as a delimiter. You can change this character back to the default by repeating the above process.

In older Windows

In Microsoft Windows, click the Start button > click Control Panel .

In Clock, Language, and Region , click Change date, time, or number formats .

In Region , on the Format tab , click Additional settings .

In the Customize Format dialog box , on the Numbers tab , type the character that serves as the new delimiter in the List separator box .

Click OK twice.

Hopefully after this Excel lesson, you will be proficient in importing and exporting text files in Microsoft Excel.

Sign up and earn $1000 a day ⋙

Leave a Comment

How to fix the error of printing blank pages in Microsoft Excel

How to fix the error of printing blank pages in Microsoft Excel

How to fix the error of printing blank pages in Microsoft Excel. Why do some Excel documents not display data or contain blank pages when printed? Below are the reasons

How to pin Word, Excel and PowerPoint files to the corresponding app icon on the Windows 11 taskbar

How to pin Word, Excel and PowerPoint files to the corresponding app icon on the Windows 11 taskbar

How to pin Word, Excel and PowerPoint files to the corresponding app icon on the Windows 11 taskbar, How to pin Office files to the taskbar icon on Windows 11? Invite

How to fix the error of not being able to save Excel files on Windows

How to fix the error of not being able to save Excel files on Windows

How to fix the error of not being able to save an Excel file on Windows. If you can't save the Excel file, don't worry too much. Below are ways to fix the error of not being able to save the above Excel file

How to insert a picture into a cell using VBA in Excel

How to insert a picture into a cell using VBA in Excel

How to insert a picture into a cell using VBA in Excel, You can easily insert pictures into Excel spreadsheets and edit them with macro code. Here's how to insert images

How to enable auto-capitalization of the first letter of a sentence in Excel

How to enable auto-capitalization of the first letter of a sentence in Excel

How to enable auto-capitalization of the first letter of a sentence in Excel, Turning on the automatic-capitalization of the first letter of a sentence in Excel will save you a lot

How to calculate seniority work on Excel spreadsheets

How to calculate seniority work on Excel spreadsheets

Through this tutorial, WebTech360 will guide you how to calculate the seniority of a list of employees in the company via Microsoft Excel spreadsheet or WPS Spreadsheets.

IF and IFS functions in Excel: Usage and specific examples

IF and IFS functions in Excel: Usage and specific examples

The IF function in Microsoft Excel is one of the most commonly used logical functions. In the simplest sense, the IF function will return a result based on one or several input comparison data.

Learn Excel - Lesson 6: How to filter unique values ​​and delete duplicate values ​​in Excel

Learn Excel - Lesson 6: How to filter unique values ​​and delete duplicate values ​​in Excel

Filtering unique values ​​and deleting duplicate data is a basic operation that everyone needs to know when using Excel. Join us to learn how to do it in Excel!

How to use VLOOKUP, INDEX, ... in Excel you should know

How to use VLOOKUP, INDEX, ... in Excel you should know

The functions VLOOKUP, INDEX, MATCH, ... are quite complex functions but are effective in data mining in Excel. In this article, I will guide you in detail how to use VLOOKUP and related functions effectively.

Instructions for deleting horizontal lines in Word

Instructions for deleting horizontal lines in Word

Word, an indispensable tool for office workers when editing documents and editing documents to create professionally presented and arranged documents, Word often comes with Excel and PowerPoint in the Microsoft Office suite, caters to many types

What is the difference between Excel and OpenOffice?

What is the difference between Excel and OpenOffice?

OpenOffice is an office suite built on open source. Like Excel, this toolkit can be used on computer systems using Windows or Linux operating systems.

How to insert a PDF file into Excel

How to insert a PDF file into Excel

Adding PDF files to Excel will make working with this spreadsheet tool much more efficient and professional.

Instructions for sending emails from Excel spreadsheets using VBA scripts

Instructions for sending emails from Excel spreadsheets using VBA scripts

Using VBA scripts in Excel does not require any programming knowledge. VBA helps you do many advanced tasks such as creating a spreadsheet report containing all PC information, emailing directly from a spreadsheet ...

Create charts that automatically update data in Excel 2016

Create charts that automatically update data in Excel 2016

You can create automatically updated charts according to newly added data in Excel with just three simple steps.

Zooper Widget for Android 2.36 - Latest Version Updates for 2024 and 2025

Zooper Widget for Android 2.36 - Latest Version Updates for 2024 and 2025

Discover the latest updates in Zooper Widget for Android. Zooper Widget is a compact, highly customizable, and battery-friendly widget with endless possibilities.

Lipp for iOS 4.4 - Make lip sync and voiceover videos on iPhone / iPad 2024, 2025

Lipp for iOS 4.4 - Make lip sync and voiceover videos on iPhone / iPad 2024, 2025

Lipp for iOS, alongside Dubsmash and Musical.ly, provides iPhone and iPad users with a new platform to create lip sync videos and dub videos readily.

How to Share Audio with TeamViewer - Latest Version Updates 2024, 2025

How to Share Audio with TeamViewer - Latest Version Updates 2024, 2025

Learn how to share audio using TeamViewer, including the latest updates for 2024 and 2025, making remote communication more effective.

Adorable Home: Latest Tips for Petting Your Cat in 2024 and 2025

Adorable Home: Latest Tips for Petting Your Cat in 2024 and 2025

Learn the latest tips on how to pet a cat in Adorable Home for optimal gameplay in 2024 and 2025. Discover what positions give you hearts.

OrangeHRM Open Source 3.2.1 - Latest Features for 2024 and Beyond

OrangeHRM Open Source 3.2.1 - Latest Features for 2024 and Beyond

Explore the latest features of OrangeHRM Open Source, a free HR management system tailored to meet evolving business needs in 2024 and 2025.

How to Fix IDM Error Not Catching Download Links - Updates for 2024 and 2025

How to Fix IDM Error Not Catching Download Links - Updates for 2024 and 2025

Fix IDM not catching file download links. Discover solutions for downloading videos, music, and more with the latest IDM updates in 2024 and 2025.

The File Converter for Android - Convert files to other formats - Updated Versions 2024, 2025

The File Converter for Android - Convert files to other formats - Updated Versions 2024, 2025

Discover the latest version of The File Converter for Android, an application that converts various file formats efficiently.

Updated Zalo Web Login Features for 2024-2025

Updated Zalo Web Login Features for 2024-2025

Explore the latest features of Zalo Web, allowing users to chat and connect seamlessly on various browsers without installation.

PDF Password Remover 6.0 - The Ultimate Solution for Password Removal in PDF Files

PDF Password Remover 6.0 - The Ultimate Solution for Password Removal in PDF Files

PDF Password Remover is a powerful tool for removing passwords and restrictions from PDF files. Download the latest version for 2024 to experience enhanced features.

Latest Instructions for Completely Removing Internet Download Manager (IDM) - Updated for 2024 and 2025

Latest Instructions for Completely Removing Internet Download Manager (IDM) - Updated for 2024 and 2025

Step-by-step instructions to completely remove Internet Download Manager (IDM) from your system in 2024 and 2025. Learn to uninstall IDM effectively and cleanly.

Mindjet MindManager 2024 & 2025 - Empower Your Mind Mapping

Mindjet MindManager 2024 & 2025 - Empower Your Mind Mapping

Discover Mindjet MindManager, an innovative software that helps individuals and teams streamline their work processes through effective mind mapping and project management.

Su Doku 2.0.0 - Latest Updates 2024 & 2025

Su Doku 2.0.0 - Latest Updates 2024 & 2025

Su DoKu 2.0.0 is an advanced Sudoku game design software that captivates enthusiasts with its simplicity and functionality. Download now and challenge yourself with Sudoku puzzles.

Google Photos for Android - Unlimited Photo and Video Storage with Latest Features in 2024, 2025

Google Photos for Android - Unlimited Photo and Video Storage with Latest Features in 2024, 2025

Discover the new features of Google Photos for Android in 2024 and 2025, including faster backup and advanced search capabilities.

Updated IQmango DVD Ripper 2024 - New Features and Enhancements

Updated IQmango DVD Ripper 2024 - New Features and Enhancements

Discover the latest version of IQmango DVD Ripper in 2024, a powerful software to easily rip, copy, convert, and back up DVDs.

When2meet - Tools to Arrange Group Meetings (Updated 2024, 2025)

When2meet - Tools to Arrange Group Meetings (Updated 2024, 2025)

When2meet is an easy-to-use group meeting scheduling tool for organizing collaborative meetings. Discover the latest features of When2meet for 2024 and 2025.