Solved

OleDB Read from Excel with Cells > 256 Chars?

Posted on 2011-03-14
3
1,143 Views
Last Modified: 2012-05-11
Hi,

i am trying to read Data from an Excel spreadsheet. My code works great, but it can not read cells with more than 256 chars.
Is there a solution for it?


OleDbConnection DBConnection;
OleDbCommand DBCommand;

DBConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + @"c:/temp/lpa.xls" + ";"
                + "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"");
string SQLString = @"Select [F1],[F2],[F3],[F4],[F5],[F23] FROM [Sheet1$Print_Area]";
DBCommand = new OleDbCommand(SQLString, DBConnection);

           try
            {
                DBConnection.Open();
                DataTable ExcelData = new DataTable();
                ExcelData.Columns.Add("Item", typeof(int));
                ExcelData.Columns.Add("Description", typeof(string));
                ExcelData.Columns.Add("Station", typeof(string));
                ExcelData.Columns.Add("Location", typeof(string));
                ExcelData.Columns.Add("SampleSize", typeof(string));
                ExcelData.Columns.Add("ReactionPlan", typeof(string));
                DBReader = DBCommand.ExecuteReader();
                int num;
                while(DBReader.Read())
                {
                    bool isNum = int.TryParse(DBReader[0].ToString(), out num);
                    if (isNum)
                    {
                        ExcelData.Rows.Add(num, valid(DBReader, 1), valid(DBReader, 2), valid(DBReader, 3), valid(DBReader,              4), valid(DBReader, 5));
                }
                   DB.Connection.Close();
                 }
                           }
            catch (System.Exception ex)
            {
                DBConnection.Close();
                if (DBReader != null)
                {
                    DBReader.Close();
                }


                MessageBox.Show(ex.Message.ToString());

            }
            finally
            {
                MessageBox.Show("Data Inserted");
            }

Open in new window

0
Comment
Question by:ktpoitm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 12

Expert Comment

by:Mamtha1982
ID: 35136365
Try using ODBC-driver instead of Oledb
0
 
LVL 1

Author Comment

by:ktpoitm
ID: 35138085
The ODBC-driver did not work since it has a bug with the First Column Name.

I found a registry key that allowed me to change the row scan value from 8 to 30. It sees the Cells as Memos now and reads all the chars.

Thanks anyways for you help.
0
 
LVL 3

Accepted Solution

by:
Sebastian_OH earned 500 total points
ID: 35138273
Change the following Reg key

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel]

 REG_DWORD "TypeGuessRows".

That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question