Fields to rows? Pivot table like conversion

Centrican

Board Regular
Joined
Dec 20, 2006
Messages
130
Hi all,

I have a very large table, 15,000 rows and 50 columns, that I need to turn into a table with just three columns.

The data is half hourly meter readings, so the columns are all times (00:00, 00:30, 01:00 etc.) but I need them to convert into rows, so I have the date and time of a meter reading, in 600,000 rows.

I can think of two ways to do this; add the table to access and somehow get it to convert it, or do it in daily batches in excel, creating 30 enormous files that will then need to be added together.

I'm guessing Access is the place to do this, but does anyone have any suggestions on where to start?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
When you say "convert them to rows" do you mean you want to summarize a count of readings by periods during a 24 hour military time cycle? Do you need all 50 columns to import or just key fields? Pivot table is generally for summarizing large data but the "group by" feature might help.

I would suggest starting with your current file. Assuming it is .txt or .csv, import it into a new table using import wizard. Then make a simple query--to exclude uneeded data-- against this table and go to Pivot View. Put your time fields in the row field and look for Group By Options and see if that starts you in the right direction. It really depends on your ultimate goal for the data.
 
Upvote 0
Hi all,

I have a very large table, 15,000 rows and 50 columns, that I need to turn into a table with just three columns.

The data is half hourly meter readings, so the columns are all times (00:00, 00:30, 01:00 etc.) but I need them to convert into rows, so I have the date and time of a meter reading, in 600,000 rows.

I can think of two ways to do this; add the table to access and somehow get it to convert it, or do it in daily batches in excel, creating 30 enormous files that will then need to be added together.

I'm guessing Access is the place to do this, but does anyone have any suggestions on where to start?

i had exactly the same problem a few years back

i ended up doing in VBA looping though an append query SQL string and altering it to reflect the column headers changing
 
Upvote 0
Sorry for the delay in responding; I couldn't find my thread.

I need the table to have 15,000 x 50 rows, each with the consumption for the half hour. I've been looking for a method since posting and suspect I might have to use a lot of queries, or loop in VBA - but its something I wanted to avoid as it has to be able to be easily modified by others.

If worst comes to worst I'll just use 48 queries in one .MDB file, and then link the output table into a second one where my queries that actually use the data will be.
 
Upvote 0
Do you have an example of a few rows of what it looks like currently?
Can you provide an example of the expected output?

CT
 
Upvote 0
Basically, the rows are currently an MPAN (a serial number) and a date, and then 48 columns with the headings of the half hourly periods (00:00, 00:30, 01:00 etc.) which contain numbers.

What I want, is a column with the MPAN, a column with the date, and a column with the time (the heading of the 48 columns). Then a column with the number.

So basically 48 rows for each 1 that currently exists.

I can try to write an example in HTML if that would be easier to understand..?
 
Upvote 0
ok, this should get you what you want
Based on MS KB: http://support.microsoft.com/kb/202176

Code:
Function Transposer2(strSource As String, strTarget As String)
'Transpose Function by CT Witter for Mrexcel.com
         Dim db As DAO.Database
         Dim tdfNewDef As DAO.TableDef
         Dim fldNewField As DAO.Field
         Dim rstSource As DAO.Recordset
         Dim rstTarget As DAO.Recordset
         Dim i As Integer
         Dim wksp As DAO.Workspace
 
         Set db = CurrentDb()
         Set wksp = DBEngine.Workspaces(0) ' set up a transaction buffer
         wksp.BeginTrans ' all record set changes are buffered after this
         On Error GoTo roll0
         Set rstSource = db.OpenRecordset(strSource)
         rstSource.MoveLast
         ' Create a new table to hold the transposed data.
         ' Create 4 New Columns (Fields)
     '
         'Key_Name
         'Date
         'Field_Name
         'Data
         Set tdfNewDef = db.CreateTableDef(strTarget)
           Set fldNewField = tdfNewDef.CreateField("Key_Name", dbText)
            tdfNewDef.Fields.Append fldNewField
            Set fldNewField = tdfNewDef.CreateField("Date", dbDate)
            tdfNewDef.Fields.Append fldNewField
            Set fldNewField = tdfNewDef.CreateField("Field_Name", dbText)
            tdfNewDef.Fields.Append fldNewField
            Set fldNewField = tdfNewDef.CreateField("Data", dbText)
            tdfNewDef.Fields.Append fldNewField
            db.TableDefs.Append tdfNewDef
 
rstSource.MoveFirst
Do Until rstSource.EOF
         Set rstTarget = db.OpenRecordset(strTarget)
 
         ' Open the new table and fill
         ' the first field with key value the original table.
         ' the second field with the date
         ' the third & fourth field are built by looping
         ' thru the original table
 
         For i = 2 To rstSource.Fields.Count - 1
         With rstTarget
               .AddNew
              .Fields(0) = rstSource.Fields(0).Value
              .Fields(1) = rstSource.Fields(1).Value
              .Fields(2) = rstSource.Fields(i).Name
              .Fields(3) = rstSource.Fields(i).Value
              .Update
            End With
            Next i
            rstSource.MoveNext
            Loop
 
wksp.CommitTrans
GoTo finish_it
roll0:
MsgBox Err.Number & " " & Err.Description
wksp.Rollback ' cancel everything if unexpected error
GoTo finish_it
finish_it:
rstSource.Close
Set rstSource = Nothing
rstTarget.Close
Set rstTarget = Nothing
Set db = Nothing
End Function

Call as Transposer2("tblMeter","tblMeterTrans")

This will take:

----------------------------------------------------------------------------------------------------------
| MPAN | DATE | Time1 | Time2 | Time3 |
----------------------------------------------------------------------------------------------------------
| 1 | 7/15/2008 | 1 | 2 | 5 |
----------------------------------------------------------------------------------------------------------
| 2 | 7/20/2008 | 5 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
| 3 | 7/21/2008 | 0 | 0 | 5 |
----------------------------------------------------------------------------------------------------------

And create a new table like:
-------------------------------------------------------------------------------------
| Key_Name | Date | Field_Name | Data |
-------------------------------------------------------------------------------------
| 1 | 7/15/2008 | Time1 | 1 |
-------------------------------------------------------------------------------------
| 1 | 7/15/2008 | Time2 | 2 |
-------------------------------------------------------------------------------------
| 1 | 7/15/2008 | Time3 | 5 |
-------------------------------------------------------------------------------------
| 2 | 7/20/2008 | Time1 | 5 |
-------------------------------------------------------------------------------------
| 2 | 7/20/2008 | Time2 | 5 |
-------------------------------------------------------------------------------------
| 2 | 7/20/2008 | Time3 | 5 |
-------------------------------------------------------------------------------------
| 3 | 7/21/2008 | Time1 | 0 |
-------------------------------------------------------------------------------------
| 3 | 7/21/2008 | Time2 | 0 |
-------------------------------------------------------------------------------------
| 3 | 7/21/2008 | Time3 | 5 |
-------------------------------------------------------------------------------------

Make sure you test on a sample data set first. You may also have to refresh to database window (F5) to have the new table appear. I've wrapped it in a transaction so it should go faster, but this will load the data into memory, so you may want to start with a fresh reboot. If you run out of memory you will need to comment out the transaction setup.

HTH,
CT
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top