Loading data from CSV files

From user code

The following code snippet, when put into the pre-constants user code, allows you to load CSV data from any file or input stream and put it into the grid at a specific position.

import clr
clr.AddReferenceByName("LumenWorks.Framework.IO")
from LumenWorks.Framework.IO.Csv import CsvReader
from System.IO import StreamReader

def ImportCSV(reader, topLeftCell):
    if isinstance(reader, basestring):
        reader = StreamReader(reader)

    csvReader = CsvReader(reader, False)
    csvReader.SkipEmptyLines = False

    rowOffset = 0
    while csvReader.ReadNextRecord():
        colCount = csvReader.FieldCount
        for colOffset in range(colCount):
            field = csvReader[colOffset]
            topLeftCell.Offset(colOffset, rowOffset).Value = field
        rowOffset += 1

A simple example of how to use it in your user code (assuming that the file C:\mycsvs.csv contains CSVs:

ImportCSV(r'C:\mycsvs.csv', workbook["Sheet1"].Cells.D3)

Here's how it works:

• The first three lines load up Sebastien Lorion's .NET CSV file parser, which he has kindly made available under an MIT license and which is used by Resolver One to parse CSV data when you paste it. Because it is distributed with Resolver One, you don't need to download it.
• The function creates a CSV parser object to parse the reader object that you pass in, then iterates over that object, writing the data into the cells below and to the right of the cell you specified as the top left.

From a formula

If you want your CSVs to sit inside a bounded area in the grid, and to use a formula to specify where that is, you can use a combination of code like the above and Resolver One's array handling. Here's the code first - this can live in the pre-constants user code:

import clr
clr.AddReferenceByName("LumenWorks.Framework.IO")
from LumenWorks.Framework.IO.Csv import CsvReader
from System import Array
from System.IO import StreamReader

def LoadCSV(reader):
    if isinstance(reader, basestring):
        reader = StreamReader(reader)

    csvReader = CsvReader(reader, False)
    csvReader.SkipEmptyLines = False

    rows = []
    maxCols = 0
    while csvReader.ReadNextRecord():
        row = []
        colCount = csvReader.FieldCount
        maxCols = max(colCount, maxCols)
        for colOffset in range(colCount):
            row.append(csvReader[colOffset])
        rows.append(row)

    ret = Array.CreateInstance(object, maxCols, len(rows))
    for rowIndex, row in enumerate(rows):
        for colIndex, val in enumerate(row):
            ret[colIndex, rowIndex] = val
    return ret

It's pretty much the same as the previous code, but copies the data from the CSV reader to a list of lists and then copies that into an array. (This is done in two steps because the CsvReader does not expose the number of rows in the file, and the array needs to know how big it should be at the point when it is constructed).

You can use a formula like this: =LoadCSV(r'C:\mycsvs.csv'). This will return an array, which is represented with the string <2D array>. To see the values, select a range with the formula's cell in the top left, then hit Control-Shift-Enter to unpack the array into the range.

Alternatively, you can pull a single value from the CSV file by using a subscript with the array formula; for example, you could pull out row 1, column 2 (arrays are indexed from zero upwards, so this is the second row, third column) with the formula =LoadCSV(r'C:\mycsvs.csv')[2, 1].