Casting value while reading from Excel file.

Question

0.00/5 (No votes)

See more:

Hi,
I want to cast value while reading it from excel use oldDB. my code is below.

DataTable dtOleDB = new DataTable();
        string constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + xlsFileName + ";    Extended Properties='Excel 12.0 Xml;HDR=YES;'";
        OleDbConnection oledbCon = new OleDbConnection(constr);
        string cmdText = "SELECT Cast(ExceptionID as varchar), [FeatureOptionID], ExceptionTypeID, ExceptionFeatureOptionID, ";
        cmdText += "AlternateDescription FROM [Exceptions$]";
        OleDbDataAdapter oleDA = new OleDbDataAdapter(cmdText, oledbCon);
        oleDA.Fill(dtOleDB);

If using without cast its working fine. But I want to cast it in varchar format like do in sql server how can I achieve this functionality here.

thanks

Posted 23-Jan-14 2:02am

ShivKrSingh

Updated 12-Jan-21 20:34pm

Add a Solution

Comments

Sampath Lokuge 23-Jan-14 8:08am

why do you need that ?

ShivKrSingh 23-Jan-14 23:02pm

I'm using excel file in my application and want to check if someone enter text in ID field it should be validate. but when we read it text is not displaying if it is not in number format. While value exist in excel file. if I format that column as text in excel sheet it goes well. But this file generate at runtime and destroy. so I want a solution that can help me conversion. without any manual change in excel file.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Answer 1 · 2014-01-23T02:16:00

Solution 2

Try this, it might help you.
cast-a-data-type-with-the-odbc-excel-driver[^]

Posted 23-Jan-14 2:16am

Karthik_Mahalingam

TnTinMn · Answer 2 · 2014-01-23T04:02:00

The Microsoft.ACE.OLEDB provider being the "Microsoft Access Database Engine" uses MS Access SQL syntax.

You will need to use the type conversion functions available in Access[^].

You should also be checking for null values before attempting the conversion.

Cast(ExceptionID as varchar) would be:

IIf(IsNull([ExceptionID]), Null, CStr([ExceptionID])) As strExceptionID

In this example, a DBNull will be returned if the Excel cell is empty. If you want to return an empty String then it would be:
IIf(IsNull([ExceptionID]), '', CStr([ExceptionID])) As strExceptionID

Abhinav S · Answer 3 · 2014-01-23T02:12:00

Solution 1

You cannot use CAST with OLEDb provider.

Posted 23-Jan-14 2:12am

Abhinav S

Comments

ShivKrSingh 23-Jan-14 23:03pm

what is the alternative of this while using with excel.

Abhinav S 23-Jan-14 23:21pm

Fetch the data and then convert using C# code.