blocks|key|1202242|text|在使用Excel4.0和读取.Net文件时，我遇到了类似的OleDbDataAdapter问题--即在MS+Excel中读入"+PartID“列的混合数据类型，其中PartID值可以是数字(例如561)或文本(例如HL4354)，即使Excel列被格式化为"+text”。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1202243|据我所知，ADO.NET根据列中的大多数值选择数据类型(与数值数据类型一致)。也就是说，如果样本集中的大部分PartID都是数字，则ADO.NET将声明该列为数字。因此，ADO.Net将尝试将每个单元格转换为一个数字，对于"text“PartID值，这将失败，并且不会导入这些"text”PartID。|1202244|我的解决方案是将OleDbConnection连接字符串设置为使用Extended+Properties=IMEX=1;HDR=NO，以指示这是一个导入，并且表将不包含标头。excel文件有一个标题行，因此在本例中告诉ado.net不要使用它。然后在后面的代码中，从dataset中删除标题行，这样就有了该列的混合数据类型。|1202245|string+sql+=+"SELECT+F1,+F2,+F3,+F4,+F5+FROM+[sheet1$]+WHERE+F1+IS+NOT+NULL";

OleDbConnection+connection+=+new+OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data+Source="+%2B+PrmPathExcelFile+%2B+@";Extended+Properties=""Excel+8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text""");

OleDbCommand+cmd+=+new+OleDbCommand(sql,+connection);
OleDbDataAdapter+da+=+new+OleDbDataAdapter(cmd);

DataSet+ds+=+new+DataSet();
ds.Tables.Add("xlsImport",+"Excel");
da.Fill(ds,+"xlsImport");

//+Remove+the+first+row+(header+row)
DataRow+rowDel+=+ds.Tables["xlsImport"].Rows[0];
ds.Tables["xlsImport"].Rows.Remove(rowDel);

ds.Tables["xlsImport"].Columns[0].ColumnName+=+"LocationID";
ds.Tables["xlsImport"].Columns[1].ColumnName+=+"PartID";
ds.Tables["xlsImport"].Columns[2].ColumnName+=+"Qty";
ds.Tables["xlsImport"].Columns[3].ColumnName+=+"UserNotes";
ds.Tables["xlsImport"].Columns[4].ColumnName+=+"UserID";

connection.Close();+|code-block|syntax|javascript|1202246|//现在可以使用LINQ搜索字段了|1202247|++++var+data+=+ds.Tables["xlsImport"].AsEnumerable();
++++var+query+=+data.Where(x+=>+x.Field<string>("LocationID")+==+"COOKCOUNTY").Select(x+=>
++++++++++++++++new+Contact
++++++++++++++++{
++++++++++++++++++++LocationID=+x.Field<string>("LocationID"),
++++++++++++++++++++PartID+=+x.Field<string>("PartID"),
++++++++++++++++++++Quantity+=+x.Field<string>("Qty"),
++++++++++++++++++++Notes+=+x.Field<string>("UserNotes"),
++++++++++++++++++++UserID+=+x.Field<string>("UserID")
++++++++++++++++});|1202248|entityMap^0|T|G|0|0|8|F|X|X|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|X|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|Y|8|@$9|Z|A|10|B|C]|$9|11|A|12|B|C]]|D|@]|E|$]]|$1|J|3|K|5|L|7|13|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|14|8|@]|D|@]|E|$]]|$1|Q|3|R|5|L|7|15|8|@]|D|@]|E|$M|N]]|$1|S|3|-4|5|6|7|16|8|@]|D|@]|E|$]]]|T|$]]

Using .Net 4.0 and reading Excel files, I had a similar issue with <code>OleDbDataAdapter</code> - i.e. reading in a mixed data type on a "PartID" column in MS Excel, where a PartID value can be numeric (e.g. 561) or text (e.g. HL4354), even though the excel column was formatted as "Text". 

From what I can tell, ADO.NET chooses the data type based on the majority of the values in the column (with a tie going to numeric data type). i.e. if most of the PartID's in the sample set are numeric, ADO.NET will declare the column to be numeric. Therefore ADO.Net will attempt to cast each cell to a number, which will fail for the "text" PartID values and not import those "text" PartID's. 

My solution was to set the <code>OleDbConnection</code> connectionstring to use <code>Extended Properties=IMEX=1;HDR=NO</code> to indicate this is an Import and that the table(s) will not include headers. The excel file has a header row, so in this case tell ado.net not to use it. Then later in the code, remove that header row from the dataset and voilà you have mixed data type for that column.

<pre class="lang-cs prettyprint-override"><code>string sql = "SELECT F1, F2, F3, F4, F5 FROM [sheet1$] WHERE F1 IS NOT NULL";

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PrmPathExcelFile + @";Extended Properties=""Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text""");

OleDbCommand cmd = new OleDbCommand(sql, connection);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataSet ds = new DataSet();
ds.Tables.Add("xlsImport", "Excel");
da.Fill(ds, "xlsImport");

// Remove the first row (header row)
DataRow rowDel = ds.Tables["xlsImport"].Rows[0];
ds.Tables["xlsImport"].Rows.Remove(rowDel);

ds.Tables["xlsImport"].Columns[0].ColumnName = "LocationID";
ds.Tables["xlsImport"].Columns[1].ColumnName = "PartID";
ds.Tables["xlsImport"].Columns[2].ColumnName = "Qty";
ds.Tables["xlsImport"].Columns[3].ColumnName = "UserNotes";
ds.Tables["xlsImport"].Columns[4].ColumnName = "UserID";

connection.Close(); 
</code></pre>

// now you can use LINQ to search the fields

<pre class="lang-cs prettyprint-override"><code> var data = ds.Tables["xlsImport"].AsEnumerable();
 var query = data.Where(x =&gt; x.Field&lt;string&gt;("LocationID") == "COOKCOUNTY").Select(x =&gt;
 new Contact
 {
 LocationID= x.Field&lt;string&gt;("LocationID"),
 PartID = x.Field&lt;string&gt;("PartID"),
 Quantity = x.Field&lt;string&gt;("Qty"),
 Notes = x.Field&lt;string&gt;("UserNotes"),
 UserID = x.Field&lt;string&gt;("UserID")
 });
</code></pre>

blocks|key|1202315|text|@Brian+Wells谢谢你，你的建议起到了作用，但不是完全...对混合字段int-string有效，但是datetime列在那之后使用了奇怪的字符，所以我在"hack“上应用了"hack”。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1202316|1.-执行System.Io.File.Copy并创建excel文件的副本。|1202317|2.-在运行时以编程方式将Datetime列标题修改为datetime格式，即"01/01/0001“。|1202318|3.-保存excel，然后将使用HDR=NO执行查询的技巧应用于修改后的文件。|1202319|是的，这很棘手，但很有效，而且相当快，如果有人有任何替代方案，我将很高兴听到。|1202320|欢迎光临。|1202321|请原谅我的英语，这不是我的母语。|1202322|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|S|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|U|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|V|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|O|$]]

@Brian Wells Thank you, your suggestion did the trick, but not totally... Worked for the mixed field int-string, but the datetime columns went with strange characters after that, so i applied a "hack" over the "hack".

1.- Do a System.Io.File.Copy and create a copy of the excel file.

2.- Modify the Datetime column headers programatically at runtime to something in datetime format, i.e. "01/01/0001".

3.- Save the excel, and then apply your trick doing the query with HDR=NO to the modified file.

Tricky, yes, but worked, and reasonabily fast, if anyone has any alternative to this, i will be glad to hear.

Greetings.

P.D. Excuse my english, it isn't my native language.

blocks|key|987915|text|没有问题的sh4，很高兴它有助于解决混合类型的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|987916|DateTime专栏是另一个我记得在过去给我带来悲痛的动物……我们处理了一个excel文件，OleDbDataAdapter有时会将日期转换为double数据类型(显然，Excel将日期存储为double，这是自1900年1月0号以来经过的天数的编码)。|987917|解决方法是使用：|987918|OleDbConnection+mobjExcelConn+=+new+OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data+Source="+%2B+txtExcelFile.Text+%2B+@";Extended+Properties=""Excel+8.0;IMEX=1;HDR=Yes;""");

OleDbDataAdapter+mobjExcelDataAdapter+=+new+OleDbDataAdapter("Select+*+from+["+%2B+txtSheet.Text+%2B+"$]+where+[Supplier+ID]+<>+''+",+mobjExcelConn);


DateTime+dtShipStatus+=+DateTime.MinValue;
shipStatusOrig+=+excelRow["Est+Ship+Date"].ToString();+//+excelRow+is+DataRow+in+the+DataSet+via+the+OleDbDataAdapter+++++++++++++

if+(shipStatusOrig+!=+string.Empty)
{
++++//+Date+may+be+read+in+via+oledb+adapter+as+a+double
++++if+(IsNumeric(shipStatusOrig))
++++{
++++++++double+d+=+Convert.ToDouble(shipStatusOrig);
++++++++dtShipStatus+=+DateTime.FromOADate(d);

++++++++if+(DateTime.TryParse(dtShipStatus.ToString(),+out+dtShipStatus))
++++++++{
++++++++++++validDate+=+true;
++++++++++++Debug.WriteLine("{0}+converted:+",+dtShipStatus.ToString("s"));
++++++++}
++++}
++++else
++++{
++++++++if+(ValidateShipDate(shipStatusOrig))
++++++++{
++++++++++++dtShipStatus+=+DateTime.Parse(shipStatusOrig);
++++++++++++validDate+=+true;
++++++++++++Debug.WriteLine("{0}+converted:+",+dtShipStatus.ToString("s"));
++++++++}
++++++++else
++++++++{
++++++++++++validDate+=+false;
++++++++++++MessageBox.Show("Invalid+date+format+in+the+Excel+spreadsheet.\nLine+#+"+%2B+progressBar1.Value+%2B+",+the+'Ship+Status'+value+'"+%2B+shipStatusOrig+%2B+"'+is+invalid.\nDate+should+be+in+a+valid+date+time+format.\ne.g.+M/DD/YY,+M.D.Y,+YYYY-MM-DD,+etc.",+"Invaid+Ship+Status+Date");
++++++++}
++++}
...
}
++++++++public+static+Boolean+IsNumeric+(Object+Expression)
++++++++{
++++++++++++if(Expression+==+null+%7C%7C+Expression+is+DateTime)
++++++++++++++++return+false;

++++++++++++if(Expression+is+Int16+%7C%7C+Expression+is+Int32+%7C%7C+Expression+is+Int64+%7C%7C+Expression+is+Decimal+%7C%7C+Expression+is+Single+%7C%7C+Expression+is+Double+%7C%7C+Expression+is+Boolean)
++++++++++++++++return+true;

++++++++++++try
++++++++++++{
++++++++++++++++if(Expression+is+string)
++++++++++++++++++++Double.Parse(Expression+as+string);
++++++++++++++++else
+++++++++++++++++++Double.Parse(Expression.ToString());
++++++++++++++++return+true;
++++++++++++}+catch+{}+//+just+dismiss+errors+but+return+false

++++++++++++return+false;
++++++++}

++++++++public+bool+ValidateShipDate(string+shipStatus)
++++++++{
++++++++++++DateTime+startDate;
++++++++++++try
++++++++++++{
++++++++++++++++startDate+=+DateTime.Parse(shipStatus);
++++++++++++++++return+true;
++++++++++++}
++++++++++++catch
++++++++++++{
++++++++++++++++return+false;
++++++++++++}
++++++++}|code-block|syntax|javascript|987919|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

No problem sh4, glad it helps w/ the mixed type issue.

The DateTime column is whole other animal that I recall caused me grief in the past... we have one excel file we process that the OleDbDataAdapter will sometimes convert dates to a double data type (apparently Excel stores dates as doubles, which encode the number of days elapsed since January 0, 1900 ). 

The workaround was to use:

<pre><code>OleDbConnection mobjExcelConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtExcelFile.Text + @";Extended Properties=""Excel 8.0;IMEX=1;HDR=Yes;""");

OleDbDataAdapter mobjExcelDataAdapter = new OleDbDataAdapter("Select * from [" + txtSheet.Text + "$] where [Supplier ID] &lt;&gt; '' ", mobjExcelConn);


DateTime dtShipStatus = DateTime.MinValue;
shipStatusOrig = excelRow["Est Ship Date"].ToString(); // excelRow is DataRow in the DataSet via the OleDbDataAdapter 

if (shipStatusOrig != string.Empty)
{
 // Date may be read in via oledb adapter as a double
 if (IsNumeric(shipStatusOrig))
 {
 double d = Convert.ToDouble(shipStatusOrig);
 dtShipStatus = DateTime.FromOADate(d);

 if (DateTime.TryParse(dtShipStatus.ToString(), out dtShipStatus))
 {
 validDate = true;
 Debug.WriteLine("{0} converted: ", dtShipStatus.ToString("s"));
 }
 }
 else
 {
 if (ValidateShipDate(shipStatusOrig))
 {
 dtShipStatus = DateTime.Parse(shipStatusOrig);
 validDate = true;
 Debug.WriteLine("{0} converted: ", dtShipStatus.ToString("s"));
 }
 else
 {
 validDate = false;
 MessageBox.Show("Invalid date format in the Excel spreadsheet.\nLine # " + progressBar1.Value + ", the 'Ship Status' value '" + shipStatusOrig + "' is invalid.\nDate should be in a valid date time format.\ne.g. M/DD/YY, M.D.Y, YYYY-MM-DD, etc.", "Invaid Ship Status Date");
 }
 }
...
}
 public static Boolean IsNumeric (Object Expression)
 {
 if(Expression == null || Expression is DateTime)
 return false;

 if(Expression is Int16 || Expression is Int32 || Expression is Int64 || Expression is Decimal || Expression is Single || Expression is Double || Expression is Boolean)
 return true;

 try
 {
 if(Expression is string)
 Double.Parse(Expression as string);
 else
 Double.Parse(Expression.ToString());
 return true;
 } catch {} // just dismiss errors but return false

 return false;
 }

 public bool ValidateShipDate(string shipStatus)
 {
 DateTime startDate;
 try
 {
 startDate = DateTime.Parse(shipStatus);
 return true;
 }
 catch
 {
 return false;
 }
 }
</code></pre>

blocks|key|987998|text|我找到的几个论坛声称，通过将IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text添加到连接字符串中的扩展属性可以解决问题，但事实并非如此。我最终解决了这个问题，将"HDR=NO“添加到连接字符串的扩展属性中(如Brian+Wells上面所示)，这样我就可以导入混合类型。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|987999|然后，我添加了一些通用代码来根据第一行数据命名列，然后删除第一行。|988000|++++public+static+DataTable+ImportMyDataTableFromExcel(string+filePath)
++++{
++++++++DataTable+dt+=+new+DataTable();

++++++++string+fullPath+=+Path.GetFullPath(filePath);

++++++++string+connString+=
+++++++++++"Provider=Microsoft.Jet.OLEDB.4.0;"+%2B
+++++++++++"Data+Source=\""+%2B+fullPath+%2B+"\";"+%2B
+++++++++++"Extended+Properties=\"Excel+8.0;HDR=No;IMEX=1;\"";

++++++++string+sql+=+@"SELECT+*+FROM+[sheet1$]";

++++++++using+(OleDbDataAdapter+dataAdapter+=+new+OleDbDataAdapter(sql,+connString))
++++++++{
++++++++++++dataAdapter.Fill(dt);
++++++++}

++++++++dt+=+BuildHeadersFromFirstRowThenRemoveFirstRow(dt);

++++++++return+dt;
++++}

++++private+static+DataTable+BuildHeadersFromFirstRowThenRemoveFirstRow(DataTable+dt)
++++{
++++++++DataRow+firstRow+=+dt.Rows[0];

++++++++for+(int+i+=+0;+i+<+dt.Columns.Count;+i%2B%2B)
++++++++{
++++++++++++if(!string.IsNullOrWhiteSpace(firstRow[i].ToString()))+//+handle+empty+cell
++++++++++++++dt.Columns[i].ColumnName+=+firstRow[i].ToString().Trim();
++++++++}

++++++++dt.Rows.RemoveAt(0);

++++++++return+dt;
++++}|code-block|syntax|javascript|988001|entityMap^0|E|18|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|S|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

Several forums I found claim that by adding <code>IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text</code> to the Extended Properties in the connection string would fix the problem, but this was not the case. I finally solved this problem by adding "HDR=NO" to the Extended Properties in the connection string (as Brian Wells shows above) so that I could import mixed types.

I then added some generic code to name the columns after the first row of data, then remove the first row.

<pre><code> public static DataTable ImportMyDataTableFromExcel(string filePath)
 {
 DataTable dt = new DataTable();

 string fullPath = Path.GetFullPath(filePath);

 string connString =
 "Provider=Microsoft.Jet.OLEDB.4.0;" +
 "Data Source=\"" + fullPath + "\";" +
 "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"";

 string sql = @"SELECT * FROM [sheet1$]";

 using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, connString))
 {
 dataAdapter.Fill(dt);
 }

 dt = BuildHeadersFromFirstRowThenRemoveFirstRow(dt);

 return dt;
 }

 private static DataTable BuildHeadersFromFirstRowThenRemoveFirstRow(DataTable dt)
 {
 DataRow firstRow = dt.Rows[0];

 for (int i = 0; i &lt; dt.Columns.Count; i++)
 {
 if(!string.IsNullOrWhiteSpace(firstRow[i].ToString())) // handle empty cell
 dt.Columns[i].ColumnName = firstRow[i].ToString().Trim();
 }

 dt.Rows.RemoveAt(0);

 return dt;
 }
</code></pre>

I have an Excel worksheet I want to read into a datatable - all is well except for one particular column in my Excel sheet. The column, 'ProductID', is a mix of values like <code>##########</code> and <code>n#########</code>.

I tried to <a href="http://support.microsoft.com/kb/306572">let OleDB handle everything by itself automatically</a> by reading it into a dataset/datatable, but any values in 'ProductID' like <code>n######</code> are missing, ignored, and left blank. I tried manually creating my DataTable by looping through each row with a datareader, but with the exact same results.

Here's the code :

<pre class="lang-cs prettyprint-override"><code>// add the column names manually to the datatable as column_1, column_2, ...
for (colnum = 0; colnum &lt; num_columns; colnum ++){
 ds.Tables["products"].Columns.Add("column_" +colnum , System.Type.GetType("System.String")); 
}
while(myDataReader.Read()){
 // loop through each excel row adding a new respective datarow to my datatable 
 DataRow a_row = ds.Tables["products"].NewRow();
 for (col = 0; col &lt; num_columns; col ++){
 try { a_row[col] = rdr.GetString(col); }
 catch { a_row[col] = rdr.GetValue(col).ToString(); }
 }
 ds.Tables["products"].Rows.Add(a_row);
}
</code></pre>

I don't understand why it won't let me read in values like <code>n######</code>. How can I do this?

OleDB & mixed Excel datatypes : missing data

我有一个要读入datatable的Excel工作表-除了Excel工作表中的一个特定列之外，一切都很好。“ProductID”列是像##########和n#########这样的值的混合。我试图通过将其读取到dataset/datatable中来执行，但是像n######这样的'ProductID‘中的任何值都会丢失...

问OleDB和混合Excel数据类型:缺少数据
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问OleDB和混合Excel数据类型:缺少数据EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问OleDB和混合Excel数据类型:缺少数据
EN