We help IT Professionals succeed at work.
Get Started

Datatype Issue importing from Excel with Ado.net

ltarrant
ltarrant asked
on
995 Views
Last Modified: 2012-08-13
Hi,

I have the following code below to import from an excel file into a datatable. The problem is one of the columns in my excel file contains mixed content. The first few rows contain numbers and then the majority contain text. When i tried to import in .net it entered all numbers but entered all rows that contained text as null.

I have read about the downfalls of establishing datatypes when importing from excel. I have also seen microsoft recommendations of adding IMEX setting as seen below. Which seemed to work for a time.

However, I then started getting other problems with "Table in unexpected format". This was solved by saving as a CSV file and then saving back to XLS. However, after doing that it seemed to revert to the old problem of entering null for text values in the particular column.

As you can see in my code below I have set the IMEX setting on the connection string. I have been unable to implement the registry changes as the site is hosted on a shared server and the hosts won't make these change. I believe the problem is that only the first 8 rows are being evaulated to establish the datatype to use and in the case of the excel sheet the first 14 row are numbers and the majority of the rest text.

Is there any other way round this? Could I use a loop with a  datareader to fill a table or would the same problem occur.?

Any help would be much appreciated.

public DataTable getXlsSheet(string file, string sql)
        {
            string cString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;IMEX=1;HDR=No';";

            OleDbConnection myConnection = new OleDbConnection(cString);

            myConnection.Open();

            OleDbCommand myCommand = new OleDbCommand(sql, myConnection);

            OleDbDataAdapter myAdapter = new OleDbDataAdapter(myCommand);

            DataTable myTable = new DataTable();

            myAdapter.Fill(myTable);

            myConnection.Close();

            return myTable;
        }
Comment
Watch Question
Applications and Integrations Consultan
Commented:
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE