# CSV Tricks

## The Story

I’ve been requested to recreate some spreadsheet for our execs. Being annoying as usual, I made it a point to only use scripting to build the tables.

Most of the reconstruction is interesting in an unrelated way, but I wanted to post about some little gimmicks that helped me create the spreadsheet-like part using plain-old CSV.
I used Ruby as my CSV generator, which I then imported by LibreOffice’s Calc for some formatting touch-ups (bold titles etc.) and then to Google Docs.

## Formulas survive

I see no reason why they shouldn’t, but it still surprised me - when generating CSV files, you can keep cells with formulas (e.g. `=1+1`

) and they will be honored by LibreOffice’s Calc. This allowed me to preserve the spreadsheet’s formulated structure (e.g. calculated averages) while generating basic CSV, using Ruby’s `CSV`

module.

## R1C1 Notation

This is a way to represent cell addresses differently than the default `A1`

notation.

In `A1`

, every cell gets a column index in letters (`A,B,C..ZZZZZZZ`

) and a row index in numbers (`1,2,3...9999`

). The first cell is `A1`

.

In `R1C1`

notation, every cell is getting a numeral row index (`R1,R2..R9999`

) and column index (`C1,C2..C9999`

) joined together.

While `R1C1`

seems more cumbersome, it allows relative addressing, such as `RC[-1]`

(Cell to my left), `R3C`

(Third cell from the top in my column) and `R[1]C[1]`

(Cell immediately below and right to me). This allows you to address other cells without calculating their/your location, which is easy when using GUI, hard when pushing lines into a file.

This notation isn’t honored by default (my guess is because it can conflict with `A1`

notation), but you can either:

- Change your application’s settings to work with
`R1C1`

(Which I don’t like, since it’s a global setting and will probably break other spreadsheets) - Use the
`INDIRECT`

function (LibreOffice documentation) to address a cell, like`=INDIRECT("RC[-1]",0)`

for the cell to the immediate left. This function is also implemented in Google Docs.

Since you’re scripting anyway, you’re probably better off using the second option.

Should I encounter any other neat CSV discoveries, I’ll update this post.

## Credits

- R1C1 in stackoverflow