It seems that the purpose of your stored procedure is to transform the
spreadsheet data into a form that you can use, presumably in a table. It is
possible to do this kind of transformation in SQL, it just isn't pretty!
Here
goes.

First, assume you have your sample data loaded into your temp table. I've
added a row number to help us later. You would obviously do this bit in DTS:

CREATE TABLE TempTable1 (rowno INTEGER IDENTITY PRIMARY KEY, col1
VARCHAR(20) NULL, col2 VARCHAR(20) NULL, col3 VARCHAR(20) NULL, col4
VARCHAR(20) NULL, col5 VARCHAR(20) NULL, col6 VARCHAR(20) NULL, col7
VARCHAR(20) NULL, col8 VARCHAR(20) NULL)

INSERT INTO TempTable1 (col1,col2) VALUES ('Country','Nigeria')
INSERT INTO TempTable1 (col1,col2) VALUES ('Region','African Continent')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7,col8)
VALUES ('Gender','Male','Male','Male','Male','Male','Fema le','Female')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7,col8)
VALUES ('Age Group','0-10','10-20','20-30','40-50','50-60','0-10','10-20')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7,col8)
VALUES ('Total','200','323','111','3232','333','555','333 ')
INSERT INTO TempTable1 (col1,col2) VALUES ('Country','Nicaragua')
INSERT INTO TempTable1 (col1,col2) VALUES ('Region','African Continent')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7)
VALUES ('Gender','Male','Male','Male','Female','Female',' Female')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7)
VALUES ('Age Group','0-4','15-20','20-30','0-4','15-20','20-30')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7)
VALUES ('Total','200','323','111','3232','112','441')

You haven't told us what the target structure is that you want to put the
data into. Based on your sample I'll assume it looks something like this:

CREATE TABLE SomeStats (country VARCHAR(20) NOT NULL /* REFERENCES Countries
(country) */, min_age INTEGER NOT NULL, max_age INTEGER NOT NULL, CHECK
(min_age>=0 AND max_age>min_age AND max_age<=120), gender CHAR(1) NOT NULL
CHECK (gender IN ('M','F')), stat INTEGER NOT NULL, PRIMARY KEY
(country,gender,min_age))

In reality you would probably want to use codes rather than country names.
The Continent name obviously belongs in the Countries table rather than here
so I've left it out.

Create a view:

CREATE VIEW TransformStats
AS
SELECT rowno, 1 AS col, col1 AS stat
FROM TempTable1
UNION ALL
SELECT rowno, 2 AS col, col2
FROM TempTable1
UNION ALL
SELECT rowno, 3 AS col, col3
FROM TempTable1
UNION ALL
SELECT rowno, 4 AS col, col4
FROM TempTable1
UNION ALL
SELECT rowno, 5 AS col, col5
FROM TempTable1
UNION ALL
SELECT rowno, 6 AS col, col6
FROM TempTable1
UNION ALL
SELECT rowno, 7 AS col, col7
FROM TempTable1
UNION ALL
SELECT rowno, 8 AS col, col8
FROM TempTable1

Finally, insert your data:

INSERT INTO SomeStats (country, min_age, max_age, gender, stat)
SELECT country,
LEFT(age,CHARINDEX('-',age)-1),
SUBSTRING(age,CHARINDEX('-',age)+1,20),
gender, stat
FROM
(SELECT
(SELECT stat
FROM TransformStats
WHERE rowno=
(SELECT MAX(rowno)
FROM TransformStats
WHERE col = 1
AND stat = 'Country'
AND rowno <= T.rowno)
AND col=2) AS country,
(SELECT stat
FROM TransformStats
WHERE rowno=
(SELECT MAX(rowno)
FROM TransformStats
WHERE col = 1
AND stat = 'Age Group'
AND rowno <= T.rowno)
AND col=T.col) AS age,
(SELECT LEFT(stat,1)
FROM TransformStats
WHERE rowno=
(SELECT MAX(rowno)
FROM TransformStats
WHERE col = 1
AND stat = 'Gender'
AND rowno <= T.rowno)
AND col=T.col) AS gender,
stat
FROM
TransformStats AS T
WHERE ISNUMERIC(stat)=1) AS T

This will of course fail if your spreadsheets aren't in a fairly regular,
predictable format. This is an unavoidable problem with spreadsheet data and
there isn't an easy solution except to find a reliable, structured data
source. Your data looks somewhat suspect anyway. Last time I checked my
atlas Nicragua [sic] wasn't in Africa

Hope this helps.

--
David Portas
SQL Server MVP
--