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 |
|
kishorefeb28
Starting Member
2 Posts |
 Posted - 2013-05-29 : 04:52:23
|
| Hi Experts,i have a text file with 1 -3 lakh records and 5 columns or so.... each column is seperated by tab delimiter. i need to import this file to SQLSERVER 2008.while importing this text file into SQL server, the first column in text file has 10 characters stringi need to split this string into 4 separate strings of fixed lengths (like1st string -2char, 2nd string - 2char, 3rd string - 3char, 4th string -- remaining chars) and insert it into 4 different columns in sql server.example i have a string in first column like INAPHYD00001 when i import this into sql server it should split like IN ------to be inserted into Country column in sql server.AP -----to be inserted into State column in sql server.HYD ----to be inserted into City column in sql server.00001 --to be inserted into LocalityID column in sql server.i heard i can use Bulk Copy but i'm not sure how to use it and also how to prepare a format file for this requirement.please help me in this regard.ThanksKishorekishorefeb28@icloud.com |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-29 : 04:55:13
|
| If you want to include transformation logic like splitting strings etc you may be better of using tool like SSIS------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-29 : 04:56:13
|
| For splitting part use expression based on SUBSTRING function inside derived column transformation to create required columns out of first column value------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
Impoting text file(without delimiter) into sql2008