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

The two most commonly used text file formats are:

delimited text (TXT) files, which typically use a tab character (ASCII character code 009) to separate fields of text;

Comma-separated value (CSV) text files, which typically use a comma (,) as the text field separator.

You can change the separator character used in TXT and CSV files. This may be required to ensure successful implementation import and export.

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

Import a text file by opening it in Excel

A text file created in another program can be opened as Excel workbook using the command Open. When you open a text file in Excel, its format does not change - this can be seen in the title of the Excel window, where the name is indicated with the extension of the text file (for example, TXT or CSV).

Go to file > Open and navigate to the location containing the text file.

Select text files in the File Type drop-down list in the dialog box Opening .

Find required file and double click it.

Import a text file by connecting to it (Power Query)

1. On the "tab" data" in Group " Receiving & Converting Data" select from text or CSV.

   In the dialog box Import data locate and double-click the text file you want to import and click the button Import.

   There are several options in the Preview dialog box:

If Excel does not convert a specific column of data to required format, you can convert them after import. additional information see articles and.

Save as.

Import a text file by connecting to it

You can import data from a text file into an existing sheet.

If Excel doesn't convert the column to the required format, it can do so after import. For more information, see Convert numbers from text to numeric format and Convert dates from text to date format.

Export data to a text file by saving it

Excel sheet can be converted to a text file using the command Save as.

1. A dialog box will open Saving a document.

   In field File type Select a text file format for the sheet.

2. A dialog box appears reminding you that only the current worksheet will be saved as a text file. If you want to save exactly the current sheet, click the button OK. Other sheets can be saved as separate text files by repeating this procedure for each of them.

   A second dialog box appears reminding you that the worksheet you are saving may contain features that are not supported by text file formats. If you want to save only the sheet data in the new text file, click Yes. If you are unsure and want more information about what Excel features are not supported by text file formats, click Reference.

For more information about saving files in other file formats, see Save a workbook in another file format.

Changing the delimiter used in a text file

If you use the Text Import Wizard to import a text file, you can change the delimiter from the tab character to another character in the second step. This step can also change how consecutive delimiters (such as consecutive quotation marks) are handled.

For more information about delimiters and advanced options, see the Text Import Wizard.

Changing the separator in all text CSV files

Click the button Start and select Control Panel.

Open the Change Regional and Language Settings dialog box.

Enter a new separator in the field List element separator.

Press the button twice OK.

Note: After changing the list item separator, all programs on your computer will use new symbol. You can return the standard character by following the same steps.

24 Dec

Good afternoon, blog readers!

Today we will talk about exporting Excel table data to a text file.

You can immediately object - such a function is in the program! Yes, it exists, but uploading sometimes happens in the wrong encoding, I don’t know which cell contained the data, I have to rename and specify the location for the new file.

Let's try to get rid of these accompanying steps and agree on this:

• We will save it in the same folder as the table;
• the text file name will be the same.

Let’s open the table with the data and, as usual, go to the “Developer” tab.

We do not deviate from inserting the module:

1. “Developer” tab, “Code” button block, “Visual Basic” button;
2. Next “Insert” -> “Module”.

As soon as the module appears, insert the following macro there.

Sub SaveAsText()
Dim cell As Range
‘ Open the file to save (file name = name workbook, but differs in extension - TXT)
Open ThisWorkbook.Path & "\" & ThisWorkbook.Name & ".txt" For Output As #1
‘ Writing the contents of filled table cells to a file
For Each cell In ActiveSheet.UsedRange
If Not IsEmpty(cell) Then
Print #1, cell.Address, cell.Formula
End If
Next
‘Don’t forget to close the file
Close #1
End Sub

Necessary clarifications:

• Open ThisWorkbook.Path & "\" & ThisWorkbook.Name & ".txt" For Output As #1 — declare our file #1, save it in the same place as the book, the name of the text file will be the same as the book (with the addition of txt);
• For Each cell In ActiveSheet.UsedRange If Not IsEmpty(cell) Then — for each cell on the active sheet (if it is not empty) the following action will be applied;
• Print #1, cell.Address, cell.Formula — output the data to a text file along with the cell address;
• Close #1 —close the book.

Call the macro (Alt+F8), execute it (in the window that appears, click the “Run” button).

Nothing happens visually, but if we close the table and return to the folder where it is saved, we will see the following picture.

Everything is in accordance with the unloading conditions. When you open a text file, you will also see all the information about the table data - where, what is located.

Convert csv and txt format files to xls format can be done in two ways:

Method 1

This method is suitable for both users Microsoft programs Excel and for those who use Openoffice.

Text file or csv file can be opened like a book Microsoft Excel using the Open command. When you open a text file, its format does not change. If you open a csv file, Excel application will open it and display the data in a new workbook.

In Excel, click the button Open- a dialog box will appear Open.

For operating system Windows Vista select Text files. Next, find in address bar desired text file and double-click on it.

For Microsoft Windows XP select from the list File type - Text files. On the list Folder Find the text file you want and double click on it.

An example of what it looks like Openoffice

A window opens Importing text

After setting all the values, click Ok, and a table appears that you need to save. Come in File - Save As- and in the dialog box, select Microsoft format Excel 97/2000/XP (.xls) and the file is saved in xls format.

Method 2.

Suitable for Microsoft users Excel

You can import data from a text file into an existing worksheet by opening external data. Select a cell in the table to import external data. Next on the tab Data in Group External data click the button From the text.

For operating room Windows systems Vista, in the address bar, find the file you need to import and double-click on it.

For Microsoft Windows XP listed Folder Find the file you need to import and double-click on it. A dialog window will open Importing text, in which you can change the encoding (usually set to Unicode (UTF-8)), you can also select the delimiter that separates the values ​​in the csv file.

1)

2)

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

The two most commonly used text file formats are:

delimited text (TXT) files, which typically use a tab character (ASCII character code 009) to separate fields of text;

Comma-separated value (CSV) text files, which typically use a comma (,) as the text field separator.

You can change the separator character used in TXT and CSV files. This may be required to ensure that the import and export complete successfully.

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

Import a text file by opening it in Excel

A text file created in another program can be opened as an Excel workbook using the command Open. When you open a text file in Excel, its format does not change - this can be seen in the title of the Excel window, where the name is indicated with the extension of the text file (for example, TXT or CSV).

Go to file > Open and navigate to the location containing the text file.

Select text files in the File Type drop-down list in the dialog box Opening .

Find the file you want and double-click it.

Import a text file by connecting to it (Power Query)

1. On the "tab" data" in Group " Receiving & Converting Data" select from text or CSV.

   In the dialog box Import data locate and double-click the text file you want to import and click the button Import.

   There are several options in the Preview dialog box:

If Excel doesn't convert a certain column of data to the format you want, you can convert it after importing. For more information, see the articles and.

Save as.

Import a text file by connecting to it

You can import data from a text file into an existing sheet.

If Excel doesn't convert the column to the required format, it can do so after import. For more information, see Convert numbers from text to numeric format and Convert dates from text to date format.

Export data to a text file by saving it

An Excel sheet can be converted to a text file using the command Save as.

1. A dialog box will open Saving a document.

   In field File type Select a text file format for the sheet.

2. A dialog box appears reminding you that only the current worksheet will be saved as a text file. If you want to save exactly the current sheet, click the button OK. Other sheets can be saved as separate text files by repeating this procedure for each of them.

   A second dialog box appears reminding you that the worksheet you are saving may contain features that are not supported by text file formats. If you want to save only the sheet data in the new text file, click Yes. If you are unsure and want more information about what Excel features are not supported by text file formats, click Reference.

For more information about saving files in other file formats, see Save a workbook in another file format.

Changing the delimiter used in a text file

If you use the Text Import Wizard to import a text file, you can change the delimiter from the tab character to another character in the second step. This step can also change how consecutive delimiters (such as consecutive quotation marks) are handled.

For more information about delimiters and advanced options, see the Text Import Wizard.

Changing the delimiter in all CSV text files

Click the button Start and select Control Panel.

Open the Change Regional and Language Settings dialog box.

Enter a new separator in the field List element separator.

Press the button twice OK.

Note: After changing the list separator, all programs on your computer will use the new character. You can return the standard character by following the same steps.

Typing information from a keyboard is a troublesome and time-consuming task. It would be fine in Word, otherwise in Excel, where you need to move between cells. Therefore, if the information is in text form, this is already good. Let's look at how to quickly import data into Excel from a text file or, in other words, correctly insert text into an Excel table.

There is nothing very, very complicated here. As I have repeatedly noted, information in an Excel table must be presented in a strictly structured manner; therefore, in a text file, this structure must also be strictly observed.

Let's take this vital and simple example - transfer a list of employees from a text file (with the extension .txt) to Excel, which lists the last name, first name, patronymic, year of birth and position.

Let's say these are our heroes:

Ivanov Ivan Ivanovich 1978 inspector
Sidorov Petr Sidorovich 1970 senior inspector
Petrov Vasily Samuilovich 1965 senior inspector

I’m too lazy to add more than three 🙂, but that’s enough.

Now let's decide which one we need in Excel table? Let it have the following columns:
Last name, first name, patronymic, year of birth, position.

We have everything, but there is one nuance in the presentation of the position. For each employee, the position has different size– 1, 2 and 3 words. How can Excel tell where the information entered in the columns begins and ends?

For this it is convenient to use separator character. Such a symbol can be any character that does not appear in your text as a punctuation mark or a special character. That is, definitely try not to use a period and a comma, but you can use an asterisk or an oblique one. In our case, if everyone’s position consisted of one word or so - senior inspector, senior inspector himself (without spaces), then a space would do. Get creative to do less work. 🙂

Let's structure our list:

Last name/First name/Patronymic/Year of birth/Position
Ivanov/Ivan/Ivanovich/1978/inspector
Sidorov/Peter/Sidorovich/1970/senior inspector
Petrov/Vasily/Samuilovich/1965/senior inspector

and save in text document with extension .txt. Now such a list will be perfectly transferred to Excel.

To do this, go to the Excel tab Data. In area Receiving external data press the button From the text.

In the window that opens, point to our text file from which we will import data. After this it will open Import Wizard on the first step. Here we need to indicate the structure of our data - “ with delimiters” (which in our case is an oblique “/”). After this, press the button Further.

On next step wizard, you need to specify a delimiter character. Here are some options, but we choose “ another” and in the window we enter our symbol - oblique. Immediately in the lower area (with our list) appear vertical lines(columns) by which you can check the correctness of the data division. Press the button Further.

In the third step, you can specify the data format for each or selected column, if necessary. As a rule, this is true for date columns. We don’t change anything and press the button Ready.

After this, you need to indicate the cell from which the table will begin - its upper left corner.