Click here to Skip to main content
15,917,971 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi dude,

I have a problem like mixed rows(Like numbers and string) in same column, while uploading time only numbers taking place string taking as null(Empty).Below is the Provider's i written for mixed rows getting from application side


C#
                            //if (strFileExtension.Trim() == ".xls")
                            //{
                            //    //ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FILENAME + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;MAXSCANROWS=0;\"";
                            //   ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FILENAME + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=Yes;TypeGuessRows=0;ImportMixedTypes=Text\"";  
                            //    //ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FILENAME + ";Extended Properties=Excel 8.0;";
                            //}
                            //else if (strFileExtension.Trim() == ".xlsx")
                            //{
                            //    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FILENAME + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=Yes;TypeGuessRows=0;ImportMixedTypes=Text;\"";
                            //}

if (strFileExtension == ".xls")
                           {
                               ConnectionString = "Provider=Microsoft.ACE.OLEDB.8.0;Data Source=" + FILENAME + @";Extended Properties=" + Convert.ToChar(34).ToString() + @"Excel 8.0;Imex=1;HDR=Yes;" + Convert.ToChar(34).ToString();
                           }
                           else if (strFileExtension == ".xlsx")
                           {
                               ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FILENAME + @";Extended Properties=" + Convert.ToChar(34).ToString() + @"Excel 12.0;Imex=1;HDR=Yes;" + Convert.ToChar(34).ToString();
                           }




Please help me to resolve this!!!
Posted
Updated 14-Feb-12 22:25pm
v4
Comments
Herman<T>.Instance 14-Feb-12 11:06am    
I believe this is a known issue. Excel looks at the first cell with data in a column and that type is used for the complete column.
visnumca123 15-Feb-12 1:56am    
Yes its know issue but Please give me some example! how to rectify this!i used imex=1 also!
ZurdoDev 14-Feb-12 13:15pm    
I can't understand what you are saying. Please clarify.
visnumca123 15-Feb-12 2:00am    
From one excel file i want to load data in dataset for that i used that provider given above when i upload mixed rows like mentioned above the numbers column only taking text columns taking as null(Empty) to avoid that issue in code side we used that Imex=1 but here every thing i used but still i'm getting null what i wants to do to rectify that?
ZurdoDev 15-Feb-12 8:04am    
I am sorry. I still do not understand what you are saying.

Hello,

Issue: When we are using an xls file as a data source using any provider. The used provider is define datatype of any column based on first 5 to 8 rows's data. so in you data it is considering numeric data type and your alphanumeric data is being null.

Resolution: to set any column to text you need to set an order on column.

this will work.
 
Share this answer
 
Comments
visnumca123 15-Feb-12 2:19am    
ok if i reset that column in to text the point values like 10.12 and 2.11 and all taking as 10.1 and 2.1 but i need original name of that how to resolve that also!
The above is used for 64 bit Ace provider for .XLSX also!
[no name] 15-Feb-12 2:35am    
Simply try add one space before you column's data and check
visnumca123 15-Feb-12 2:42am    
if its one or two rows its ok but i have nearly 10,000 records like that!all are mixed!what i wants to do now?
[no name] 16-Feb-12 1:10am    
show me your code what you done so, i can guide you proper.
visnumca123 16-Feb-12 4:09am    
if (strFileExtension == ".xls")
{
ConnectionString = "Provider=Microsoft.ACE.OLEDB.8.0;Data Source=" + FILENAME + @";Extended Properties=" + Convert.ToChar(34).ToString() + @"Excel 8.0;Imex=1;HDR=Yes;" + Convert.ToChar(34).ToString();
}
else if (strFileExtension == ".xlsx")
{
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FILENAME + @";Extended Properties=" + Convert.ToChar(34).ToString() + @"Excel 12.0;Imex=1;HDR=Yes;" + Convert.ToChar(34).ToString();
}
 
Share this answer
 

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