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 2008 Forums
 SSIS and Import/Export (2008)
 split tab delimited file vertically on import
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MrBloom
Starting Member

United Kingdom
32 Posts

Posted - 05/26/2013 :  07:12:11  Show Profile  Reply with Quote
Hi
I have a flat file with a very large number of tab delimited columns, something like 3700. The structure of the columns are
quote:
ID start end col1 col2 col3 etc..... to col3700

I would like to spilt the text file into 3 tables on import to sql server each with about 1000 columns each as sql server won't handle many more than this. So each table should have the same three fields ID start End and columns to 1-1000 and the next table should be 1001-2000 etc.

So how can I split the file where columns are delimited by a tab and the cr/lf is only at the end of the row.

thanks

James K
Flowing Fount of Yak Knowledge

3640 Posts

Posted - 05/26/2013 :  08:03:09  Show Profile  Reply with Quote
If you are using Import/Export wizard, one of the dialogs has an Edit Mappings button. click on it and select the columns you want to import. That will require you to have more than one package. If you are using BIDs, you can do something similar by having multiple dataflow tasks, and in each, pick the subset of columns that should go into the destination table.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/27/2013 :  01:17:56  Show Profile  Reply with Quote
A simple SSIS package containing data flow task with condition transform and three destination (flat file) will do it for you
I hope the metadata of file remain intact always

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

MrBloom
Starting Member

United Kingdom
32 Posts

Posted - 05/28/2013 :  05:24:50  Show Profile  Reply with Quote
thanks for the replies. It got me thinking. What I was doing wrong was trying to select the required columns from the source connection whereas you need to select all the columns in the source and create 3 destination connections and make the selections here. you can then import to SQL Server or export to text etc with a multicast.

thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/28/2013 :  05:59:50  Show Profile  Reply with Quote
yep. Thats exactly waht I suggested

------------------------------------------------------------------------------------------------------
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