SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Impoting text file(without delimiter) into sql2008
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kishorefeb28
Starting Member

India
2 Posts

Posted - 05/29/2013 :  04:52:23  Show Profile  Reply with Quote
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 string
i 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.
Thanks
Kishore
kishorefeb28@icloud.com

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/29/2013 :  04:55:13  Show Profile  Reply with Quote
If you want to include transformation logic like splitting strings etc you may be better of using tool like SSIS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/29/2013 :  04:56:13  Show Profile  Reply with Quote
For splitting part use expression based on SUBSTRING function inside derived column transformation to create required columns out of first column value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000