Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-11-2013, 11:15 AM
mebjen mebjen is offline Convert Spreadsheet to Database Table Windows 7 64bit Convert Spreadsheet to Database Table Office 2007
Novice
Convert Spreadsheet to Database Table
 
Join Date: May 2012
Posts: 1
mebjen is on a distinguished road
Cool 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
Attached Files
File Type: xlsx cEx.xlsx (8.9 KB, 16 views)
Reply With Quote
  #2  
Old 11-15-2013, 11:21 PM
macropod's Avatar
macropod macropod is offline Convert Spreadsheet to Database Table Windows 7 32bit Convert Spreadsheet to Database Table Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Thread Tools
Display Modes


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
Convert Spreadsheet to Database Table 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

Other Forums: Access Forums

All times are GMT -7. The time now is 12:33 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft