Reading csv file in C#

Question

I am developing a project for biometric attendance.I a have a thumb punching machine which exports daily reports in excel format for time in and time out of a staff.

report format:

Now I want to develop a C# application to read this file and show some reports on attendance.I tried converting this file to csv and used below code but output is blank.Any help will be thankful to guide as how to read this file (xls/csv)?

C# code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                var column1 = new List<string>();
                var column2 = new List<string>();
                using (var rd = new StreamReader("db.csv"))
                {
                    while (!rd.EndOfStream)
                    {
                        var splits = rd.ReadLine().Split(';');
                        column1.Add(splits[0]);
                        column2.Add(splits[1]);
                    }
                }
                // print column1
                Console.WriteLine("Column 1:");
                foreach (var element in column1)
                    Console.WriteLine(element);

                // print column2
                Console.WriteLine("Column 2:");
                foreach (var element in column2)
                    Console.WriteLine(element);
            }
            catch (Exception ae)
            {
                Console.WriteLine(ae);
            }
            finally
            {
                Console.WriteLine("ok");
            }
        }
    }
}

Answer 1

You can find lot of method that does this

For CSV you can use http://kbcsv.codeplex.com/

For reading Excel you need to add Com component to your project i.e. Microsoft Excel 12.0 Object Library e.g. You can refer Reading Excel files from C#

e.g. code

string con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp\test.xls;Extended Properties='Excel 8.0;HDR=Yes;'"
using(OleDbConnection connection = new OleDbConnection(con))
{
    connection.Open();
    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection) 
    using(OleDbDataReader dr = command.ExecuteReader())
    {
         while(dr.Read())
         {
             var row1Col0 = dr[0];
             Console.WriteLine(row1Col0);
         }
    }
}

Answer 2

Use "LinqToExcel" - then you can do this kind of thing:

var book = new LinqToExcel.ExcelQueryFactory(@"C:\Path\File.csv");

var query =
    from row in book.Worksheet<MyObject>()
    where row.Status != "Disabled"
    select new
    {
        row.Date,
        row.Description,
    };

Answer 3

Make use of csv helper library

var csv = new CsvReader( File.OpenRead( "file.csv" ) );
var myCustomObjects = csv.GetRecords<CustomObject>();

Answer 4

I'd recommend LightweightExcelReader for this. To read the spreadsheet in your example you could do something like:

ExcelReader reader = new ExcelReader("path/to/ExcelFile.xlsx");
reader.ReadNext(); //for the purposes of this example we'll skip the first line

/*The code within the while loop will read the data in rows 2-7, then rows 8-13, etc.*/
while (reader.ReadNext())
{
    Console.WriteLine("Header Cell:");
    Console.WriteLine(reader.Value); //Writes the value of cell A2

    reader.ReadNext(); //Moves the reader to the next cell, e.g.. the start of row 3
    do
    {
        Console.WriteLine("Value at cell" + reader.Address);
        Console.WriteLine(reader.Value);
    } while(reader.ReadNextInRow()); //Do/while reads the values in row 3, i.e. "1", "2", "3"

    reader.ReadNext(); //Moves the reader to the start of row 4
    do
    {
        Console.WriteLine("Value at cell" + reader.Address);
        Console.WriteLine(reader.Value);
    } while(reader.ReadNextInRow()); //Reads the only value in row 4, e.g. "21:10"

    reader.ReadNext();
    while(reader.ReadNextInRow()); //Skips row 5    
}

Disclaimer: I'm the author of LightweightExcelReader.