18

I need to add formulas to a CSV file that already exists. Is this possible to do this using VB.Net?

The idea is I already have a CSV file and I need one column to be populated in each cell with a custom formula. This has to be done programmatically because these files are being created dynamically.

Does anyone know how to do this? Thanks.

TWhite
  • 737
  • 2
  • 11
  • 33
  • just use formulas in "=..." notation Numbers and Libreoffice will happily apply them – Wolfgang Fahl Mar 12 '22 at 07:38
  • 1
    As a small note: if you want CSV + formulas to be able to store spreadsheets in git and have meaningful diffs, consider instead a file format Flat XML ODF Spreadsheet (.fods). It's basically a xml file, but the upside to CSV is that adding new columns would grow it down rather than to a side, so diffs will be mush easier to read as the file grows bigger. And you can also have stuff like bold/italic, etc. – Hi-Angel Sep 08 '22 at 18:32
  • @Hi-Angel what you're describing for git is exactly what I was looking into (combined with the subject of this question). Instead of FODS is there a way to make LO open the unzipped ODS file? that way the diffs become even more meaningful. – Bastiaan Quast Jan 02 '23 at 12:32
  • btw weird that this question is nearly ten years old and you posted this a few months ago (great minds) – Bastiaan Quast Jan 02 '23 at 12:33
  • 1
    @BastiaanQuast actually, I wouldn't be as surprised that over these years an answer regarding fods wasn't posted, given the question is about vb.net. OTOH, this question does come up when searching the functional without the relation to vb.net *(as you can guess by the amount of views, I doubt all these people came here for both vb.net and the functional at the same time)*, so I thought it would be useful to have `.fods` mentioned at least as a comment. Which already was useful to you :) And I don't think you can open an unzipped ods. – Hi-Angel Jan 02 '23 at 20:37

6 Answers6

37

1,2,3,=SUM(A1:C1)

Surprised me when I tried it, but Excel will keep the formula.

You can even export formulas into a CSV by first displaying them on screen. (Ctrl-`)

Greg Little
  • 3,360
  • 2
  • 19
  • 16
16

While I stand by that my original answer is technically correct, I have been getting a lot of downvotes on this. Apparently popular spreadsheet software such as Microsoft Excel, Open Office, Libre Office Calc will calculate formulas that are entered into a CSV file. However, I would still not recommend relying in this capability.

Original answer:
The CSV format does not support formulas. It is a plain text only format.

Tim B
  • 2,340
  • 15
  • 21
  • I didn't know this as I don't use CSV files. If I had it in an Excel file what would be the processes of inserting formulas? – TWhite Jun 11 '13 at 16:59
  • 1
    There is nothing built into .NET that will allow you to create/update Excel files directly. You can use the Jet OLE DB Provider and ADO.NET, or a third party Excel library. – Tim B Jun 11 '13 at 18:05
  • The worst part is that this answer is correct, but popular spreadsheet apps have DESTROYED security by removing that most very basic of assumptions. that CSVs are **supposed** to only be unexecutable text, and now because of this horrible against-standard behavior are one of the easiest and least guarded attack vectors possible – Sampson Crowley Jun 14 '22 at 08:51
  • @TimB I think the situation is that the RFC has a definition, but CSV as it is currently used is not really an expression of the RFC – Bastiaan Quast Jan 02 '23 at 12:34
10

You can import formula's into excel via a text file using comma separated values. Just remember to make sure it is named with the suffix .txt.

Then do the import.

My example import ( Data table, From Text)

Column1,Column2,ResultColumn

1,2,=A2+B2

It imported and computed just fine

Ed Michalski
  • 101
  • 1
  • 4
  • 1
    It looks like the OP needs this to be done programmatically, and this seems like much more of a manual effort. – SamHuckaby Dec 08 '14 at 17:27
  • 1
    Sam, in my case, I generate rather extensive spreadsheets using perl (outputting to a comma separated format). Even the formulas are being generated from my program. It seemed a simple process to import data from an arbitrary system that is already csv and add to it. But I do not have enough specifics to be sure what exactly is going on with the person asking the question. – Ed Michalski Dec 09 '14 at 01:42
  • This is very useful, and also works in libreoffice. I found that this works too: `4,3,2,=SUM(A2:C2)`. – user2023370 Aug 16 '16 at 12:41
3

Are you generating the CSV file? If so, consider writing an actual Excel file. (I'm assuming you're importing into Excel, since you used the term "cell", which has no meaning in CSV.)

Here's a link on how to do it: Create Excel (.XLS and .XLSX) file from C#

If you aren't generating the CSV, and if all you want is to add a new, calculated value,(rather than a formula that will change dynamically as cells change values) you can do this easily enough by reading in the CSV file, parsing each line enough to get the values you need for your formula, calculating the result, and appending it (after a comma) to each line before writing the line out to a new file.

Community
  • 1
  • 1
Ann L.
  • 13,760
  • 5
  • 35
  • 66
2

You could open the csv in Excel and then add the formulas to Excel and save back out to csv. You would do this by using the Microsoft Excel 11.0 Object Library. Then

dim wb as Excel.Workbook
dim exApp as New Excel.Application
dim exSheet as Excel.Worksheet
dim rowNum as integer = 1

wb = System.Runtime.InteropServices.Marshal.BindToMoniker(pathAndFileNameOfCSV)
exApp = wb.Parent

exApp.DisplayAlerts = False
exApp.AlertBeforeOverwriting = False

exSheet = exApp.ActiveWorkbook.Sheets.Item(1)

'do your functions in a loop here i.e.
exSheet.Range("A" & rowNum).Value = "=SUM($A$1:$D$4)"
rowNum += 1

wb.Close (True) 'closes and saves

Saving the workbook should convert the formulas back to the values when it is closed.

APrough
  • 2,671
  • 3
  • 23
  • 31
1

In Excel, to import formulas with commas the formula must be encapsulated by double quotes to prevent the formula being spread across cells. For example:

2,4,6,13,=sum(A1:C1),"=if(A1=C1,D1-A1,D1+A1)"

Another quirk of Excel is that if you have a string consisting entirely of numbers, you must present it as a formula to retain leading zeros. "00012345" imports as 12345, ignoring the quotes. To import as text, the .CSV file must present this as ="00012345".

Community
  • 1
  • 1
Richard Luketich
  • 19
  • 1