Vertical List to Horizontal Database

[GradyLorenzo]

I have a list formatted as follows:

John Doe
someStreet
someZip
someState

Jane Doe
someStreet
someZip
someState

Jack Doe
someStreet
someZip
someState

How would I convert this into a format that can be imported into PHP MyAdmin? I would type it one at a time, but the 60k+ entries is a bit intimidating for obvious reasons...

 

[GradyLorenzo]

Removed file for security purposes.

That is a screenshot of the basic layout of the database

 

[GradyLorenzo]

#shameful bump#

 

[GradyLorenzo]

Breakthrough. I found out that OpenOffice can "paste special" similar to Excel. If someone can write a macro to transpose 5 rows at a time, that would work perfectly. There are 60k+ records to transpose, so don't worry about optimization. I'll have to let it go for a while anyways.

 

[bigburgo]

Hi I have a the same need for a macro,

MrKowz, how can you get this so that it does not leave a line between each address once converted.

 

[MrKowz]

The code I provided should be working for both of your needs.

Data before running code:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">JOHN DOE</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">SOME ADDRESS</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">SOME CITY, ST, SOMEZIP</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">SOMEPHONE</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">JANE DOE</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">SOME ADDRESS</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">SOME CITY, ST, SOMEZIP</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">SOMEPHONE</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br />

Data after running code:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">JOHN DOE</td><td style=";">SOME ADDRESS</td><td style=";">SOME CITY, ST, SOMEZIP</td><td style=";">SOMEPHONE</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">JANE DOE</td><td style=";">SOME ADDRESS</td><td style=";">SOME CITY, ST, SOMEZIP</td><td style=";">SOMEPHONE</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet2</b></th></tr></td></thead></table><br /><br />

Code used:

Public Sub GradyLorenzo()
Dim i   As Long, _
    j   As Long, _
    LR  As Long
    
LR = Range("A" & rows.Count).End(xlUp).row
Application.ScreenUpdating = False
For i = 1 To LR Step 5
    Application.StatusBar = "Currently on row " & i & " of " & LR
    Range(Cells(i + 1, 1), Cells(i + 3, 1)).Copy
    Range("B" & i).PasteSpecial Paste:=xlPasteValues, Transpose:=True
    Range(Cells(i + 1, 1), Cells(i + 3, 1)).ClearContents
Next i
Range("A1:A" & LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub