Solved

String Array question

Posted on 2014-02-23
8
222 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
Comment Utility
What data type are you using for your string column when you are reading it using OdbcDataReader?
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 10

Expert Comment

by:ericwong27
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

763 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

11 Experts available now in Live!

Get 1:1 Help Now