Solved

String Array question

Posted on 2014-02-23
8
233 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
[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
  • 2
  • 2
  • +1
8 Comments
 
LVL 25

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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

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
 
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

Independent Software Vendors: 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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
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…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

710 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