ReadCsvFile
(Requires Analytica Enterprise or Power Player)
ReadCsvFile(filename, linesep,colsep)
Reads and parses a tabular text file, known as a CSV (for "Comma-Separated Values") format, in which data is arranged in columns on separate lines, and in which the first line contains column labels.
Normally columns are separated by commas, although the optional colsep parameter can be specified if a different separation string is used.
An array of text values is returned, indexed by two local indexes, Row and Field. Typically some columns may contain text, others numbers, and other dates. You must post-process the result returned from ReadCsvFile using, for example, ParseDate and ParseNum to convert the text columns to numeric values. The Row index contains the column labels.
The data within a column in the file must never contain the separator strings.
Library
Flat File Library.ana
(Use File → Add Library... to add this library)
Example
Suppose the file "data.txt" is in the CurrentDataDirectory and contains the following:
PlantID,Name,Capacity,Start Date,Type G0372,Moss Landing,2218,7/11/2002,Oil/Gas N0002,San Onofre,2254,1/1/1968,Nuclear N0001,Diablo Canyon,2218,1/1/1985,Nuclear
The following expression
ReadCsvFile("data.txt")
returns an array of text indexed by local indexes Row and Fields as follows:
| .Fields | ||||||
|---|---|---|---|---|---|---|
| PlantID | Name | Capacity | Start Date | Type | ||
| .Row | 1 | G0372 | Moss Landing | 2218 | 7/11/2002 | Oil/Gas |
| 2 | N0002 | San Onofre | 2254 | 1/1/1968 | Nuclear | |
| 3 | N0001 | Diablo Canyon | 2218 | 1/1/1985 | Nuclear | |
In a case like this, one would typically want to parse the Capacity and Start Date columns to actual numeric values. So the expression would be enhanced to read
var f := ReadCsvFile('data.txt');
If f.Fields='Capacity' Then ParseNum(f[.Fields='Capacity'])
Else if f.Fields='Start Date' Then ParseDate([f[.Fields='Start Date'])
Else f
It would actually be a bit more elegant to break Capacity and Start_Date out as separate variables, e.g.:
Variable RawData := ReadCsvFile('data.txt')
Variable Capacity := ParseNum( RawData[.Fields='Capacity'] )
Variable Start_Date := ParseDate( RawData[.Fields='Start Date'] )
In this way, it is possible to display Capacity and Start_Date using separate number formats. (Note: In the previous code, RawData, Capacity and Start_Date are each variable nodes on a diagram, and their definition is shown to the right of :=). With Capacity as numeric, you can apply numeric operations, such as
Sum( Capacity, Capacity.Rows )
Enable comment auto-refresher