Make table

  • Thread starter Thread starter Charles B. Owen
  • Start date Start date
C

Charles B. Owen

I have an excel spreadsheet of 7000 rows and 50 columns, with 350,000
resulting cells. I want to turn this table into a database of 350,00
records, each of which is made up of three fields: 1) Row heading, 2) Column
heading, and 3) Number from the cell. (Obviously with key field, etc.)

What approach can someone recommend with this challenge? I have imported
into an Access database, but it is the same 7000 by 50 table. Is there a
MakeTable Query that can do this?

Buddy
 
Charles said:
I have an excel spreadsheet of 7000 rows and 50 columns, with 350,000
resulting cells. I want to turn this table into a database of 350,00
records, each of which is made up of three fields: 1) Row heading, 2)
Column heading, and 3) Number from the cell. (Obviously with key
field, etc.)

What approach can someone recommend with this challenge? I have
imported into an Access database, but it is the same 7000 by 50
table. Is there a MakeTable Query that can do this?

Buddy
Click to expand...

Try this:

In your spreadsheet, crank up VBA and paste this into a module:

Sub ConvertMyData()

Dim complete_list As New Collection
Dim inrage As Range
Set inrange = Worksheets("Sheet1").Range("a1:b10")
Worksheets("Sheet1").Activate ' for some stupid reason the code
doesnt work is the worksheet is not active.

'Build complete_list collection
On Error Resume Next
For Each c In inrange
da_row = c.Row
da_values = c.Row & "|" & c.Column & "|" & c.Value
complete_list.Add Item:=da_values, key:=CStr(c.Row) & "|" & c.Column
Next c
On Error GoTo 0

'write results to a test file
filenumb = FreeFile
Open "Output.txt" For Output As #filenumb
i = 1
For Each x In complete_list
Print #filenumb, x
i = i + 1
Next x
Close #filenumb

'cleanup
Set inrange = Nothing
Set complete_list = Nothing
End Sub

Tidy up any line breaks caused by copying from here. Change the Set Inrange
line to reflect the worksheet name and data range you want to convert. Run
the subroutine.

It *should* create a text file called Output.txt in your default save
directory (probably My Documents) with the data (delimited by the "|"
character) in a form in which you can import it to an Access table.

I have to give credit for the code to a mate of mine Shane Cox (Haplo) who
wrote something similar (more complex actually) for me a while back.

Damien
 
This worked great, assigning the column or row # as the field. I am study VB
to see how to cause it to take the first column entry for the row value and
the first row entry for the column value. Is there a quick answer to how to
do that? Feel free to ignore the question if you think I need to just go
look it up. ;)

Buddy Owen
 
I have an excel spreadsheet of 7000 rows and 50 columns, with 350,000
resulting cells. I want to turn this table into a database of 350,00
records, each of which is made up of three fields: 1) Row heading, 2) Column
heading, and 3) Number from the cell. (Obviously with key field, etc.)

What approach can someone recommend with this challenge? I have imported
into an Access database, but it is the same 7000 by 50 table. Is there a
MakeTable Query that can do this?

Buddy
Click to expand...

An Access solution would be a "Normalizing Union Query". You'll need
to go to the SQL window to create it. Assuming that the "row heading"
is a field in the table named ID, and the fieldnames are the "column
heading", the SQL would be something like

SELECT ID, "Field1" AS ColHeading, [Field1] AS FieldValue FROM
wideflat
UNION ALL
SELECT ID, "Field2", [Field2] FROM wideflat
UNION ALL
SELECT ID, "Field3], [Field3] FROM wideflat
<etc etc>

You can then base a MakeTable or Append query on this UNION query.
 
Back
Top