Using C# to search a CSV file and pull the value in the column next to it

Question

I am trying to make a program I am writing (C# in Visual Studio) look up a value in an external CSV file, and pull the value in the next column back in to a label in my WinForm.

My CSV file, which is a test with dummy data, is:

> old,newuser,newpassword
> firstlinetomakesure,firstnewusername,firstnewpassword
> adslusernameplaintext,thisisthenewuser,andthisisthenewpassword
> hello,terion,nadiomn
> somethingdownhere,thisisthelastuser,andthisisthelastpassword 
> 11,12,13
> 21,22,23 
> 31,32,33

I have tried using the solution at the link below, but could only return the value in the second column of the last line.

Search value in csv file using c#

I've been trying to make the program search the "old" column for a value, then pull in the value from the "newuser" column on the matching row. This would then go onto a label in the WinForm.

Any example code or suggestions would be appreciated.

This will do you good https://github.com/JoshClose/CsvHelper — trailmax, Feb 15 '18 at 13:44
@SamvelPetrosov I deleted the code I had in frustration I'm afraid. Sorry that's not helpful — Paul Wilson, Feb 15 '18 at 13:59
Try `Microsoft.VisualBasic.FileIO.TextFieldParser`. It's amazing what they hide away in these Visual Basic namespaces... — Nyerguds, Feb 15 '18 at 14:11

score 2 · Answer 1 · answered Feb 15 '18 at 13:46

2

Use a CSV library for reading CSV files. I like this one https://joshclose.github.io/CsvHelper/

Reading CSV files is not as straightforward as it seems. The number one difficulty is that values can have commas in them. The steps are simply, make a C# class to hold your data, map it to the data you see in your CSV file, and call the CSV library. That page I linked has enough examples to show you how to do this.

answered Feb 15 '18 at 13:46

user2023861

8,030
9
57
86

.Net actually contains a pretty good CSV reader... but they stupidly hid it in the Visual Basic namespace. It's called `Microsoft.VisualBasic.FileIO.TextFieldParser`. – Nyerguds Feb 15 '18 at 14:13

score 0 · Answer 2 · answered Feb 15 '18 at 13:50

Get LinqToExcel Using the Nuget Package Manager Console Copy Code PM> Install-Package LinqToExcel

void PrintArtistAlbums()
{
    string pathToExcelFile = ""
        + @"D:\Code\Blog Projects\BlogSandbox\ArtistAlbums.xlsx";

    string sheetName = "Sheet1";

    var excelFile = new ExcelQueryFactory(pathToExcelFile);
    var artistAlbums = from a in excelFile.Worksheet(sheetName) select a;

    foreach (var a in artistAlbums)
    {
        string artistInfo = "Artist Name: {0}; Album: {1}";
        Console.WriteLine(string.Format(artistInfo, a["Name"], a["Title"]));
    }
}

He is using **csv** file while you have added code for **xlsx**. — Samvel Petrosov, Feb 15 '18 at 14:03

Nyerguds · Answer 3 · 2018-02-27T06:14:55.253

Here's an interesting one, natively available in .Net, but hidden away in the Microsoft.VisualBasic.FileIO namespace: TextFieldParser. This can read the contents exactly like they are produced by MS Office, and it can even handle line breaks inside values.

public static List<String[]> SplitFile(String filePath, Encoding textEncoding, Char separator)
{
    String fileContents = File.ReadAllText(filePath, textEncoding);
    List<String[]> splitLines = new List<String[]>();
    try
    {
        using (StringReader sr = new StringReader(fileContents))
        using (TextFieldParser tfp = new TextFieldParser(sr))
        {
            tfp.TextFieldType = FieldType.Delimited;
            tfp.Delimiters = new String[] { separator.ToString() };
            while (true)
            {
                String[] curLine = tfp.ReadFields();
                if (curLine == null)
                    break;
                splitLines.Add(curLine);
            }
        }
        return splitLines;
    }
    catch (MalformedLineException mfle)
    {
        throw new FormatException(String.Format("Could not parse line {0} in file {1}!", mfle.LineNumber, filePath));
    }
}

Comes with its own handy MalformedLineException when parsing fails.

You need to add Microsoft.VisualBasic to the project references, of course.

score 0 · Answer 4 · answered Feb 15 '18 at 14:41

0

Here is another one, Cinchoo ETL - an open source file helper library to load CSV file and find the item as below

string csv = @"old,newuser,newpassword
firstlinetomakesure,firstnewusername,firstnewpassword
adslusernameplaintext,thisisthenewuser,andthisisthenewpassword
hello,terion,nadiomn
somethingdownhere,thisisthelastuser,andthisisthelastpassword 
11,12,13
21,22,23 
31,32,33";

using (var p = new ChoCSVReader(new StringReader(csv))
    .WithFirstLineHeader()
    )
{
    Console.WriteLine(p.Where(rec => rec.old == "hello").Select(rec => rec.newuser).First());
}

Disclaimer: I'm the author of this library.

answered Feb 15 '18 at 14:41

Cinchoo

6,088
2
19
34

Thanks for this. Gave it a go and the application kept exiting with error code -1. The WinForm never loaded and debug session terminated without any further details – Paul Wilson Feb 16 '18 at 09:58
Odd. can u try putting the below statement at the application startup and see any errors reported in the log file under log folder. `ChoETLFrxBootstrap.TraceLevel = System.Diagnostics.TraceLevel.Verbose;` – Cinchoo Feb 16 '18 at 12:55

Using C# to search a CSV file and pull the value in the column next to it

4 Answers4

Linked