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)
 Import csv with multiple values in a column to SQL		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KhD
Starting Member

6 Posts
Posted - 11/12/2012 :  21:20:21  Show Profile  Reply with Quote
Hi,

I have a csv file with the following format: (for clarity, I've added pipes as column separator here)

9-Jan-03 | S | MC100;MC101;MC128 | CAPITAL BUILDER | IFO | HEL | MC100 0.0;MC101 0.0;MC128 0.0
1-Jan-00 | M | B02;C02;MC04;MC23 | UNIVERSAL RETIREMENT PROGRAMME | PUP | HIL | B02 13276.17;C02 13462.17;MC04 12726.15;MC23 11952.31
8-Aug-12 | M | MC200;MC201;MC228 | CAPITAL BUILDER FUND | IFOG | HELD | MC200 0.0;MC201 0.0;MC228 0.0

I want to insert this data into SQL table in the following format:

9-Jan-03 | S | MC100 | CAPITAL BUILDER | IFO | HEL | MC100 0.0
9-Jan-03 | S | MC101 | CAPITAL BUILDER | IFO | HEL | MC101 0.0
9-Jan-03 | S | MC128 | CAPITAL BUILDER | IFO | HEL | MC128 0.0
1-Jan-00 | M | B02 | UNIVERSAL RETIREMENT PROGRAMME | PUP | HIL | B02 13276.17
1-Jan-00 | M | C02 | UNIVERSAL RETIREMENT PROGRAMME | PUP | HIL | C02 13462.17
1-Jan-00 | M | MC04 | UNIVERSAL RETIREMENT PROGRAMME | PUP | HIL | MC04 12726.15
1-Jan-00 | M | MC23 | UNIVERSAL RETIREMENT PROGRAMME | PUP | HIL | MC23 11952.31
8-Aug-12 | M | MC200 | CAPITAL BUILDER FUND | IFOG | HELD | MC200 0.0
8-Aug-12 | M | MC201 | CAPITAL BUILDER FUND | IFOG | HELD | MC201 0.0
8-Aug-12 | M | MC228 | CAPITAL BUILDER FUND | IFOG | HELD | MC228 0.0

Is there any way to do this in SSIS package?

I can do this using a Script Task and writing a code to loop through each line in the csv file. But that would be very messy.

Please suggest a simpler way to do this.

Thanks!!

Hommer
Aged Yak Warrior

693 Posts
Posted - 11/13/2012 :  10:41:08  Show Profile  Reply with Quote
If you are dealing with large data set, you should let db engine to the work for you.

1) import the data in first and adding a key column so you can join on them later.

2) build a select on those two columns and flip them into rows.

3) join the result of 2)'s select with 1) to get the final desired outcome and move it into your final destination.

Then you can saved these scripts into SSIS for re-run.
Go to Top of Page

KhD
Starting Member

6 Posts
Posted - 11/13/2012 :  21:48:45  Show Profile  Reply with Quote
Thanks for the reply Hommer.

I found a link in another forum which was very helpful and it worked.
http://social.msdn.microsoft.com/Forums/en-CA/sqlintegrationservices/thread/6cb66f8a-6527-42b4-a7df-1087ceb6e5e6
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.04 seconds. Powered By: Snitz Forums 2000