0

I am trying to retrieve DataTable from .xls file. Below are my code:

OleDbConnection MyConnection = null;
DataSet DtSet = null;
OleDbDataAdapter MyCommand = null;
MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties=Excel 8.0;");

//path is where the .xls file located
ArrayList TblName = new ArrayList();

MyConnection.Open();
DataTable schemaTable = MyConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow row in schemaTable.Rows)
{
    TblName.Add(row["TABLE_NAME"]);
}

MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + TblName[0].ToString() + "] order by Material", MyConnection);
DtSet = new System.Data.DataSet();

MyCommand.Fill(DtSet);
MyCommand.FillSchema(DtSet, SchemaType.Source);

DataTable dt = new DataTable();
dt = DtSet.Tables[0];
MyConnection.Close();

Problem is: I have some inconsistent rows in my table, meaning they don't follow the other rows datatype.

Let's say in column A, I have cells that are supposed to be like:

105161610

146161701

196171717

.........

Meaning to say it's supposed to be of Int32 datatype.

These are the majority of the column cells..

I also have some other cells (still in the same column) that look like:

ABC9012

KDJ0981

KLP0001

.......

They somehow follow string datatype.

When I execute the code, I can only Select cells of int type while cells having the other type (string) is set to null instead. Although in my code I basically set the select * explicitly.

Can someone advise me on how to consistently retrieve both kind of datatype (instead of only 1 like what happens now)?

Community
  • 1
  • 1
rofans91
  • 2,970
  • 11
  • 45
  • 60
  • I think this might be relevant. [Importing Excel data containing mixed datatypes][1] [1]: http://stackoverflow.com/questions/3232281/oledb-mixed-excel-datatypes-missing-data – Codex Mar 28 '12 at 11:30

2 Answers2

1

You have to cast or convert both types of data to SQL equivalent of string like varchar.

Try either one of the following:

1. select cast(Column_A as varchar) Column_A from TableName order by Material 
2. select convert(varchar, Column_A) Column_A from TableName order by Material
Software Engineer
  • 3,906
  • 1
  • 26
  • 35
0

Add excel connection string IMEX=1; HDR={1} like full sting below

Description : IMEX=1 You can force mixed data to be converted to text HDR={1} indicates that the first row contains column names, not data header row if you dont want then put No

sachin ghorpade
  • 1