Given string array of column names, how do I read a .csv file to a DataTable?

Question

Assume I have a .csv file with 70 columns, but only 5 of the columns are what I need. I want to be able to pass a method a string array of the columns names that I want, and for it to return a datatable.

private void method(object sender, EventArgs e) {
    string[] columns =
    {
        @"Column21",
        @"Column48"
    };
    DataTable myDataTable = Get_DT(columns);
}

public DataTable Get_DT(string[] columns) {
    DataTable ret = new DataTable();
    if (columns.Length > 0) 
    {
        foreach (string column in columns)
        {
              ret.Columns.Add(column);
        }

        string[] csvlines = File.ReadAllLines(@"path to csv file");
        csvlines = csvlines.Skip(1).ToArray();  //ignore the columns in the first line of the csv file

        //this is where i need help... i want to use linq to read the fields
        //of the each row with only the columns name given in the string[] 
        //named columns
    }
    return ret;
}

I haven't tried this but if the CSV file has a header record - then you may be able to import using OleDb & select just the columns you want. https://stackoverflow.com/questions/6813607/parsing-csv-using-oledb-using-c-sharp — PaulF, Jun 01 '17 at 16:34
Looks like a great idea @PaulF. Here's an [example](https://stackoverflow.com/questions/6813607/parsing-csv-using-oledb-using-c-sharp). — Tom Blodget, Jun 01 '17 at 16:37
If the CSV hasn't got a header file, then you can provide a "schema.ini" file in the same folder to specify field names & types. https://learn.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver — PaulF, Jun 01 '17 at 16:46

score 1 · Accepted Answer · answered Jun 01 '17 at 16:47

Read the first line of the file, line.Split(',') (or whatever your delimiter is), then get the index of each column name and store that. Then for each other line, again do a var values = line.Split(','), then get the values from the columns.

Quick and dirty version:

string[] csvlines = File.ReadAllLines(@"path to csv file");
//select the indices of the columns we want
var cols = csvlines[0].Split(',').Select((val,i) => new { val, i }).Where(x => columns.Any(c => c == x.val)).Select(x => x.i).ToList();
//now go through the remaining lines
foreach (var line in csvlines.Skip(1))
{
    var line_values = line.Split(',').ToList();
    var dt_values = line_values.Where(x => cols.Contains(line_values.IndexOf(x)));
    //now do something with the values you got for this row, add them to your datatable
}

The point of this version was to make sure Tim could use dynamic column headers, passed in an array as he had asked. — Para, Jun 01 '17 at 16:51

score 0 · Answer 2 · answered Jun 01 '17 at 16:36

You can look at https://joshclose.github.io/CsvHelper/

Think Reading individual fields is what you are looking for

var csv = new CsvReader( textReader );
while( csv.Read() )
{
    var intField = csv.GetField<int>( 0 );
    var stringField = csv.GetField<string>( 1 );
    var boolField = csv.GetField<bool>( "HeaderName" );
}

score 0 · Answer 3 · answered Jun 01 '17 at 16:37

We can easily do this without writing much code.

Exceldatareader is an awesome dll for that, it will directly as a datable from the excel sheet with just one method.

here is the links for example:http://www.c-sharpcorner.com/blogs/using-iexceldatareader1

http://exceldatareader.codeplex.com/

Hope it was useful kindly let me know your thoughts or feedbacks

Thanks

Karthik

score 0 · Answer 4 · answered Jun 01 '17 at 16:40

0

var data = File.ReadAllLines(@"path to csv file");
// the expenses row
var query = data.Single(d => d[0] == "Expenses");
//third column
int column21 = 3;
return query[column21];

answered Jun 01 '17 at 16:40

JWP

6,672
3
50
74

score 0 · Answer 5 · answered Jun 01 '17 at 17:18

As others have stated a library like CsvReader can be used for this. As for linq, I don't think its suitable for this kind of job.

I haven't tested this but it should get you through

using (TextReader textReader = new StreamReader(filePath))
{
    using (var csvReader = new CsvReader(textReader))
    {
        var headers = csvReader.FieldHeaders;
        for (int rowIndex = 0; csvReader.Read(); rowIndex++)
        {
            var dataRow = dataTable.NewRow();
            for (int chosenColumnIndex = 0; chosenColumnIndex < columns.Count(); chosenColumnIndex++)
            {
                for (int headerIndex = 0; headerIndex < headers.Length; headerIndex++)
                {
                    if (headers[headerIndex] == columns[chosenColumnIndex])
                    {
                        dataRow[chosenColumnIndex] = csvReader.GetField<string>(headerIndex);
                    }
                }
            }
            dataTable.Rows.InsertAt(dataRow, rowIndex);
        }
    }
}

Given string array of column names, how do I read a .csv file to a DataTable?

5 Answers5