ImportExcel
The ImportExcel() function imports data from one Excel file into another. The function allows users to import specific columns that satisfy user-defined criteria.
Related functions: ImportCSV, ImportODBC, ImportText
Syntax
ImportExcel (File, Sheet, Range, Filter Expression, Columns)
| File | The full path and name of the Excel file to import from |
| Sheet | The name or number of the Excel worksheet to import from |
| Range | The range of Excel cells to import |
| Filter Expression | The expression representing the filter criteria to apply to the data |
| Columns | The columns that need to be returned. These columns are 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 from an Excel file, specify the sheet name and range
Example 2. Import from an Excel file, specify the sheet number and range
Example 3. Import from Excel without the header lines
Example 4. Import data that meets a criterion, and output specific columns
Example 5. 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 from an Excel file, specify the sheet name and range
In this example, the function imports data from a specified Excel file, sheet and range.
| Import all data from the file, sheet and range | ||||
| =ImportExcel(“\SomeComputerWorkUnitHolding31032015.xlsx”,”31 March 2015″,”A1:E10″) |
| Output | ||||
| Account No | Account Name | Asset Code | Sector | Market Value |
| 5030 | AGRI PROVIDENT FUND | NHP | Consumer Goods | 70000 |
| 5030 | AGRI PROVIDENT FUND | QGW | Consumer Goods | 12000 |
| 5030 | AGRI PROVIDENT FUND | QJU | Consumer Goods | 8000 |
| 5040 | TNT MEDICAL SCHEME | XDU | Consumer Services | 150000 |
| 5040 | TNT MEDICAL SCHEME | VRR | Consumer Services | 55000 |
| 5040 | TNT MEDICAL SCHEME | LEF | Consumer Services | 6700 |
| 5050 | XYZ STAFF PENSION FUND | JTD | Oil & Gas | 13000 |
| 5050 | XYZ STAFF PENSION FUND | ABA | Telecommunications | 1400 |
| 5050 | XYZ STAFF PENSION FUND | ZQI | Telecommunications | 85000 |
Example 2. Import from an Excel file, specify the sheet number and range
In this example, the function imports data from a specified Excel file, sheet number and range. The number should not be placed in quotes.
| Import all data from the file, sheet and range | ||||
| =ImportExcel(“\SomeComputerWorkUnitHolding31032015.xlsx”,3,”A1:E4″) |
| Output | ||||
| Account No | Account Name | Asset Code | Sector | Market Value |
| 5030 | AGRI PROVIDENT FUND | NHP | Consumer Goods | 70000 |
| 5030 | AGRI PROVIDENT FUND | QGW | Consumer Goods | 12000 |
| 5030 | AGRI PROVIDENT FUND | QJU | Consumer Goods | 8000 |
Example 3. Import from Excel without the header lines
In this example, the header line is omitted from the output.
| Omit the first line which contains the headers | ||||
| =ImportExcel(“\SomeComputerWorkUnitHolding31032015.xlsx”,”31 March 2015″,”A1:E10″,”Line = 1″,”*”) |
| Output | ||||
| 5030 | AGRI PROVIDENT FUND | NHP | Consumer Goods | 70000 |
| 5030 | AGRI PROVIDENT FUND | QGW | Consumer Goods | 12000 |
| 5030 | AGRI PROVIDENT FUND | QJU | Consumer Goods | 8000 |
| 5040 | TNT MEDICAL SCHEME | XDU | Consumer Services | 150000 |
| 5040 | TNT MEDICAL SCHEME | VRR | Consumer Services | 55000 |
| 5040 | TNT MEDICAL SCHEME | LEF | Consumer Services | 6700 |
| 5050 | XYZ STAFF PENSION FUND | JTD | Oil & Gas | 13000 |
| 5050 | XYZ STAFF PENSION FUND | ABA | Telecommunications | 1400 |
| 5050 | XYZ STAFF PENSION FUND | ZQI | Telecommunications | 85000 |
Note:
Columns, Rows and Line are reserved words. See more on reserved words.
Example 4. Import data that meets a criterion, and output specific columns
This example restricts the output to TNT Medical Scheme’s holdings and only shows certain columns.
| TNT Medical Scheme’s holdings only | ||
| =ImportExcel(“\SomeComputerWorkUnitHolding31032015.xlsx”,”31 March 2015″,”A1:E10″,”C1=5040″,”C2,C3,C5″) |
| Output | ||
| TNT MEDICAL SCHEME | XDU | 150000 |
| TNT MEDICAL SCHEME | VRR | 55000 |
| TNT MEDICAL SCHEME | LEF | 6700 |
Example 5. Import data that meets multiple criteria, and output specific columns
This example restricts the output to TNT Medical Scheme’s holdings where the market value of their holdings is less than 85000, and only shows certain columns.
| Account = TNT and market value < 85000 | ||
| =ImportExcel(“\SomeComputerWorkUnitHolding31032015.xlsx”,”31 March 2015″,”A1:E10″,”C1=5040 & C5 < 85000″,”C2,C3,C5″) |
| Output | ||
| TNT MEDICAL SCHEME | VRR | 55000 |
| TNT MEDICAL SCHEME | LEF | 6700 |