×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel Transform data for database import

Excel Transform data for database import

Excel Transform data for database import

(OP)
Sorry I'm unable to explain, so therefore it is hard to search. Here is an example of what I have and what I want it to look like. This will be over hundreds of records.

What the data is:
Code Bore (mm) Bush (mm) Diameter (mm) Diameter (in) Kerf (mm) Rake RPM Teeth Tooth Grind
TP2102540 25 16 210 8-1/4 1.8 15 7000 40 ATB 15
TP2163060 30 16 216 8-1/2 2.0 5 7000 60 ATB 15

How I need it to look like:
TP2102540 Bore (mm) 25
TP2102540 Bush (mm) 16
TP2102540 Diameter (mm) 210
TP2102540 Diameter (in) 8-1/4
TP2102540 Kerf (mm) 1.8
TP2102540 Rake 15
TP2102540 RPM 7000
TP2102540 Teeth 40
TP2102540 Tooth Grind ATB 15
TP2163060 Bore (mm) 30
TP2163060 Bush (mm) 16
TP2163060 Diameter (mm) 216
TP2163060 Diameter (in) 8-1/2
TP2163060 Kerf (mm) 2.0
TP2163060 Rake 5
TP2163060 RPM 7000
TP2163060 Teeth 60
TP2163060 Tooth Grind ATB 15

Even if someone could point me in the right direction would be much appreciated.

RE: Excel Transform data for database import

Hi,

Your beginning table is what could be referred to as a report. Reports are kind of useless for performing analysis in Excel. What you need is normalized data.

The Excel Pivot Table Wizard can be a tool to help you normalize your data.

FAQ68-7103: Normalize your data using the PivotTable Wizard

You can start the Pivot Table Wizard via alt+d+p and continue with the instructions in the linked FAQ.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Transform data for database import

Aside from Skip's suggestion, you can also use a simple macro with a few lines of VBA code to transform what you have to what you need.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Excel Transform data for database import

Another method: excel pover query from excel table has explicit unpivot action.

combo

RE: Excel Transform data for database import

I've used Skip's suggestion in Excel with great success. Being a database guy, I would import as is and then create a union query that can be used as the source for a make table query:

CODE --> SQL

SELECT Code, "Bore (mm)" AS Characteristic, Bore
FROM SawBlades
UNION ALL
SELECT Code, "Bush (mm)", Bush
FROM SawBlades
UNION ALL
SELECT Code, "Diameter (mm)", DiameterMM
FROM SawBlades
UNION ALL
SELECT Code, "Diameter (in)", DiameterIN
FROM SawBlades
UNION ALL
SELECT Code, "Kerf (mm)", Kerf
FROM SawBlades
UNION ALL
SELECT Code, "Rake", Rake
FROM SawBlades
UNION ALL
SELECT Code, "RPM", RPM
FROM SawBlades
UNION ALL
SELECT Code, "Teeth", Teeth
FROM SawBlades
UNION ALL
SELECT Code, "Tooth Grind", [Tooth Grind]
FROM SawBlades
ORDER BY 1; 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Excel Transform data for database import

Had a few minutes to kill...
If your data is in columns A-J with the header in row 1, my output is in columns M-N-O starts in row 1

CODE

Option Explicit

Sub blackduck()
Dim R As Integer
Dim C As Integer
Dim R_Out As Integer

R = 2
R_Out = 1

Do While Cells(R, 1).Value <> ""
    For C = 2 To 10
        Range("M" & R_Out & ":O" & R_Out).Value = Array(Cells(R, 1), Cells(1, C), Cells(R, C))
        R_Out = R_Out + 1
    Next C
    R = R + 1
Loop

End Sub 

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Excel Transform data for database import

(OP)
Thank you soo much for everyone's help, works a treat!
I have used Andy's code because the names and number of my column headings will vary between different ranges of products.
The code below is Andy's with an extra line I have added to handle the variable number of columns.
(ps. Haven't been on the forum for a long time, but heartwarming to see my old helpers Skip and Duane still pop up straight away offering great solutions and advice.)

Option Explicit

Sub Attributes()
Dim R As Integer
Dim C As Integer
Dim R_Out As Integer
Dim lastCol As Long

R = 2
R_Out = 1
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

Do While Cells(R, 1).Value <> ""
For C = 2 To lastCol
Range("M" & R_Out & ":O" & R_Out).Value = Array(Cells(R, 1), Cells(1, C), Cells(R, C))
R_Out = R_Out + 1
Next C
R = R + 1
Loop

End Sub

RE: Excel Transform data for database import

You have also changed the name of the Sub, not fair... lol
Your lastCol is fine, as long as it does not overlap column M.
It would be safer to create the output on another Worksheet, something like:

CODE

...
Sheets(2).Range("A" & R_Out & ":C" & R_Out).Value = _
Array(Sheets(1).Cells(R, 1), Sheets(1).Cells(1, C), Sheets(1).Cells(R, C))
... 

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close