Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Columns to Rows
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert rows to columns | Excel Discussion (Misc queries) | |||
convert columns to rows | Excel Worksheet Functions | |||
Convert rows to columns | Excel Worksheet Functions | |||
to convert columns to rows having mulit independent group columns | Excel Worksheet Functions | |||
Can I convert columns to rows? | Excel Discussion (Misc queries) |