Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trim spaces after excel import Expand / Collapse
Author
Message
Posted Monday, January 13, 2014 4:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 7, 2014 9:22 PM
Points: 22, Visits: 48
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!
Post #1530503
Posted Monday, January 13, 2014 5:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:07 PM
Points: 3,622, Visits: 8,129
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1530507
Posted Monday, January 13, 2014 5:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 7, 2014 9:22 PM
Points: 22, Visits: 48
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?

Post #1530510
Posted Monday, January 13, 2014 5:24 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:07 PM
Points: 3,622, Visits: 8,129
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1530511
Posted Monday, January 13, 2014 5:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 7, 2014 9:22 PM
Points: 22, Visits: 48
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
Post #1530512
Posted Monday, January 13, 2014 8:23 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:45 PM
Points: 403, Visits: 307
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


Mike

How to Post Performance Problems

How to Post Best Practices
Post #1530520
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse