ImportCSV
The ImportCSV() function retrieves data from an external CSV (comma delimited) text file. The function allows users to import specific columns that satisfy user-defined criteria.
Related functions: ImportExcel, ImportODBC, ImportText
Syntax
ImportCSV (File, Filter Expression, Columns)
| File | The full path and name of the csv file 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 a CSV file into Excel
Example 2. Import a CSV file into Excel without the 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 a CSV file into Excel
UnitHoldings31032015.CSV:
Account No,Account Name,Asset Code,Sector,Holding
5000,ABC INSURANCE COMPANY,CEB,General Mining,500
5000,ABC INSURANCE COMPANY,MBD,Gold Mining,1000
5000,ABC INSURANCE COMPANY,KVJ,NonFerrous Metals,400
5010,QQQ RETIREMENT FUND,VRR,Apparel Retailers,700
5000,ABC INSURANCE COMPANY,QGW,Furnishing,20000
5000,ABC INSURANCE COMPANY,BSF,Clothing & Accessories,500
5000,ABC INSURANCE COMPANY,NHP,Food Products,77000
5090,TOP SOLUTIONS EQUITY FUND,NHP,Food Products,67000
This example imports the example CSV file above into Excel. Carriage returns are recognised as demarcations of new rows. Commas are used as column delimiters.
| Import all contents of the CSV file | ||||
| =ImportCSV(“\SomeComputerWorkUnitHolding31032015.csv”) |
| Output | ||||
| Account No | Account Name | Asset Code | Sector | Holding |
| 5000 | ABC INSURANCE COMPANY | CEB | General Mining | 500 |
| 5000 | ABC INSURANCE COMPANY | MBD | Gold Mining | 1000 |
| 5000 | ABC INSURANCE COMPANY | KVJ | NonFerrous Metals | 400 |
| 5010 | QQQ RETIREMENT FUND | VRR | Apparel Retailers | 700 |
| 5000 | ABC INSURANCE COMPANY | QGW | Furnishing | 20000 |
| 5000 | ABC INSURANCE COMPANY | BSF | Clothing & Accessories | 500 |
| 5000 | ABC INSURANCE COMPANY | NHP | Food Products | 250 |
| 5090 | TOP SOLUTIONS EQUITY FUND | NHP | Food Products | 67000 |
Example 2. Import a CSV file into Excel without the header lines
In this example, the header line is omitted from the output.
| Omit the first line which contains the headers | ||||
| =ImportCSV(“\SomeComputerWorkUnitHolding31032015.csv”,”Line 1″,”*”) |
| Output | ||||
| 5000 | ABC INSURANCE COMPANY | CEB | General Mining | 500 |
| 5000 | ABC INSURANCE COMPANY | MBD | Gold Mining | 1000 |
| 5000 | ABC INSURANCE COMPANY | KVJ | NonFerrous Metals | 400 |
| 5010 | QQQ RETIREMENT FUND | VRR | Apparel Retailers | 700 |
| 5000 | ABC INSURANCE COMPANY | QGW | Furnishing | 20000 |
| 5000 | ABC INSURANCE COMPANY | BSF | Clothing & Accessories | 500 |
| 5000 | ABC INSURANCE COMPANY | NHP | Food Products | 250 |
| 5090 | TOP SOLUTIONS EQUITY FUND | NHP | Food Products | 67000 |
Note:
Columns, Rows and Line are reserved words. See more on reserved words.
Example 3. Import data that meets a criterion, and output specific columns
This example restricts the output to ABC Insurance company’s holdings, and only outputs columns 2, 3 and 5.
| ABC holdings only | ||
| =ImportCSV(“\SomeComputerWorkUnitHolding31032015.csv”,”C1=5000″,”C2,C3,C5″) |
| Output | ||
| ABC INSURANCE COMPANY | CEB | 500 |
| ABC INSURANCE COMPANY | MBD | 1000 |
| ABC INSURANCE COMPANY | KVJ | 400 |
| ABC INSURANCE COMPANY | QGW | 20000 |
| ABC INSURANCE COMPANY | BSF | 500 |
| ABC INSURANCE COMPANY | NHP | 250 |
Example 4. Import data that meets multiple criteria, and output specific columns
This example restricts the output to data with the asset code ‘NHP’ and holdings greater than 50000 units. Note: in this example, the holdings in column 5 are explicitly recast to a number type (C5:N).
| Asset code = NHP and unit holdings 50000 | ||
| =ImportCSV(“\SomeComputerWorkUnitHolding31032015.csv”,”C3=’NHP’ & C5>50000″,”C2,C3,C5:N”) |
| Output | ||
| ABC INSURANCE COMPANY | NHP | 77000 |
| TOP SOLUTIONS EQUITY FUND | NHP | 67000 |
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 | |