Getting numerical amounts from csv files

Question

Hi my application basically reads a CSV file which will always have the same format and I need the application to create a CSV file with different formatting. Reading and writing CSV file is not the issue, however the problem I am having is getting the amounts value as these are formatted with a , in the csv file (ex: 4, 500). Having said that these are being split when writing to csv file.

Ex: From the below, how can I get the full numbers .i.e. 2241.84 & 1072809.33

line = "\"02 MAY 18\",\"TTEWTWTE\",\"GRHGWHWH\",\"02 MAY 18\",\"2,241.84\",\"\",\"1,072,809.33\""

This is how I am reading from CSV file.

        openFileDialog1.ShowDialog();
        var reader = new StreamReader(File.OpenRead(openFileDialog1.FileName));
        List<string> searchList = new List<string>();
        while (!reader.EndOfStream)
        {
            var line = reader.ReadLine();
            searchList.Add(line);
        }

So far I have tried to use the below which gets you \"2,241.84\" which is correct but when writing to csv file I am only getting 2

searchList[2].Split(',')[1].Replace("\"", "")

@SeM so far I tried using the searchList[2].Split(',')[1].Replace("\"", "") — Carla Fenech, May 18 '18 at 12:29
Use an actual parser, like [`Microsoft.VisualBasic.FileIO.TextFieldParser`](https://stackoverflow.com/a/48809517/395685). It helps. — Nyerguds, May 18 '18 at 12:37

score 1 · Answer 1 · answered May 18 '18 at 12:36

1

Let me visualize contents in another way:

"
    \"02 MAY 18\",
    \"TTEWTWTE\",
    \"GRHGWHWH\",
    \"02 MAY 18\",
    \"2,241.84\",
    \"\",
    \"1,072,809.33\"
"

It seems that your separator is \", rather than ,. Change searchList[2].Split(',')[1].Replace("\"", "") to searchList[1].Split(new string[] { "\",\"" }, StringSplitOptions.None).

answered May 18 '18 at 12:36

raidensan

1,099
13
31

1

Not actually true. CSV [has specs](https://tools.ietf.org/html/rfc4180). You are ignoring the quotes before and behind it, and this still won't be able to handle all cases, like possible doubled quotes inside quoted pieces. – Nyerguds May 18 '18 at 12:41
I am getting the correct numbers however they are still being split in 3 different cells in excel :/ – Carla Fenech May 18 '18 at 12:44
@Nyerguds You are correct, providing that the file in question is implemented by adhering to specs. This might not be a healthy assumption for all _CSV-like_ files. – raidensan May 18 '18 at 12:46
Fairly sure it's MS Excel output. The "always the same" in the question is a naive assumption of someone who hasn't encountered the special cases yet. – Nyerguds May 18 '18 at 12:55

score 0 · Answer 2 · answered May 18 '18 at 12:34

0

In your case you can use this:

var result = searchList[2].Split(new string[] { "\",\"" }, StringSplitOptions.None)[4].Replace("\"", "");

Split your string with "," separator, instead of ,.

I don't know why you are using static numbers for indexes, but I will assume it's for test purposes.

answered May 18 '18 at 12:34

SᴇM

7,024
3
24
41

As I commented on the other question here, this only works until you find any of the _other_ special cases in [the CSV specs](https://tools.ietf.org/html/rfc4180). Like the fact fields can contain quotes and even _line breaks_. – Nyerguds May 18 '18 at 12:43
I am getting the correct numbers however they are still being split in 3 different cells in excel :/ – Carla Fenech May 18 '18 at 12:44
@Nyerguds _" my application basically reads a CSV file which will always have the same format"_ – SᴇM May 18 '18 at 12:45
@CarlaFenech what excel? – SᴇM May 18 '18 at 12:46
@SeM, Yes, and I know that format; it's the output that comes from MS Excel when saving a spreadsheet as CSV. No need to reinvent the wheel; [the .Net framework already has a parser for it](https://msdn.microsoft.com/en-us/library/microsoft.visualbasic.fileio.textfieldparser). – Nyerguds May 18 '18 at 12:46
@Nyerguds I'm basically trying to say, that if OP is pretty sure about csv string format, then he can use this. – SᴇM May 18 '18 at 12:47
@CarlaFenech In Excel? What do you mean? What Excel does with your _input_ is unrelated to this problem, and what it does with the _output_ is 100% related to how you make that output, which would be the processing that comes _after_ the code that's relevant to this question. – Nyerguds May 18 '18 at 12:50
the csv file generated will be opened by Excel. So what I need to do with my application is to format the amount so that the comma is removed and the amount is in one cell when opened in Excel. – Carla Fenech May 18 '18 at 12:52
@CarlaFenech Ask another question. That's not related to this one. – SᴇM May 18 '18 at 12:53
@CarlaFenech You never posted any code for the writing, and your question title is only about the reading. Read the specs I linked to, it mentions all special cases. – Nyerguds May 18 '18 at 12:54

Getting numerical amounts from csv files

2 Answers2