OLEDB decimal issue

[TobbeK]

Is there any way to parse foreign decimal/double values from a Excel file with OLEDB? However, searches on Google shows this is not a new problem, so put my hope to anyone who maybe have a solution.

MY COMPUTER SETTINGS:
Non english settings on the machine, using the "," instead of "." as decimal separator. I have also tried to change settings on the machine, but no success.

PROBLEM:
All decimal values from Excel sheet containing a dot, returns a DBNull value. Example 1.5, 0.5 and so on. I want to read column from Excel sheet whatever the decimal format is, "," or "."

And yes I have tested ALL OF THE existing datatypes according to OLEDB. And yes I have done searches on Google.

[Narue]

I'd read it as a string and then parse it using a suitable culture:

using System;
using System.Globalization;

namespace JSW
{
    static class Program
    {
        static void Main()
        {
            string test = "12345,67";
            CultureInfo german = CultureInfo.CreateSpecificCulture("de-DE");

            Console.WriteLine("Default: " + decimal.Parse(test));
            Console.WriteLine("German:  " + decimal.Parse(test, german));
        }
    }
}

[TobbeK]

Thanks

The problem isn't the conversion after Import, it is the OLEDB Import itself, at least my qualified quess of the problem?! As long numeric values doesn't contains decimal points "." it works. Otherwise the value is always returned as DBNull.

I use a DataTable for "reading" the Excel values like this one:

DataTable myTable = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables_Info, null);
myTable.Locale = System.Globalization.CultureInfo.InvariantCulture;

DataRow myRow = myTable.Rows[0];
sheetName = myRow[2].ToString();

[Narue]

>Otherwise the value is always returned as DBNull.
Right, and that's why I said to read the value as a string.

>myTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
I'm not sure what you expect to happen here. The schema table holds schema information, not the spreadsheet data, so at this point you still have yet to read the decimal values. Further InvariantCulture doesn't support the radix character you want, so even if you used it in the actual conversion, it would still likely fail for the same reason.

[TobbeK]

Alright!

How can I expand my current code to involve string handling of cell values.

[Narue]

>How can I expand my current code to involve string handling of cell values.
I don't know. You haven't posted enough code for me to know how you're handling cell values currently. I know how I'd do it, but that doesn't help you modify your current code, does it? :)

How about posting a simple non-general program that loads a spreadsheet and prints the cells to a console window? Then I can see exactly how you're doing it now, and can offer suggestions on how to do it differently. That can be done less than a page of code, unless your current method is excessively complicated.

[TobbeK]

Hi

My code is provided below, stripped down a bit to contain only one OleDbParameter. But I think you can see the picture of it, from the Excel Export to MS Access Import.

fileNameExcel = openExcel.FileName;
	string fileNameAccess = filename;

	//Connection string for Excel

	    connectionStringExcel = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileNameExcel + ";Extended Properties=\'Excel 12.0;HDR=Yes;IMEX=1\'; ");

	//Connection object for Excel
	OleDbConnection connExcel = new OleDbConnection(connectionStringExcel);

	//Connection string for Access
	ConnectionStringAccess = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileNameAccess + ";Persist security Info = false");

	//Connection object for Access
	OleDbConnection connAccess = new OleDbConnection(ConnectionStringAccess);

	//Open connections
	connExcel.Open();
	connAccess.Open();

	//Get SheetNames
	DataTable sheetTable = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables_Info, null);
	DataRow rowSheetName = sheetTable.Rows[0];
	sheetName = rowSheetName[2].ToString();


	//Command object for Excel where data is stored
	OleDbCommand cmdExcel = connExcel.CreateCommand();
	cmdExcel.CommandType = CommandType.Text;
	cmdExcel.CommandText = "SELECT [column1] FROM [" + sheetName + "]";


	OleDbCommand cmdAccess = connAccess.CreateCommand();
	cmdAccess.CommandType = CommandType.Text;
	cmdAccess.CommandText = "INSERT INTO tbtemp ([column1]) VALUES(@column1)";



	// I use only 1 parameter for this example
	OleDbParameter param1 = new OleDbParameter("@column1", OleDbType.VarChar);
	cmdAccess.Parameters.Add(param1);


	//Read Excel
	OleDbDataReader drExcel = cmdExcel.ExecuteReader();

	else if (drExcel.HasRows == true)
	{

	    while (drExcel.Read())
	    {

		param1.Value = drExcel[0].ToString();
		cmdAccess.ExecuteNonQuery();

	    }
	}

	drExcel.Close();
	connExcel.Close();
	connAccess.Close();

    }
    catch (Exception ex)
    {
	MessageBox.Show(ex.Message);

    }
    finally
    {

    }
}

[TobbeK]

Has anyone experienced this problem with Excel data types below and OLEDB .

Excel using is own set of datatypes, and this is probably why some datatypes cannot be read from Excel sheet into OLEDB. Is it possible to convert any Excel data type to plain string for use in OLEDB DataReader used in my provided code in earlier post.

Excel data types:
Numeric – double-precision float (DT_R8)
Currency – currency (DT_CY)
Boolean – Boolean (DT_BOOL)
Date/time – datetime (DT_DATE)
String – Unicode string, length 255 (DT_WSTR)
Memo – Unicode text stream (DT_NTEXT)

Reference:
http://msdn.microsoft.com/en-us/library/ms141683.aspx