December 19, 2011 at 5:47 am
Hi
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
December 20, 2011 at 2:59 pm
Just a stab in the dark - what data type is the column being cast to? If it's text (not varchar or char), the left function won't work.
December 20, 2011 at 3:14 pm
Can you import the data into a staging table, do your data cleanup, and then insert the data into your intended target table?
That's what I've always done when inserting data from spreadsheets. Just make sure the columns in your staging table have the data types the way you want them. That wizard will sometimes pick some funky ones.
December 21, 2011 at 2:51 am
nellisjp (12/20/2011)
Just a stab in the dark - what data type is the column being cast to? If it's text (not varchar or char), the left function won't work.
The data type is varchar. If I replace the charindex() with a fixed value then the expression works fine.
December 21, 2011 at 3:04 am
bwoulfe (12/20/2011)
Can you import the data into a staging table, do your data cleanup, and then insert the data into your intended target table?That's what I've always done when inserting data from spreadsheets. Just make sure the columns in your staging table have the data types the way you want them. That wizard will sometimes pick some funky ones.
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?
January 4, 2012 at 5:37 am
I've found the answer to this.
It appears that the Import/Export wizard uses the Access database engine and so the Access dialect of SQL has to be used. This means that instr() has to be used instead of charindex(). The left() function is supported in Access.
Just thought I would post this for completeness.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply