locked
Charindex() in Import/Export wizard RRS feed

  • Question

  • Hi

    Not sure if I have the right area of the forums

    I'm trying to import a spreadsheet into a SQL Server table using the Import/Export wizard. I want to process the data by removing everything after the first space so I am using a source query. The problem I have is that as soon as I introduce the charindex() function it tells me that it is an invalid query. I have also tried patindex() with the same result. The expression I am using is

    left(column,charindex(' ',column)-1)

    The expression works fine in SSMS. Does anyone know why I get this response or can anyone suggest an alternative expression to achieve the same effect?

    Thanks, Dave


    • Edited by Dave Aitch Monday, December 19, 2011 3:29 PM
    Monday, December 19, 2011 3:22 PM

Answers

  • I tried creating a package in BIDS with the same results.

    It appears that the Import/Export wizard uses the Access database engine for it's SQL dialect and so the charindex() function should be replaced by the instr() function. The left() function is supported in Access.

    • Marked as answer by Dave Aitch Wednesday, January 4, 2012 12:29 PM
    Wednesday, January 4, 2012 12:29 PM

All replies

  • Why don't you just import it as is and format it in the staging table?

    SSIS Import/Export Wizard page:

    http://www.sqlusa.com/bestpractices/ssis-wizard/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Monday, December 19, 2011 7:07 PM
    Answerer
  • Why don't you just import it as is and format it in the staging table?

    SSIS Import/Export Wizard page:

    http://www.sqlusa.com/bestpractices2008/administration/ssiswizardicon/

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER GRAND SLAM

    That certainly is possible but I suppose what I am really asking is why does charindex() (perfectly valid T-SQL) break the query in this context?
    Wednesday, December 21, 2011 11:05 AM
  • Could be some bad data in the source files - best option would be import them onto staging and then apply functions

    http://uk.linkedin.com/in/ramjaddu
    Wednesday, December 21, 2011 12:28 PM
  • Within the wizard, the query language is not T-SQL, and you probably need FINDSTRING. See http://msdn.microsoft.com/en-us/library/ms141671.aspx

     

    • Proposed as answer by Naomi N Sunday, December 25, 2011 2:44 AM
    • Marked as answer by Stephanie Lv Friday, December 30, 2011 1:37 AM
    • Unmarked as answer by Dave Aitch Wednesday, January 4, 2012 12:24 PM
    Saturday, December 24, 2011 10:26 PM
  • Hi Steve

    Thanks for the response but, unless I am missing something, FINDSTRING doesn't work. I tried various other SSIS expressions and they all came back as an invalid query. Any further thoughts?

    Wednesday, January 4, 2012 11:30 AM
  • I tried creating a package in BIDS with the same results.

    It appears that the Import/Export wizard uses the Access database engine for it's SQL dialect and so the charindex() function should be replaced by the instr() function. The left() function is supported in Access.

    • Marked as answer by Dave Aitch Wednesday, January 4, 2012 12:29 PM
    Wednesday, January 4, 2012 12:29 PM