 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2007Excel to Database Conversion
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 Check out Pivot Tables: Data > Pivot Table Report...  SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >I am often faced with the task of moving data from excel workbooks > into numerous databases (Access, OLAP, SQL). Here is my challenge: [quoted text clipped - 20 lines] > Sep Bonds 70 > Sep Property 80 I am familiar with pivot tables but I am not sure how they would be a solution for this task? Can you explain? 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. SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >I am familiar with pivot tables but I am not sure how they would be a > solution for this task? Can you explain? 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) >I am often faced with the task of moving data from excel workbooks > into numerous databases (Access, OLAP, SQL). Here is my challenge: [quoted text clipped - 20 lines] > Sep Bonds 70 > Sep Property 80 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!!! 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) > Awesome, expanding on this method how do I create records when the > spreadsheet has multiple rows. [quoted text clipped - 5 lines] > > Thanks in advance!!! 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.