Import csv with multiple values in a column to SQL

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)

Import csv with multiple values in a column to SQL

Author

Topic

KhD
Starting Member

6 Posts

Posted - 2012-11-12 : 21:20:21

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

808 Posts

Posted - 2012-11-13 : 10:41:08

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.

KhD
Starting Member

6 Posts

Posted - 2012-11-13 : 21:48:45

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources