Read CSV file - Save Columns into Rows

I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table

--CSV file format
State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 600

GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Data in SQL table from csv file should look like this

State,Name,Total
GA,John Doe,14.00
GA,Roger Smith,15.00
FL,John Doe,14.00,
FL,Roger Smith,15.00
FL,Sally Cox,16.00

I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.

See if my post in this thread helps get you started. I know it's not quite the same, but it may be a good foundation for you.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=880588&SiteID=1

Phil|||

Thanks Phil for your reply

I am new to SSID can you please provide some more detail

|||You want to use the unpivot transform to accomplish this. It does a fantastic job about flipping over columns to rows and purging nulls for you. Let me know if you need more info but there are some good blog posts out there on it.|||

Brian Knight wrote:

You want to use the unpivot transform to accomplish this. It does a fantastic job about flipping over columns to rows and purging nulls for you. Let me know if you need more info but there are some good blog posts out there on it.

I don't believe the unpivot will work because there can be a variable number of columns to unpivot. If it was a set number of columns, then this would work, however from row to row the number of columns changes.|||

Phil Brammer wrote:

Brian Knight wrote:

You want to use the unpivot transform to accomplish this. It does a fantastic job about flipping over columns to rows and purging nulls for you. Let me know if you need more info but there are some good blog posts out there on it.

I don't believe the unpivot will work because there can be a variable number of columns to unpivot. If it was a set number of columns, then this would work, however from row to row the number of columns changes.

I think it should be OK. If the original poster populates those columns wich don't have a value with NULL,then they won't get unpivoted.

If unpivot doesn't do the job let us know, there are alternatives using SORT and UNION ALL.

-Jamie

|||

Jamie Thomson wrote:

I think it should be OK. If the original poster populates those columns wich don't have a value with NULL,then they won't get unpivoted.

If unpivot doesn't do the job let us know, there are alternatives using SORT and UNION ALL.

-Jamie

My guess is that the OP can't control the file. That seems to usually be the case. The OP also states that the number of columns can go up to 600. Wow.

So for his data:
GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Row 1 has 6 columns. Row 2 has 8 columns. Row 3 has 12 columns.

Will the SSIS flat file connector work to correctly set the number of columns if the file is 10,000 rows long with the longest record (say 50 columns) is at the end when all of the other records were under 40 columns?|||

Phil Brammer wrote:

Jamie Thomson wrote:

I think it should be OK. If the original poster populates those columns wich don't have a value with NULL,then they won't get unpivoted.

If unpivot doesn't do the job let us know, there are alternatives using SORT and UNION ALL.

-Jamie

My guess is that the OP can't control the file. That seems to usually be the case. The OP also states that the number of columns can go up to 600. Wow.

So for his data:
GA,2,'John Doe',14.00,'Roger Smith',15.00
FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00
SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00

Row 1 has 6 columns. Row 2 has 8 columns. Row 3 has 12 columns.

Will the SSIS flat file connector work to correctly set the number of columns if the file is 10,000 rows long with the longest record (say 50 columns) is at the end when all of the other records were under 40 columns?

No it won't But then the complexity here is in the source file - there's no getting around that. The original poster will just have to accept that he has a ridiculously complex source file and bite the bullet. There are still lots of ways to achieve this.

-Jamie

|||

Can you guys please point me to any examples or blogs.

|||

Amar Khaira wrote:

Can you please point me any examples or blogs

One way is to search the forums for examples. On the main page of this forum is a search box on the left-hand side of the screen. Type in unpivot and you'll get a few examples/topics that discuss that transformation.|||

Here's a great blog post: http://sqljunkies.com/WebLog/ashvinis/archive/2005/03.aspx

Send me an email and I'll send you an example code and I'll eventually do a video on JumpstartTV.com. The variable amount of columns is not a problem though. bknight<at>jumpstarttv.com is my email addr if needed.

|||

Brian Knight wrote:

Here's a great blog post: http://sqljunkies.com/WebLog/ashvinis/archive/2005/03.aspx

Send me an email and I'll send you an example code and I'll eventually do a video on JumpstartTV.com. The variable amount of columns is not a problem though. bknight<at>jumpstarttv.com is my email addr if needed.

How can the variable columns not be a problem? Because the flat file source won't account for the variability, you'll have to read in each record as one column, and then split it with a script transformation. In doing that, you might be able to set a fixed number of columns and fill them with NULLs if no data exists. I'm trying to understand how you think you can expose all of the columns to the unpivot transformation when the metadata can't be pre-populated with certainty. (That is, based on my previous example, there's a certain row limit that the flat file connector uses to assess how many columns there are in the file.)|||

I am getting following error while trying Unpivot Transform:

PivotKeyValue is not valid. In an UnPivot transform with more than one unpivoted DestinationColumn, the set of PivotKeyValues per destination must match exactly.