Cannot parse some excel column

Question

Good day!

I try to open and parse excel file into DataSet.

So, i use OleDbConnection:

if (_filePath.Substring(_filePath.LastIndexOf('.')).ToLower() == ".xlsx")
            //                 strConn =  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
            //                    + _filePath + ";Extended Properties=\"Excel  12.0;HDR=" + HDR + ";IMEX=0\"";
             strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                    + _filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=" + HDR + ";IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text;\"";

        //   strConn="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + _filePath + ";Extended Properties=Excel 12.0;";
        else
            strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + _filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=1\"";

But some column are empty! The next column parses well (it with same data).

Can you tell me how to fix it?

Then i fill Dataset:

 OleDbConnection conn = new OleDbConnection(strConn);


            System.Data.DataSet dtSet;
            System.Data.OleDb.OleDbDataAdapter oleCommand;

            oleCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName + "]", conn);
            oleCommand.TableMappings.Add("Table", sheetName);
            dtSet = new System.Data.DataSet();

            oleCommand.Fill(dtSet);
            oleCommand.Dispose();
            conn.Close();
            return dtSet.Tables[0];

But, some columns are empty!

May be, it happens because excel file has format:

 Cell1--------------|Value1------------|
 Cell2---|Cell3-----|Value2---|Value4--|

So, dataset fill columns :

Cell1---|-------|--Value1------|-----|
Cell2---|Cell3--|---Empty(!)---|Value4|

So, i need to get Empty(!) column.

About invalid data at column.

I copy and paste this column at right column- and it works!

But,i should use last format, not mine.

HDR="NO";

Paste code with actual parsing(whatever You mean by this word). You've just provided the code creating ConnectionString. — MajkeloDev
– MajkeloDev, Commented Feb 9, 2015 at 7:24

Community · Accepted Answer · 2017-05-23 12:32:08Z

1

Maybe you ran into this error:

OleDB & mixed Excel datatypes : missing data

What's the value of 'HDR'? Take a look at the Datatypes of the Columns, maybe they are mixed.

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Feb 9, 2015 at 7:34

kassi

3681 gold badge3 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kassi Over a year ago

Make a breakpoint after the adapter fills the columns of the dataset, what are the types of the empty columns?

user2545071 Over a year ago

Name:Double ,another column type- is String

user2545071 Over a year ago

How to change data type of column to String?

Collectives™ on Stack Overflow

Cannot parse some excel column

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related