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 350
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.What is the best and
fastest way to achieve this?
Throw me anythingHi
"pintoo" wrote:

> 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 350
> 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
>
If you are moving these into rows the data is not normalized! You will only
be able to have a finite number of rows that can be converted, therefore you
could self join if each row is allocated a sequence number or the methid
described by Erland in
http://groups-beta.google.com/group...r />
2e?&hl=en

> I have multiple CSV files with millions of records.What is the best and
> fastest way to achieve this?
> Throw me anything
>
If you are using SQL 2005 you could use the PIVOT command.
John