ImportText
The ImportText() function imports data from a Text file into Excel. The function allows users to import specific columns that satisfy user-defined criteria.
Related functions: ImportCSV, ImportExcel, ImportODBC
Syntax
ImportText (File, Delimiter, Filter Expression, Columns)
| File | The full path and name of the text file to import |
| Delimiter | The character that specifies the end of a column in the text file |
| Filter Expression | The expression representing the filter criteria to apply to the data |
| Columns | The columns to return, represented as C1,C2,C3. If the value is set to ‘*’ or omitted, then all columns are returned. |
Note:
Values are imported as text. To recast the text in the output columns to particular types, append a colon and a type, for example, “C1:N”, where N = number, T = text and D = date.
The most recently saved import source file is imported. If the import source file is being edited, any changes will not be imported until the file is saved and the import function is recalculated.
Use file paths that specify the computer name rather than the drive letter, for example “\computerfolderfile.txt” rather than “C:folderfile.txt”. If the file is shared and users have mapped different drives to the same path, the import function could fail for some users.
Examples
Example 1. Import data from delimited text files into Excel
Example 2. Import data from delimited text files into Excel without header lines
Example 3. Import data that meets a criterion, and output specific columns
Example 4. Import data that meets multiple criteria, and output specific columns
Note:
How your system administrator configured Quintessence determines which function parameters are required. In most implementations, administrators configure functions so that certain parameters default to specific values when left blank.
Example 1. Import data from delimited text files into Excel
Account No, Account Name, Asset Code, Holding
5000,ABC INSURANCE COMPANY,BSF,8000
5030,AGRI PROVIDENT FUND,BSF,65000
5060,NNN LIFE LIMITED,BSF,300
5090,TOP SOLUTIONS EQUITY FUND,BSF,55000
5090,TOP SOLUTIONS EQUITY FUND,NEM,800
Without specifying a delimiter, columns are not recognised (note: carriage returns in the text files are treated as rows)
| No delimiter is specified |
| =ImportText(“\SomeComputer\WorkHoldingsData.txt”) |
| Output |
| Account No, Account Name, Asset Code, Holding |
| 5000,ABC INSURANCE COMPANY,BSF,8000 |
| 5030,AGRI PROVIDENT FUND,BSF,65000 |
| 5060,NNN LIFE LIMITED,BSF,300 |
| 5090,TOP SOLUTIONS EQUITY FUND,BSF,55000 |
| 5090,TOP SOLUTIONS EQUITY FUND,NEM,800 |
| A comma delimiter is specified | |||
| =ImportText(“\SomeComputer\WorkHoldingsData.txt”,”,”) |
| Output | |||
| Account No | Account Name | Asset Code | Holding |
| 5000 | ABC INSURANCE COMPANY | BSF | 8000 |
| 5030 | AGRI PROVIDENT FUND | BSF | 65000 |
| 5060 | NNN LIFE LIMITED | BSF | 300 |
| 5090 | TOP SOLUTIONS EQUITY FUND | BSF | 55000 |
| 5090 | TOP SOLUTIONS EQUITY FUND | NEM | 800 |
Example 2. Import data from delimited text files into Excel without header lines
In this example, the header line is omitted from the output.
| Omit the header line |
| =ImportText(“\SomeComputer\WorkHoldingsData.txt”,”,”,”Line 1″) |
| Output | |||
| 5000 | ABC INSURANCE COMPANY | BSF | 8000 |
| 5030 | AGRI PROVIDENT FUND | BSF | 65000 |
| 5060 | NNN LIFE LIMITED | BSF | 300 |
| 5090 | TOP SOLUTIONS EQUITY FUND | BSF | 55000 |
| 5090 | TOP SOLUTIONS EQUITY FUND | NEM | 800 |
Example 3. Import data that meets a criterion, and output specific columns
This example restricts the output to holdings > 50000, and only shows columns 2 and 4. Note: in this example, the holdings in column 4 are explicitly recast to a number type (C4:N).
| Holdings 50,000 | |
| =ImportText(“\SomeComputer\WorkHoldingsData.txt”,”,”,”C4>50000″,”C2,C4:N”) |
| Output | |
| Account Name | Holding |
| AGRI PROVIDENT FUND | 65000 |
| TOP SOLUTIONS EQUITY FUND | 55000 |
Example 4. Import data that meets multiple criteria, and output specific columns
This example restricts the output to instrument ‘BSF’ where holdings < 60000, and only shows columns 2 and 4. Note: in this example, the holdings in column 4 are explicitly recast to a number type (C4:N).
| Instrument – BSF and holdings are less that 60000 | |||
| =ImportText(“\SomeComputer\WorkHoldingsData.txt”,”,”,”C3 = BSF & C4<60000″) |
| Output | |||
| 5000 | ABC INSURANCE COMPANY | BSF | 8000 |
| 5060 | NNN LIFE LIMITED | BSF | 300 |
| 5090 | TOP SOLUTIONS EQUITY FUND | BSF | 55000 |
Differences between ImportText and ImportCSV
There are subtle differences between the behaviour of ImportText and ImportCSV. Given a CSV and TXT file with the following identical contents: “AAA”,”BBB”,”CCC, DDD”:
| Function | Output | ||||
| =ImportText(“\SomeComputer\WorkMyTextFile.txt”) | Import Text | “AAA”,”BBB”,”CCC,DDD” | |||
| =ImportText(“\SomeComputer\WorkMyTextFile.txt”,”,”) | Import text and specify a comma delimiter | “AAA” | “BBB” | “CCC | DDD” |
| =ImportCSV(“\SomeComputer\WorkMyCSVFile.CSV”) | Import CSV | AAA | BBB | CCC,DDD | |