GetValue() does not read decimal values when first row is string

Question

'm using Microsoft.ACE.OLEDB.12.0 provider for reading data from Excel Sheet. I'm using OleDbDataReader and his GetValue() for getting data. First row/rows (can be more then one) is string header and I can't to skip it. Next are the numeric data that are set to 0 decimal place, but when I choose one of them, it appears in the bar in the correct decimal format.

How can I read this mixed data in full original decimal format like the bar in Excel? I can't change settings of excel sheet.

Here is my code:

using System.Data.OleDb;

    namespace ConsoleApplication2
    {
        class Program
        {
            static void Main(string[] args)
            {
                string query = "SELECT * FROM [List1$]";
                string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Temp\Test.xls;Extended Properties=""Excel 12.0;HDR=NO;IMEX=1""";
                using (OleDbConnection connection = new OleDbConnection(connString))
                {
                    connection.Open();
                    using (OleDbCommand command = new OleDbCommand(query, connection))
                    {
                        using (OleDbDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                object value = reader.GetValue(0);
                            }
                        }
                    }
                }
            }
        }
    }

Answer 1

From experience, the best you're probably gonna do is the below. I've always had issues with excel files, reading the data. Which is why I despise excel as a data transport mechanism.

I worked for a company that got all its "bank data" through excel. I'd be glad to be proven wrong.

Note. After the GetValue(0) runs, put a watch on it. It'll probably tell you its a string. But you can determine what it thinks it is, then adjust your "Get" mehthod a tad. Like, if the value is a "string", you can change GetValue(0) to GetString(0).

while (reader.Read())
{
    Decimal tryParseResultDec;
    object value = reader.GetValue(0);
    if !(Decimal.TryParse(value, out tryParseResultDec))
    {
        throw new ArgumentException(string.Format("Unable to parse '{0}'.", value));   
    }

}

Extra Suggestion.

Instead of "0", "1", "2", etc, I usually put some private const's at the top of the class to tell me what the columns are.

private const int EXCEL_COLUMN_TOTAL_AMOUNT = 0;

(you may do something like that, and you just kept the example simple)

EXTRA HINT:

I think the way it works is that excel will look at the first row of data , and look at the datatype, and use that for the rest of the rows in that same column. I do not think it says "check the datatype for each row". And thus your conundrum.

If you say there is not a header row, it will look at A1 for the datatype for all rows in A. If you say there is a header row, it will look at A2 for the datatype for all rows in A.

Answer 2

Try using HDR=YES in your connection string and stop skipping the first row:

string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Temp\Test.xls;Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""";

[UPDATE]

A workaround that I would suggest would you to use is reading twice the file (with the same method):

1. First, you get the header row, probably you will need that for the data structure later
2. On a second read, you skip the header and read the rows.

Here's how it should look like:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.OleDb;
using System.Data;
using System.IO;

class Program
{
    static void Main(string[] args)
    {
        // the Excel file
        string file = @"c:\Temp\Test.xls";

        if (!File.Exists(file))
        {
            Console.WriteLine("File not found.");
            return;
        }

        // DataTable bonus! :)
        System.Data.DataTable dt = new System.Data.DataTable();

        IEnumerable<List<object>> header = new List<List<object>>();
        IEnumerable<List<object>> rows = new List<List<object>>();

        // read the header first
        header = GetData(file, true);

        // read the rows
        rows = GetData(file, false);

        // add the columns
        foreach (var column in header.First())
        {
            dt.Columns.Add(column.ToString());
        }

        // add the rows
        foreach (var row in rows)
        {
            dt.Rows.Add(row.ToArray());
        }

        // now you may use the dt DataTable for your purpose
    }

    /// <summary>
    /// Read from the Excel file
    /// </summary>
    /// <param name="file">The path to the Excel file</param>
    /// <param name="readHeader">True if you want to read the header, 
    /// False if you want to read the rows</param>
    /// <returns></returns>
    private static IEnumerable<List<object>> GetData(string file, bool readHeader)
    {
        string query = "SELECT * FROM [List1$]";
        string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
            @"Data Source=" + file + @";Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX="
            + ((readHeader) ? "1" : "0") + @";""";
        using (OleDbConnection connection = new OleDbConnection(connString))
        {
            connection.Open();
            using (OleDbCommand command = new OleDbCommand(query, connection))
            {
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    bool isHeaderRead = false;
                    while (reader.Read())
                    {
                        if (readHeader && isHeaderRead)
                        { break; }
                        isHeaderRead = true;
                        List<object> values = new List<object>();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            values.Add(reader.GetValue(i));
                        }
                        yield return values;
                    }
                }
            }
        }
    }
}