Solved

String Array question

Posted on 2014-02-23
8
226 Views
Last Modified: 2014-02-26
Hello,
In my c# application I have a string array defined  as:

string[,] TranArr = new string[999,20]

I am reading data into this array from an excel spreadsheet (MS Excel 2007 xlsx)  via an OdbcDataReader. One spreadsheet cell value is approx. 400 characters in length but when I read it into my array  it is truncated to 245 characters. Any idea why this might be happening ?    

regards
Pat
0
Comment
Question by:pclarke7
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 39881378
What data type are you using for your string column when you are reading it using OdbcDataReader?
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39881732
I seem to remember about a limit of the number of characters in excel (many years ago - and I may be remembering incorrectly).  My guess is the reader has a built in limit or or is an out of date version that shouldn't be used with newer Excel versions.
I'd try a different reader than the OdbcDataReader reader you say you are using.
0
 
LVL 10

Accepted Solution

by:
ericwong27 earned 500 total points
ID: 39881837
Could you please try following code?

using System;
using System.Data.OleDb;

namespace ConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            string con = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                         @"Data Source=c:\temp\Book1.xlsx;" +
                         @"Extended Properties='Excel 8.0;HDR=Yes;'";

            using(OleDbConnection connection = new OleDbConnection(con))
            {
                connection.Open();
                OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);

                var TranArr = new string[999, 20];
                int i = 0;

                using(OleDbDataReader dr = command.ExecuteReader())
                {
                     while(dr.Read())
                     {
                         TranArr[i, 1] = dr[0].ToString();
                         TranArr[i, 2] = dr[1].ToString();

                         Console.WriteLine(TranArr[i, 1]);
                         Console.WriteLine(TranArr[i, 2]);
                     }
                }
            }

            Console.ReadKey();
        }
    }
}

Open in new window

0
 

Author Comment

by:pclarke7
ID: 39887270
Thanks for all of your comments. Yes there seems to be a 255 character limit on excel cells and everything exceeding 255 is truncated.

Eric
I tried your sample code on my PC and it worked perfectly. Then I copied the code and test xlsx file to my laptop, where I am developing my application, and It did not work. Both are windows 7 but the PC is 64bit o/s whilst the laptop is 32 bit.

Interestingly when run on the 32bit laptop the fields exceeding 255 characters are returned as null rather than truncated.

Any idea why this works on 64bit o/s and not 32 ?

regards
Pat  

 
PS - I know that registery entry "TypeGuessRows" attempts to guess the data type after a default 8 rows. I have set this to 0 on both machines.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 10

Expert Comment

by:ericwong27
ID: 39887746
Unfortunately, I didn't have 32 bit machine to test.

Have you upgrade the Microsoft Access Database Engine 2010 Redistributable to SP2?
 
http://www.microsoft.com/en-sg/download/details.aspx?id=13255
0
 

Author Comment

by:pclarke7
ID: 39888217
Hi Eric,
just upgraded Microsoft Access Database Engine 2010 Redistributable to SP2 but still receiving null for any value greater than 255 characters

regards
Pat
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39888379
In Excel 2010 you ought not to have  problems.
http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx

My guess this is something to do with the provider you are using.
0
 

Author Closing Comment

by:pclarke7
ID: 39890490
Thanks Eric,
whilst this was not the final solution , it put me on the right track. The answer to this 255 character excel restriction is as follows:

instead of
                         TranArr[i, 1] = dr[0].ToString();
                         TranArr[i, 2] = dr[1].ToString();

Use                        
                        TranArr[i, 1] = dr,GetString(0);
                        TranArr[i, 2] = dr,GetString(1);

This will retrieve the full content of the cell even when it exceed 255 characters. However if the cell contains a null then you will get the following error "System.IsDBNull". To get around this do the following:

if (!dr.IsDBNull)
{
     TranArr[i, 1] = dr,GetString(0);
}
else
TranArr[i, 1] =""


Works like a dream. Hope this helps someone struggling with this same problem.

Pat
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now