Solved

OLE Grabbing Excel Strings is cutting short

Posted on 2003-12-02
11
389 Views
Last Modified: 2010-04-16
I am grabbing strings out of an excel spreadsheet and I thought all was working fine until I realized that it is pulling the first part of the text but not the full string. Anyone know how using C-sharp and an OLE connection to make sure it has grabbed all of the text out of a cell?
0
Comment
Question by:jj819430
  • 6
  • 5
11 Comments
 
LVL 22

Expert Comment

by:_TAD_
ID: 9859415


Where is it breaking apart the string?  At a Space, a specific number of characters, at a specific cell width, at special characters?


0
 
LVL 7

Author Comment

by:jj819430
ID: 9859453
255 chars is the length of the string it grabs. The total is around 4000 chars.
I can't seem to find any prewritten functionality to check if it is something like EndOfFile (when dealing with a regular .txt file)
Any Ideas? I am thinking if there is a EOF or some other marker in the cell I can just concantenate until it has hit that mark. Otherwise I am not sure.

Any help is much appreciated.
0
 
LVL 7

Author Comment

by:jj819430
ID: 9859461
I also am not sure how to get another string to start reading at char 256
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 9859890


That is a big problem with Crystal reports as well (any version prior to Version 9).  But that is another matter...

As for your issue, I have not encountered it before... You may need to create another variable that you pass along in your query (which will be blank 90% of the time).  This variable will contain the characters from 256 to 512 (you may even want to create a additional variables to handle up to 1,000 characters).

At any rate, as mentioned above you'll need to parse your string out into segments and then reassemble them back inside your C# code.

If you are using standard SQL commands then you command should look something like:

Select col1, col2, col3, col4, decode(length(col4)>255, true, col5=mid(col4, 256,256), col5='') from tableName where Criteria= something

0
 
LVL 22

Expert Comment

by:_TAD_
ID: 9859919


Oh... you may also want to check the data type you are using...

I'm assuming a string, but you may want to double check to see what the OLE has specified.  It may be using some kind of data type specific to EXCEL that obly uses 254 characters.  You may be able to change a text data type to a long text or something....
0
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

 
LVL 7

Author Comment

by:jj819430
ID: 9860195
well, I am not sure how to do what you are saying.

Basically I have the field in excel named "Buyer_Field";

it contains text of up to 4000 charachters.

I use the OLE connection and do the following select command
"Select * FROM [Buyer_Field]"

I then put this into a dataAdapter
DataSet
then try and pull it out, and it is still cutting off at 255. How do I implement what you are saying in this sense.

Do I switch the select statement around? if so, to what? The Decode is not making sense to me in how it works.

Thanks very much
0
 
LVL 7

Author Comment

by:jj819430
ID: 9860328
I thought the Decode was used for Joins between tables or cells in a database.
0
 
LVL 7

Author Comment

by:jj819430
ID: 9860402
OleDbCommand PressRelease = new OleDbCommand("SELECT * FROM [Description_Buyer]",objConn);
OleDbDataAdapter PressReleaseAdapter = new OleDbDataAdapter();
PressReleaseAdapter.SelectCommand = PressRelease;
DataSet PressReleaseDS = new DataSet();
PressReleaseAdapter.Fill(PressReleaseDS,"XLData");
PressReleaseDataGrid.DataSource = PressReleaseDS.Tables[0].DefaultView;
                  PressReleaseDataGrid.DataBind();

This is the code to bring the string out of the cell and into a DataGrid just so I can see it.
thought you might be able to see something stupid I did or some little mistake. But this is all pretty simple and straight forward.
0
 
LVL 22

Expert Comment

by:_TAD_
ID: 9860855


Decode is just like an IF statement in SQL

DECODE(Something, condition, if true, if false)


0
 
LVL 7

Author Comment

by:jj819430
ID: 9861220
I really am not sure how to implement what you are talking about. I posted a similar question in the .NET section. I will give you the points if you show me a snippet on how that works on an example. I can send you an Excel file if you want with what is giving me the problem.
0
 
LVL 22

Accepted Solution

by:
_TAD_ earned 500 total points
ID: 9862473
Using your example from above:

//obviously you'll have to change the column names from col1, col2, etc to something more meaningful

OleDbCommand PressRelease = new OleDbCommand("SELECT col1, col2, col3, Decode(Length(col3)>255,true,substr(col3,255,255),'') FROM [Description_Buyer]",objConn);



OleDbDataAdapter PressReleaseAdapter = new OleDbDataAdapter();
PressReleaseAdapter.SelectCommand = PressRelease;
DataSet PressReleaseDS = new DataSet();
PressReleaseAdapter.Fill(PressReleaseDS,"XLData");
PressReleaseDataGrid.DataSource = PressReleaseDS.Tables[0].DefaultView;
               PressReleaseDataGrid.DataBind();
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

758 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

21 Experts available now in Live!

Get 1:1 Help Now