CODEWITHSUNDEEP
c#excel
Richi Sharma
Richi Sharma

Reputation: 185

OLEDB connection not reading string values C#

My requirement is to read a excel file from a local folder and import into DataTable. Importing work fine if the first row contains string value, If the value is int in first 3 rows the datatype of column becomes Integer and it is ignoring string values. I want to read a the values. I tried to insert a row of string values, but due to int datatype its not allowing. Plz help.. i am in a big trouble. I Tried with IMEX=1 in connection string, but no go

string Extension = ".xlsx";
string conStr = "";
switch (Extension)
{
    case ".xls": //Excel 97-03
    conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
    break;
    case ".xlsx": //Excel 07
    conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
    break;
}
conStr = String.Format(conStr, strPath);
oledbConn = new OleDbConnection(conStr);
if (oledbConn.State != ConnectionState.Open)
    oledbConn.Open();
OleDbCommand cmd = new OleDbCommand(); ;
OleDbDataAdapter oleda = new OleDbDataAdapter();
DataTable dt = new DataTable();
dt.Columns.Add("Store", typeof(string)).DefaultValue = strPath.Substring(18, 3);
var sheets = oledbConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
cmd.Connection = oledbConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = " SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";
oleda = new OleDbDataAdapter(cmd);
//oleda.FillSchema(dt, SchemaType.Source);
dt.TableName = strPath.Substring(18, 3);

oleda.Fill(dt);

Upvotes: 1

Views: 1827

Answers (1)

MartW
MartW

Reputation: 12548

This unfortunately comes down to registry settings used when using the Jet or ACE engines to import from Excel (and other file types). The engine will scan the first N rows of data (controlled by a registry value called TypeGuessRows, which defaults to 8), and use this to determine what it thinks the data type of each column is. If within the first TypeGuessRows rows of the spreadsheet it detects multiple types, it will then use the ImportMixedTypes setting (whose only valid values are Text and MajorityType, and Text is what you want here).

The simplest option for you here is probably to increase that TypeGuessRows setting. But if the spreadsheets you need to work with contain tens of thousands of rows, you'll have a problem as I believe that it won't sample more than 16,384 rows regardless. And if it doesn't think a particular column is mixed type, then it won't treat apply the ImportMixedTypes setting (so if you've got 20,000 rows, and the first 17000 are all int, that column is staying int even if the last 3000 aren't integers, and you'll get nulls in their place). Formatting the column in Excel will have no effect, nor will specifying a destination data type.

You may need to change the setting(s) in multiple places, depending on your connection string and the version of Excel or the Access Connectivity Engine installed. On Windows 7 64-bit, for example, the location for the Jet setting in the registry is HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel.

There are similar locations for using the ACE engine, depending on the version of Excel. More information here, both in the old blog post and the more recent comments.

Basically, importing from files using Jet/ACE can be a painful experience and some of the time you might not even notice there's a problem.

Upvotes: 5

Related Questions