litLIB Help
Product pages :
litLIB
Quick links:
help index
forums

String - CSV

Syntax:
=CSV(Source, [ SourceType, Separator, Skip, SortByCol, SortMode ])

Note that this is an array function. First select a range of cells to receive the output (e.g. 3 by 10 cells) Then enter the formula and then press Ctrl+Shift+Enter in the formula editor.

Description:
Returns in separate cells the contents of a CSV file, string or webpage. By creating the CSV function as array formula you can receive multiple values from the source in separate cells.

Source: The source of the CSV data. This can be a string, a file in which case you enter the full file path, or a web page in which case you type the full url.
SourceType: (default string) The type of data source. Set it to web or http for a web page. Or set it to file to read the CSV data from a file
Separator : (default ,) The delimiter character that separates the fields in the CSV data.
Skip: Number of rows to skip.
SortByCol: One column can be used to sort the csv data. Enter the column number.
SortMode : (default 0): 0 to sort ascending, 1 to sort descending

Examples:

Display the contents of CSV data from the web:
Display the daily data from the Dow Jones Industrial Average from 1/Dec/2008 to 1/Dec/2009
Select a block of cells (e.g. 10 rows and 7 columns), enter the following formula and press Alt-Enter
=csv("http://ichart.finance.yahoo.com/table.csv?s=^DJI&a=1&b=1&c=2000&d=1&e=1&f=2001&g=d&ignore=.csv","http")


Result:

Date Open High Low Close Volume Adj Close
36923 636.82 638.27 632.56 634.04 0 634.04
36922 636.17 640.5 633.8 639.98 0 639.98
36921 638.91 639.11 633.25 634.89 0 634.89
36920 633.04 637.19 632.99 637.19 0 637.19
36917 632.6 636.22 631.86 632.57 0 632.57
36916 633.42 638.18 632.4 636.14 0 636.14
36915 636.68 638.85 632.43 635.94 0 635.94
36914 626.96 630.76 623.19 630.51 0 630.51
36913 627.84 631.14 620.42 628.84 0 628.84

Reults:



Display the contents of a CSV file:
Select a block of cells (e.g. 2 rows and 3 columns), enter the following formula and press Ctrl+Shift+Enter
=WorksheetName("c:\mydir\myfile.csv","file")

Source file:
1,2,3,4
a,b,c,d

Result:
      A    B    C    D

1    1    2     3     4
2    a    b     c     d      

Parse a CSV string:
A1: "1,2,3"
A2:C2: =csv(A1)

Result:
       A       B      C
1     1,2,3
2     1        2      3