Convert Columns to Rows
 

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

Convert Columns to Rows
 

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.


-----Original Message-----
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






.

Convert Columns to Rows
 

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?

Convert Columns to Rows
 

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.



"Buck Turgidson" wrote:

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

Convert Columns to Rows
 

wrote ...

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.

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


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.

--

Convert Columns to Rows
 

Convert Columns to Rows
 

How many rows?

Tim.


"Buck Turgidson" wrote in message
...
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

Convert Columns to Rows
 

Old fashion way, works on multiple versions of Oracle.

Save your Excel file as comma separated values file (*.CSV).

Create table in Oracle with your Oracle table column names same as your
spreadsheet.

Use Oracle SQLLoader function to load the data from the CSV file into
the Oracle table.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!