Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

String Array question

Posted on 2014-02-23
8
Medium Priority
?
235 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 1500 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
How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

688 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