Solved

String Array question

Posted on 2014-02-23
8
234 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

623 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