CODEWITHSUNDEEP
c#linqcsv
Tim
Tim

Reputation: 51

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

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;
}

Upvotes: 1

Views: 8366

Answers (5)

Parag
Parag

Reputation: 430

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);
        }
    }
}

Upvotes: 0

Para
Para

Reputation: 858

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
}

Upvotes: 1

JWP
JWP

Reputation: 6963

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];

Upvotes: 0

Karthik Elumalai
Karthik Elumalai

Reputation: 1612

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

Upvotes: 0

Natraj Bontha
Natraj Bontha

Reputation: 16

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" );
}

Upvotes: 0

Related Questions