Excel cell format

Hi Expert,
I have problem to read one column whose format is text. On the spreadsheet I see
all values, but when I read it by using oledb some line of the same column returned
null. Then I check its format. it is text and possibly has 'apostrophy' infront. Then I add
" ' " in front of the cell, then I can read it.
How can I use excel macro to check if the cells in this column named has " ' " if not ,
my macro will add " '". I am new to macro excel.
Could you please help me out?
Thx,'
JT
OleDbCommand cmd3 = new OleDbCommand("select * from [" + txtSheetName.Text + "$]", con);
            using (rd = cmd3.ExecuteReader())
            {
                int index = 0;
 
                while (rd.Read())
                {
                    index++;
 
                    if (index < DataRowIndex - 1)
                    {
                        continue;
                    }
                    else if (index == int.Parse(txtEndedData.Text.ToString()) - 1)
                    {
                        goto closing;
 
                    }
 
                    string countryId = null;
                    if (countryName != null)
                    {
                        countryId = valid(rd, CountryNameIndex);
                        //countryId = rd[CountryNameIndex].ToString();
                    }
                    string Destination = null;
                    Destination = valid(rd, DestIndex);
                    if (Destination.Equals("")) continue;
                    string countryCode = null;
                    if (DNCountry != null)
                        countryCode = valid(rd, DNCountryIndex);
                    //countryCode = rd[DNCountryIndex].ToString();
                    string cityCode = null;
                    cityCode = valid(rd, DNAreaIndex);
                    string Rate = null;

Open in new window

sheetname-err.xls
jtran007Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
Usually, I try to use IMEX=1 in the OLEDB connection string, when working with Excel files.

IMEX=1, of course!
http://blogs.acceleration.net/ryan/archive/2005/01/11/477.aspx
0
jtran007Author Commented:
Hi Expert,

After adding IMEX=1, I receive this error message "could not find installable ISAM".
Do you what it means?
Thx,
JT
0
Alexandre SimõesManager / Technology SpecialistCommented:
Hi,

have a good look at this: http://support.microsoft.com/kb/257819


Cheers,
Alex
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Bob LearnedCommented:
That error just means that you have the connection string incorrect.  That article pointed to connectionstrings.com, which has the IMEX=1 in the Extended Properties:

http://www.connectionstrings.com/excel

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
0
jtran007Author Commented:
Hi expert,

I checked everything , it seems ok  but i still have the error.

Also is there limit of charaters in the Excel cell, if yes, how can I increase it since
some of my cell contains more than 255  characters.

Thx,
JT
0
Alexandre SimõesManager / Technology SpecialistCommented:

Under [Worksheet and workbook specifications] you can see Excel 2003 cell limit (255 chars)
http://office.microsoft.com/en-us/excel/HP051992911033.aspx

Here's the same thing but to Office 2007 (same limitation)
http://office.microsoft.com/en-us/excel/HP100738491033.aspx


So this is a limitation of Excel. Another link:
http://support.microsoft.com/kb/213841
0
jtran007Author Commented:
Hi expert,

AlexCode: thanks.

I use "using Microsoft.Office.Tools.Excel; and using System.Data.OleDb;" to access Excel, but I still have
""could not find installable ISAM" . Do I miss something?

And my connection string is:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\xuan\\My Documents\\ex\\codeerr.xls;Extended Properties=Excel 8.0; HDR=YES; IMEX=1"      


Thx,
JT
0
Alexandre SimõesManager / Technology SpecialistCommented:
Sorry mate, I'm finding it difficult to help you on that one.
Didn't the microsoft support link I gave you help in any way?
0
jtran007Author Commented:
Hi Expert,

The support site that you told me did not help since whatever they recommend I did.
However I still have this error "could not find installable ISAM".

Thanks,
JT
0
Bob LearnedCommented:
What does your connection string look like?
0
jtran007Author Commented:
Hi expert,

Please read my attached connection string in previous conversation.


And my connection string is:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\xuan\\My Documents\\ex\\codeerr.xls;Extended Properties=Excel 8.0; HDR=YES; IMEX=1"      
Thx,
JT
0
mastooCommented:
I'm not sure if they're required but I notice you're missing some quotes in that string.  Try some double quote around that last part

Extended Properties=""Excel 8.0; HDR=YES; IMEX=1"""
0
jtran007Author Commented:
Hi expert,
They are not required. Also if I add them in, the compiler complains.
Ths,
JT
0
Bob LearnedCommented:
They are required, otherwise the parser doesn't know how to combine the values together for the Extended Properties.  If the compiler complains, you don't have enough double quotes.  "Cannot find installable ISAM" means that you didn't create a valid connection string--period.
0
Bob LearnedCommented:
C# escapes quoted strings differently than VB.NET does:

            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\xuan\\My Documents\\ex\\codeerr.xls;Extended Properties=\"Excel 8.0; HDR=YES; IMEX=1\"";
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.