Log in | Sign up

Resolver One: The Python-powered spreadsheet
Download or buy ($99)


Loading data from CSV files

This topic shows some examples of loading data from CSV files.

Although you can open a CSV file into a new document using the File | Import | From CSV... command, sometimes you want to automatically reload a CSV data file into another spreadsheet on every recalculation instead. You can do that from user code, or, if the data you want to load is bounded, from a formula.

You will learn how to load data from CSV files using

  • User code
  • Formulae

From user code

The following code snippet belongs in the Pre-Constants user code. It allows you to load CSV data from any file or input stream, placing it in 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

You would use the above code like this:

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

How the code works

  1. 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.
  2. 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

You can combine Resolver One's array handling with user code to display the contents of a CSV inside inside a bounded area in the grid, as specified by a formula.

The code

Here's the code first. It belongs 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.

The formula

You can use the above code in a formula:

=LoadCSV(r'C:\mycsvs.csv').

This returns an array, which is represented by 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, pull a single value from the CSV file by using a subscript with the array formula. For example, =LoadCSV(r'C:\mycsvs.csv')[2, 1] returns the data found in row 1, column 2 in the CSV file. (Arrays are indexed from zero upwards, so this is the second row, third column.)

Comments