none
Casting Int to String with OleDb RRS feed

  • General discussion

  • Hello,

    I'm using ADO.NET to connect to an Excel spreadsheet via the OleDb provider.  I'm trying to cast/convert a column from an integer to a string so when my sql runs it'll be able to do a correct compare instead of giving me a data type mismatch error.  Does anyone know how this can be accomplished?

    I've already gone into the spreadsheet and formatted the cells as text but Excel seems to think it's smarter and knows that the text hold an int and when my query runs it passes it as an int instead of a string.  And yes, I am using the extended properties on the connection string (IMEX=1) and that doesn't work either.

    Monday, March 29, 2010 2:00 PM

All replies

  • Write your query select clause with a cast this will force the query to return a char(string).

     

    SELECT CAST ( fieldname AS varchar(x) )  from table

     

    Monday, March 29, 2010 2:15 PM
  • When I do the Cast I get the following error:

     

    IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

    Monday, March 29, 2010 2:22 PM
  • Let's see you query code.
    Monday, March 29, 2010 2:30 PM
  • Let's see you query code.
    Monday, March 29, 2010 2:30 PM
  • public static DataSet GetHCDifferences(string spreadSheetPath, bool hdr, Dictionary<string, string> dictionary)
            {
                DataSet ds = new DataSet();
                ds.Tables.Add(Data.OleDbHelper.ExecuteCommand(GetExcelConnectionString(spreadSheetPath, hdr), GetSheet1NotInSheet2(dictionary)));
                return ds;
            }
    
            private static string GetSheet1NotInSheet2(Dictionary<string, string> hcData)
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT CAST(" + hcData["Sheet1SSNColumn"] + ", varchar(9)) FROM [Sheet1$]");
                return sb.ToString();
            }

    That's the code that I'm using to pass to the OleDbCommand object.  It generates the following:

    SELECT CAST(SSN, varchar(9)) FROM [Sheet1$]

     

    Monday, March 29, 2010 2:50 PM
  • Are there hypens in the SSN column? The won;t cast to a character you'll have to remove them with a REPLACE function.

     

    Monday, March 29, 2010 2:55 PM
  • Are there hypens in the SSN column? The won't cast to a character you'll have to remove them with a REPLACE function.

     

    Monday, March 29, 2010 2:55 PM
  • No, there are no hyphens in the SSN column
    Monday, March 29, 2010 3:08 PM
  • I did notice that I was doing Cast(SSN, varchar(9)) and not Cast(SSN as varchar(9)) so I went and fixed it but I'm still getting the same error. 
    Monday, March 29, 2010 3:37 PM
  • I ran some tests and I think the problem is that the Ole/Excel does not have a CAST command. You'll have to ask an Excel guru or find what command that engine uses to cast values.

    You could use your regular query and once you have the dataset from Excel you could run a LINQ query against that to convert the int to a string column.

    Monday, March 29, 2010 4:16 PM
  • You cannot use CAST with Jet or ACE OLEDB provider. In general, SQL syntax is very limited when it comes to work with Excel files and in many cases you would need to do extra processing inside of the code, instead of using SQL statements. If Excel file is not large, you could actually retrieve all the rows from it and manipulate them inside of DataTable/DataView
    Val Mazur (MVP) http://www.xporttools.net
    Tuesday, March 30, 2010 10:07 AM
    Moderator
  • Thank you very much - this is exactly the same problem that I had!

    Are there any providers which might allow casting from an Int to a String with OleDb?

    Monday, October 18, 2010 11:32 AM