#1
|
|||
|
|||
Convert Spreadsheet to Database Table
I am trying to find a quick way to convert an Excel 2013 spreadsheet to a database table. I am familiar with the Transpose feature in Excel - but I don't think this is what I'm looking for. My challenge is the top row really needs to be one of the fields in the database table. The database table structure is three fields - CustomerId / ItemId / Price - the spreadsheet that was given to me has the Top Row as the ItemId:
See attachment . . . . What I need is: CustId ItemId Price ABC01 ITEM01 102.30 ABC01 ITEM02 77.50 ABC01 ITEM03 14.77 FRK03 ITEM01 110.33 FRK03 ITEM02 66.33 FRK03 ITEM03 14.77 The spreadsheet I'm working with has 278 rows and 55 columns worth of data. Thanks in advance, MB |
#2
|
||||
|
||||
Add a new worksheet (Sheet2) to your workbook, then run the following macro.
Code:
Sub MakeRecords() Dim i As Long, j As Long, LRow As Long, LCol As Long, wkSht As Worksheet With ThisWorkbook Set wkSht = .Sheets("Sheet2") With wkSht .Cells(1, 1).Value = "CustId" .Cells(1, 2).Value = "ItemId" .Cells(1, 3).Value = "Price" End With With .Sheets("Sheet1") LRow = .Cells.SpecialCells(xlCellTypeLastCell).Row LCol = .Cells.SpecialCells(xlCellTypeLastCell).Column For i = 2 To LRow For j = 2 To LCol wkSht.Cells((i - 2) * (LCol - 1) + j, 1).Value = .Cells(i, 1).Value wkSht.Cells((i - 2) * (LCol - 1) + j, 2).Value = .Cells(1, j).Value wkSht.Cells((i - 2) * (LCol - 1) + j, 3).Value = .Cells(i, j).Value Next Next End With End With End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Easy send/save Excel data to Database Table or Stored Procedure | TonyHarvard | Excel | 3 | 07-11-2012 12:43 PM |
How to convert foxpro database to excel | KIM SOLIS | Excel | 0 | 10-20-2011 11:24 PM |
How do I dynamically update data in a Word Document from a database table | RSchmidt | Word | 1 | 07-14-2011 04:27 PM |
Using data entered in prompt to search database table | BluRay | Word | 1 | 03-25-2011 01:47 AM |
Converting MSWord table to MS Excel spreadsheet | MiaLouise | Office | 1 | 03-09-2010 10:08 AM |