Import of text

Although Excel is the best software to work with tables, we often create them in other software. Also, tables are commonly used for data exchange, when they are exported as tables in a text file as “Comma Separated Values”. In this file, each row of the table is a separate line in the text, and the values that are in the columns are separated by commas (hence the name). In the text that follows, we will show how to import text from one such file …

In order to import the text, we need to first have a file with the data in the CSV format. It’s a traditional text file that we can open in Notepad and see its content. In particular, in this text you will find out how to import a simple table with regular numbers, product names and related prices.

First, we need to position the Data ribbon and then choose From Text  in the Get External Data group. The Import Text File window opens, and here we need to find the file that we want to  import. In our example, the file is called UVOZ.CSV. After loading that file, the Text Import Wizzard window will appear on the screen. Let’s do a small digression: data import can be done in the described way, directly from the file, and it is also possible to enter the file, copy the desired contents to the Clipboard and then paste it into the worksheet. On that occasion, all the copied values will be found in one column, so they need to be separated by the Text to Columns option, located in the Data ribbon . It is important that you know that after selecting the file in the Import Text File dialog box, it automatically launches the Text to Columns option, which opens the Text Import Wizzard dialog. No matter what way we choose to import text, the next procedure is practically the same.

The text import process takes place in three steps. In the first step, in the dialog window, we first need to determine whether the text is a fixed length (Fixed Width) or are the values to be transferred to columns separated by a certain symbol (Delimited). When the text is of a fixed length, in the next step, it can be manually determined how much the column width will be, by setting the number of characters that represent the width of each column. This can be a satisfactory way to separate content into columns, but it’s much better when data is separated by some symbol. This can be, as in our case, a comma. The symbol may also be some other sign, such as semicolon, tab, space … The first step in importing is to specify from which rowthe import of data begins, whether the table has a header, and which code page is used for text entry (this option may be important if we import some text containing international or, in our case, Serbian characters).

After clicking Next, in the second step, we indicate which symbol separates the columns (if we have previously selected the Delimited option) or manually adjust the column width (if Fixed Width has been selected). In this step it is also possible to specify which symbol is used as a text qualifier.

In the third step, we have the ability to determine which data type will be in the columns that we imported. By clicking on the column we specify designation, and by choosing from one of the radio buttons we specify a type of data (General, Text, Date) or if we want to skip import of a designated column.

Clicking on Finish will open another dialog box in which we choose whether to import a table starting from the specified cell in the current worksheet or we want to import it to a new worksheet. After we confirm the selection, the text will be imported into the table.