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: Today @ 11:17 AM
Points: 3,374, Visits: 7,298
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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: Today @ 11:17 AM
Points: 3,374, Visits: 7,298
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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: Friday, July 25, 2014 7:38 PM
Points: 403, Visits: 277
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