Re: Excel to Database Conversion

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Try using this UNpivot method from John Walkenbach's
website illustrating Joel Horowitz's technique:
http://j-walk.com/ss/excel/usertips/tip068.htm

With your range in this structure (note the Acct heading):
Acct______Aug____Sep
Cash______10____ 50
Stocks____20_____60
Bonds_____30_____70
Property__40_____80

<Data><Pivot Table>
Use: Multiple Consolidation Ranges__________Click [Next]
Select: "I will create the page fields"_____Click [Next]
Range: (Select your data)_____Click [Add]___Click [Next]
Click the [Layout] button
ROW: Drag ROW off the diagram
COLUMN: Drag COLUMN off the diagram
DATA: Leave the VALUE field in this section
Click the [OK] button
Select a location for the Pivot Table_____Click [Finish]

That will create a minimal Pivot Table containing only one cell with a
value.

Double-Click on that one value cell
Excel will add a sheet to the workbook with the details of
that cell in a database table format, like this:
Row_______Column___Value
Cash______Aug______10
Cash______Sep______50
Stocks____Aug______20
Stocks____Sep______60
Bonds_____Aug______30
Bonds_____Sep______70
Property__Aug______40
Property__Sep______80

What to you think? Will that work for you?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)


"banker123" <bradbrockman@xxxxxxxxx> wrote in message
news:1190404309.119072.309670@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am often faced with the task of moving data from excel workbooks
into numerous databases (Access, OLAP, SQL). Here is my challenge:

I have a spreadsheet with the months listed in a row and the accounts
listed in a column, I need to create a record for each account and
month intersection, see second example below. I have been doing this
by simply cutting and pasting but the spreadsheets are growing faster
that I can convert them, and there has to be a more efficient way of
accomplishing this task. I am not trying to write VBA.

Aug Sep
Cash 10 50
Stocks 20 60
Bonds 30 70
Property 40 80

Aug Cash 10
Aug Stocks 20
Aug Bonds 30
Aug Property 40
Sep Cash 50
Sep Stocks 60
Sep Bonds 70
Sep Property 80



.

Relevant Pages

  • Re: Excel Database Conversion (Record Creation)
    ... I have a spreadsheet with the months listed in a row and the accounts ... Aug Cash 10 ... Aug Stocks 20 ... Aug Bonds 30 ...
    (microsoft.public.excel.misc)
  • Re: Excel to Database Conversion
    ... Data> Pivot Table Report... ... Aug Cash 10 ... Aug Stocks 20 ... Aug Bonds 30 ...
    (microsoft.public.excel)
  • Re: Excel Database Conversion (Record Creation)
    ... I am not trying to write VBA. ... Aug Cash 10 ... Aug Stocks 20 ... Aug Bonds 30 ...
    (microsoft.public.excel.misc)
  • Excel Database Conversion (Record Creation)
    ... I have a spreadsheet with the months listed in a row and the accounts ... Aug Cash 10 ... Aug Stocks 20 ... Aug Bonds 30 ...
    (microsoft.public.excel.misc)
  • Why Bother With Bonds?
    ... Why Bother With Bonds? ... If stocks outperform bonds by as much as 5% over the long run then, ... It is written into our investment truisms that investors expect their ... 19th century for them was meaningless, as the stock market then was ...
    (misc.invest.stocks)