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.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 split tab delimited file vertically on import

Author  Topic 

MrBloom
Starting Member

36 Posts

Posted - 2013-05-26 : 07:12:11
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-26 : 08:03:09
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

52326 Posts

Posted - 2013-05-27 : 01:17:56
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

36 Posts

Posted - 2013-05-28 : 05:24:50
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

52326 Posts

Posted - 2013-05-28 : 05:59:50
yep. Thats exactly waht I suggested

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

- Advertisement -