Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sgraves
Starting Member
29 Posts |
 Posted - 2006-12-12 : 13:17:00
|
| Hi! I am importing some social security numbers and federal ID numbers into my database. When I do, I lose the dashes in the social and federal ID's. Is there anyway to format them after I have imported them or is there a way to make the dashes stay in excel.Thanks for any help!Scott |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-12-12 : 13:22:57
|
| What is the data type in the table for Social Security and Federal ID?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-12-12 : 13:24:45
|
| If your excel cells are formatted as "text" they should maintain the dashes when you import, no?Be One with the OptimizerTG |
|
|
|
sgraves
Starting Member
29 Posts |
Posted - 2006-12-12 : 15:58:32
|
| If I change the format in excel from special to text, I lose the dashes. I have a list of over 3000 and do not wish to retype each one after converting them to text. What now?Thanks!Scott |
|
|
|
sgraves
Starting Member
29 Posts |
Posted - 2006-12-12 : 16:08:06
|
| The data type in the table is varchar for both. This right?Scott |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-12-12 : 16:12:18
|
| The formatting is being done in Excel, most likely, to add in the dashs. Right-click on one of the values in Excel and choose "Format..." and see what comes up (probably CUSTOM with a format string) to be sure.One idea is just to insert a new column in Excel next to the existing column, and copy the data into the new column formatted as you want -- as text -- explicitly including the dashes and whatnot that you want.i.e., if column "B" is the new column, and column "A" contains the numeric soc. security numbers, then you could put a formula like this in each row in column B:=LEFT(A1,3) & "-" & MID(A1,4,2) & "-" & RIGHT(A1,4)copy that all the way down, verify the results, and then highlight the entire column B, choose COPY, and do a PASTE SPECIAL-->VALUES right back over the new column. This replaces the formulas with the values that they returned. Then delete the first column and you are done. Or something to that effect.Or, you could bring in the raw, unformatted numeric data into SQL Server and do the conversion there. Either way, there's never going to be a reason why you'd need to re-type things! You have some good tools available to you to use, make use of them.So, there's many ways to do this, you just have to know how to write formulas in Excel or in T-SQL. If you don't know how to do it in either place, time to learn --- it's a pretty fundamental things to know about either technology. Feel free to ask specific questions here, but experiment and see what you can learn.- Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
format