Excel to Database Conversion

[banker123]

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

 

[Sandy Mann]

Check out Pivot Tables:

Data > Pivot Table Report...

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[banker123]

I am familiar with pivot tables but I am not sure how they would be a
solution for this task? Can you explain?

 

[Sandy Mann]

First column with months lebelled "Months", Second Column labbeled
"Transaction" third Column labbelled "Amount"

In the Wizard:
Drag Transaction to the Row area, Months to the Column area & Amount to the
Data area Make sure that Amount is set to Sum.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ron Coderre]

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]

Awesome, expanding on this method how do I create records when the
spreadsheet has multiple rows.

Jan Feb
101 Cash 1 2
102 Bonds 3 4
103 Stock 5 6


Thanks in advance!!!

 

[Ron Coderre]

I can think of 2 optons:

1)Create a concatenated helper column to assist.

For example:
Insert a column after Col_B
Then:
C1: MultiField
C2: =A2&"|"&b2
Copy down as far as you need

In your example, C2 would result in: 100|Cash

Then...use the UNpivot method with the source data beginning in C1
The resulting expansion will look like this:
Row___________Column__Value
100|Cash______Aug_____10
100|Cash______Sep_____50
200|Stocks____Aug_____20
200|Stocks____Sep_____60
300|Bonds_____Aug_____30
300|Bonds_____Sep_____70
400|Property__Aug_____40
400|Property__Sep_____80

The last step is to parse the MultiField using Text-to-Columns
with the pipe (Shift+\) as the separator.

Alternatively,
2)You could skip the MultiField formulas
and just consolidate beginning with Col_B. After expansion,
the table will look like the original post.
You can then assign Acct Numbers using VLOOKUP formulas against
a table of Accts/AcctNums

Does that help?
--------------------------

Regards,

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

 

[banker123]

Think I will go with the lookup option, thanks great solution. Hard
to believe this situation does not occur more often, maybe folks just
deal with it or use the copy and paste method.