Upload mixed rows in excel

Question

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 14-Feb-12 1:13am

visnumca123

Updated 14-Feb-12 22:25pm

v4

Add a Solution

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.

Nigam Patel 15-Feb-12 2:14am

Please clarify your question.

visnumca123 15-Feb-12 2:21am

i need to import data from excel file i have the mixed rows like numbers and strings in that above provider i'm using but i'm not getting the value of string data trr and ui7 that one capture as empty only in my dataset!

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

gyanesh37 · Answer 1 · 2015-07-27T23:10:00

Solution 2

Upload mixed rows in excel[^]

Posted 27-Jul-15 23:10pm

gyanesh37

MaulikDusara · Answer 2 · 2012-02-14T20:12:00

Solution 1

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.

Posted 14-Feb-12 20:12pm

MaulikDusara

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();
}

[no name] 16-Feb-12 5:31am

your final solution is here http://microsoft-ssis.blogspot.in/2011/06/mixed-data-types-in-excel-column.html

visnumca123 16-Feb-12 5:53am

Hi for the first 8 rows is numbers and again string also follow means again null will occur?
Eventhough i wants to convert excel to csv i need values not null then only i'm able to do that?

only thing i want to do is purcahsing dll right!

Note: Here our IT People saying that if i touch registry and modify it will spread virus through network(who are all having network access it will affect the system)

visnumca123 16-Feb-12 7:01am

But still i didn't get correct solution without dll!

[no name] 16-Feb-12 23:43pm

if columns are fixed in excel file which you are accepting for import data, just do one small change in your connection string just remove HDR=Yes; so your first row always in text format, and while reading that records just ignore 1st rec.