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, Feb 09 '15 at 07:24

score 1 · Answer 1 · edited May 23 '17 at 12:32

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 '17 at 12:32

Community

1
1

answered Feb 09 '15 at 07:34

kassi

358
1
3
10

Make a breakpoint after the adapter fills the columns of the dataset, what are the types of the empty columns? – kassi Feb 09 '15 at 08:08
Name:Double ,another column type- is String – user2545071 Feb 09 '15 at 08:11
How to change data type of column to String? – user2545071 Feb 09 '15 at 08:17

Cannot parse some excel column

1 Answers1