Click here to Skip to main content
15,887,297 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying to filter out the data and create new excel file out of the excel files collection I have in my directory. Currently I am using OLEDB command and I am running into error data type mismatch. I would like to know if it could be done using epplus or not and if so please share the code.

What I have tried:

using (var xlConn = new OleDbConnection(cs))
                {
                    xlConn.Open();
                    dtXlSchema = xlConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                    for (int i = 0; i < dtXlSchema.Rows.Count; i++)
                    {
                        string sTableName = dtXlSchema.Rows[i]["Table_Name"].ToString();
                        string query ="Select [cs-uri-stem] from [" + sTableName + "] where ([cs-uri-stem] like '%" + kw + "%' and [sc-status] = '" + stat + "') Order by [cs-uri-stem]";
                            
                        
                        using (var cmd = new OleDbCommand(query, xlConn))
                        {
                            var rdr = cmd.ExecuteReader();

                            while (rdr.Read())
                            {
                                var row = dt.NewRow();
                                row["cs-uri-stem"] = rdr["cs-uri-stem"].ToString();
                                dtx.Rows.Add(row);
                            }
                        }
                    }
Posted
Updated 3-Jan-19 23:36pm
Comments
Richard MacCutchan 20-Nov-18 9:53am    
Rather than trying some different library (which will likely raise the same exception), you should diagnose and correct the code that is wrong.
istudent 20-Nov-18 10:01am    
But that's all the code is. Reading data from one excel file. Here is connection properties key="OleDBProvider" value= "Microsoft.ACE.OLEDB.12.0"
key="ExtendedProperties" value="'Excel 12.0;HDR=Yes;IMEX=1'"
istudent 20-Nov-18 10:04am    
okay I change the extended properties value="'Excel 8.0;HDR=Yes;IMEX=1'". And its worked. Error message was miss leading. However I would like to learn to do with other libraries in simple fashion.

1 solution

Quote:
I am trying to filter out the data and create new excel file out of the excel files collection I have in my directory. Currently I am using OLEDB command and I am running into error data type mismatch. I would like to know if it could be done using epplus or not and if so please share the code.


As to the data type mismatch error message...
There's two reasons which may cause such of error:
1. A common reason is when Excel cannot convert data between data types...
See: Type mismatch (Error 13) | Microsoft Docs[^]
2. You're using IMEX=1 in ExtendendProperties, which forces OledDb provider to treats Excel data as a string. See: Microsoft ACE OLEDB 12.0 Connection Strings - ConnectionStrings.com[^]
So, when you're trying to set value of cell/row which is differ than string, you'll see such of error.

Conclusion:
Use debugger to find out the line which causes error to be able to fix it!

As to the diffent frameworks/providers (epplus, etc.)...
Yes, you can use it, but you have to be completely sure that it would provide more advantages than disadvantages.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900