Importing excel data into SQL Server

• sj999

  SSC Veteran

  Points: 257

  More actions

  September 11, 2012 at 5:03 am

  #281428

  I have a table imported from excel

  It has three columns, fname, lname, refno, all three of varchar (255)

  There are some undesired characters in them. I need to get rid of these, ie retain ONLY alphabetic and I need a simple way of doing it. Any takers?

• Brandie Tarvin

  SSC Guru

  Points: 173083

  More actions

  September 11, 2012 at 5:11 am

  #1536159

  SSIS is your best friend for this type of thing. You can actually run a Data Flow task and do a Derived Column Transformation (use a REPLACE()) to get rid of all the weird non-alpha characters.

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• guerillaunit

  SSCarpal Tunnel

  Points: 4249

  More actions

  September 11, 2012 at 8:55 am

  #1536308

  Try this:

  CREATE FUNCTION dbo.testfunction (@input varchar(500))

  RETURNS varchar(500)

  BEGIN

  DECLARE @output AS varchar(500)

  DECLARE @i AS int

  SET @i = 1

  SET @output = ''

  WHILE @i <= LEN(@input)

  BEGIN

  IF ASCII(SUBSTRING(@input, @i, 1)) BETWEEN 65 AND 90 OR (ASCII(SUBSTRING(@input, @i, 1)) BETWEEN 97 AND 122)

  SET @output = @output + SUBSTRING(@input, @i, 1)

  ELSE

  SET @output = @output + ' '

  SET @i = @i + 1

  END

  RETURN @output

  END

  Above creates a custom function.

  Then you do this

  SELECT dbo.testfunction(fname) AS a, dbo.testfunction(lname) AS b, dbo.testfunction(refno) AS c

  INTO MYOTHERTABLE

  FROM @MYTABLE

  And now, your other table has been edited with the criteria you want