Convert Columns to Rows

[Buck Turgidson]

I have a very large spreadsheet. The first 2 columns are a description, and
the next 52 columns are a value for a week of the year.

Is there a way in Excel to transform these into a format that is loadable
into a relational database such as Oracle?

For example, I'd like to change the following into the latter format. The
first column would be a derived week number. That first column is something
I could live without if I had to.

Any suggestions for a simple macro would be appreciated.



washer sunday 33 44 55.........
dryer monday 667 333 44........
refrig monday 555 876 99.......



1 washer sunday 33
2 washer sunday 44
3 washer sunday 55
1 dryer monday 667
2 dryer monday 333
3 dryer monday 444
1 refrig monday 555
2 refrig monday 876
3 refrig monday 99

 

[Guest]

hi,
you wont have to reformat.
the import/export driver will do that. and i am pretty
sure that oracle has the driver for excel built it.

 

[Buck Turgidson]

you wont have to reformat.

the import/export driver will do that. and i am pretty
sure that oracle has the driver for excel built it.

Thanks. I am not clear - is this a MSFT driver or an Oracle driver?

 

[Guest]

Buck:

How are you trying to connect Excel to Oracle? If you are using ADO, take a
look at Microsoft Knowledge Base Article - 295646. This article explains how
to import data into excel and transpose it before pasting it into a
worksheet. The article contains enough information for you to figure out how
to do that in reverse.

 

[Jamie Collins]

you wont have to reformat.
the import/export driver will do that.

I pretty sure the driver will not reformat in this way.

It is *possible* to do this kind of transformation using SQL,
something like (assume Excel columns for data as posted are named F1
to F5):

SELECT
1 AS week_number,
F1 AS appliance_name,
F2 AS weekday_name,
F3 AS some_number
FROM
MyExcelTable
UNION ALL
SELECT
2 AS week_number,
F1 AS appliance_name,
F2 AS weekday_name,
F4 AS some_number
FROM
MyExcelTable
UNION ALL
SELECT
3 AS week_number,
F1 AS appliance_name,
F2 AS weekday_name,
F5 AS some_number
FROM
MyExcelTable
ORDER BY
3 DESC, 2, 1
;

However, the above is for just 3 columns and I don't think it
practical (nor practicable) to scale to 52 columns.

I agree with the OP: they *do* require a simple Excel macro to
transform the data in Excel.

Jamie.

--

 

[Jamie Collins]

(e-mail address removed) (Jamie Collins) wrote ...

It is *possible* to do this kind of transformation using SQL
However,
I agree with the OP: they *do* require a simple Excel macro to
transform the data in Excel.

To the OP: you may benefit from a re-post. Me posting SQL usually has
the effect of driving other volunteers away <g>.

Jamie.

--

 

[Tim Williams]

How many rows?

Tim.