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 |
|
TopsyKretts
Starting Member
6 Posts |
 Posted - 2008-05-01 : 12:23:02
|
| Happy Thursday all,I am importing a text file to sql and most of my fields look like this:"M","NEW ADDRESS",and my other field looks like this:"firstname Lastname" but I need it like this:"firstname", "Lastname"Can anyone help me understand a better way of making this happen? Thanks in advance |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-01 : 12:53:16
|
| I think you need to specify a custoim format file for thishttp://msdn.microsoft.com/en-us/library/aa174646(SQL.80).aspxhttp://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html |
 |
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2008-05-01 : 13:40:58
|
| If this is a one time import you can save yourself a little trouble by using the import wizard in Enterprise manager. Go ahead and create a table that matches your input and then create a second table with your "firstname" and "LastName" fields in it as well as the other fields. This will give you a little flexibility to clean bad data for situations where someone has a middle name in the string.I'd use an update(or insert) statement something like: update newtableset firstname = left(name,charindex(' ',name)), Lastname = right(name,Len(name) - charindex(' ',name))Of course this will not be right if there are middle names. You could play around with this a bit to get all the scenarios for your data but you may not need to.But if this is a regular import, look into the links above and also check out DTS.An infinite universe is the ultimate cartesian product. |
|
|
|
|
|
|
Importing Text file to SQL