Post reply

Import from Excel 2007 using SQL Command using cast/convert

  • January 24, 2011 at 12:36 pm

    #233223

    I am attempting to import data from two columns in an excel spreadsheet. The Excel Source defines my 'Group Number' column as a floating point DT_R8. I would like it to DT_WSTR. Some of the groups numbers are purely numeric and some have alpha charcters in the (i.e. 12AW4). If it stays as data type float, then the ones with alpha characters will get read in as NULL.

    I tried using a SQL Command:

    select

    column1,

    convert(nvarchar(50), [Group Number])

    from [Sheet1$]

    and I get an error from SSIS that no column information was returned. I even tried a 'cast([Group Number] as nvarchar(50)) as column2' and would get an Unknown query failure.

    I've tried the IMEX=1 in the connection string.

    The only thing that has worked was to modify the Excel spreadsheet group number column to 'Text' instead of Custom.

    Does anyone have some insight on how I can accomplish this without having to modify the spreadsheet directly?

  • January 27, 2011 at 8:28 am

    #1279113

    What happens when you use IMEX=1 ?

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • January 27, 2011 at 10:01 am

    #1279185

    The IMEX=1 doesn't appear to do anything. I've seen other posts where people said this setting in the connection string worked perfectly for a 2003 excel spreadsheet, but not a 2007 excel spreadsheet.

    Unless I'm not understanding the true purpose of this flag. The column I read in from excel has some numic only and some alphanumeric. The Excel Source wants to define this column as DT_R8 and gives me a validation error "Output column "my column" on error output has properties that do not match the properties of its corresponding data source column" if I attempt to change the data type to DT_WSTR. It will not allow me to change the data type in the error output column.

  • January 27, 2011 at 1:50 pm

    #1279367

    You can't use the CAST and CONVERT commands in the SQL query to Excel, as the provider does not know those keywords. You can try FORMAT though (the Excel function).

    IMEX=1 will tell the JET provider that if intermixed datatypes are found, the resulting data type should be string. However, it only scans the first 8 rows by default. You should change the registry setting for TYPEGUESSROWS to 0, which basically tells the provider to scan the whole column.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 27, 2011 at 2:13 pm

    #1279390

    Mike,

    The driver is sampling the first n number of rows to determine what would be appropriate type for the column. As you understand the Excel is not true database and the driver is trying its best to present data as relational source. Now when you specify IMEX=1 setting, it should threat all columns as text . When you specify it , don't you get all columns as text? Have you tried refreshing the columns in the Excel source component after changing the setting?

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • January 27, 2011 at 3:35 pm

    #1279438

    Koen pointed something out that I will look at. It appears to be using an ACE driver to interface with Excel rather than JET. Maybe that is why the IMEX setting doesn't appear to make a difference.

  • February 3, 2011 at 1:19 pm

    #1282120

    I've seen this error before, and I think it results from failing to alias all columns you return with T-SQL. The query you posted didn't alias the results. Try that, then change your SSIS to use the alias name instead.

    Rich

  • February 8, 2011 at 7:52 am

    #1283692

    Sorry it took so long to reply to your suggestions. I got pulled off onto another assignment. Koen hit it on the head with the TYPEGUESSROWS setting. The first 8 rows in my sheet were all numbers, so SSIS was determined to make this field a float. I changed this setting in the registry and like magic, SSIS updated the metadata for the Excel connection and it is now a string.

    Thanks for the help.

  • February 8, 2011 at 11:18 am

    #1283897

    mike.frederick (2/8/2011)

    Sorry it took so long to reply to your suggestions. I got pulled off onto another assignment. Koen hit it on the head with the TYPEGUESSROWS setting. The first 8 rows in my sheet were all numbers, so SSIS was determined to make this field a float. I changed this setting in the registry and like magic, SSIS updated the metadata for the Excel connection and it is now a string.

    Thanks for the help.

    Allright, glad that you could solve the issue.

    And thanks for posting back!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply