SQL Server - How to eliminate spaces when table is converted from excel

Hi,

I have converted an excel sheet in to my database table. But the problem is when I am trying to find out some values it is not giving proper result. I am using LTRIM and RTRIM but it is not working. Because during the conversion some characters are included in the fields that cannot be eliminated by the TRIM functions (but they look like blank). Any solution!!!!!!

Please help.

Partha

Hi Partha,

               Can you try and convert the excel sheet to a CSV file first and see if it is just the spaces that are there or any other characters are appearing (open the CSV in notepad)? Usually this sort of problem does not happen when excel sheet is imported as a table so as you have suggested above there must be some other charcters in there. 

Also, you can try and run an update query on the table that just trims the data in the columns and then try to find the length of some data, in case it is different than what you expect I would suggest you retype the values in a new sheet (and import in the DB) just to test them out. 

Hope this helps.

As pointed out by Venkatesh, it is possible that those characters are tab characters.  Aside from tab characters, they may be carriage returns or line feeds.  To verify, you can run the following query:

SELECT * FROM YourTable WHERE YourColumn LIKE '%' + CHAR(9) + '%'

SELECT * FROM YourTable WHERE YourColumn LIKE '%' + CHAR(13) + '%'

If these queries return values, you can easily replace them with an empty string as follows:

UPDATE YourTable
SET YourColumn = REPLACE(YourColumn, CHAR(9), '')

UPDATE YourTable
SET YourColumn = REPLACE(YourColumn, CHAR(13), '')

http://www.sql-server-helper.com