Trim spaces after excel import

Question

Post reply

Trim spaces after excel import

C-kweldude

Old Hand

Points: 312
More actions
January 13, 2014 at 4:49 pm

#303001

Hi Guys
I have imported some data into two tables from a messy spreadsheet provided by my client.
I realise now that a lot of the fields have a leading space and would like to trim them on the whole table.
Can someone give me a nudge here?
Thanks in advance!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You might need to do an update on all fields.

Something like this:

UPDATE MyTable

SET column1 = LTRIM(column1),

column2 = LTRIM(column2),

column3 = LTRIM(column3),

--...

columnN = LTRIM(columnN)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

C-kweldude Old Hand Points: 312 More actions · Answer 2

Thnk you luiz

Worked like a charm.

I am also trying to copy data from one column to the other and get the following error

Here is the statement

update stkitem

SET Csimplecode=Code

and the error is

Msg 8152, Level 16, State 14, Line 1

String or binary data would be truncated.

The statement has been terminated.

Any ideas?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

That's because your column Code is larger than your column Csimplecode.

If you provide DDL of your table, I could give you the exact code, but you basically need to use a LEFT(Code, N) where N is the length of your Csimplecode column (You will lose data).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

C-kweldude Old Hand Points: 312 More actions · Answer 4

Thanks Luis

Code column is (Varchar(400),Null)

Csimplecode Column is (Varchar(20),Null)

However none of the data is longer than 20 Characters

Your help is appreciated.

A

Montana Life Right there with Babe Points: 720 More actions · Answer 5

I would want to check first before possibly truncating data:

select max(len(code)) from stkitem

or

select *

from stkitem

where len(code) > 20

If you do try:

update stkitem

SET Csimplecode=left(Code,20)

Here is the link for MS: http://technet.microsoft.com/en-us/library/ms177601.aspx

Mike

How to Post Performance Problems[/url]

How to Post Best Practices[/url]