how to convert columns of data to individual records 1

[SusanPedersen]

I have some data that looks like this:

School1 Address 325 High Street
School1 City Dallas
School1 State TX
School1 Zip 75001
School2 Address 995 E 2nd St
School2 City Memphis
School2 State TN
School3 Address East 42nd St.
School3 City New York
School3 State NY
School3 Zip 10002

(Notice that School2 does not have a zip code)
I would like to write a VBA module that converts the "columnar" data into relational database records like this:

School Address City State Zip
1 325 High St Dallas TX 75001
2 995 E 2nd St Memphis TN
3 East 42nd St New York NY 10002

Any help would be most appreciated.

 

[SkipVought]

hi,

What code do you have so far? Where are you stuck?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi Susan,

Assuming your data are in Excel, with three columns as indicated in your post, with only have 5 possible values ("School", "Address", "City", "State", "Zip"), all on the first worksheet and you want the processed data output on the second sheet, try:

Sub Demo()
Application.ScreenUpdating = False
Dim i As Long, j As Long, k As Long
Dim Ws1 As Worksheet, Ws2 As Worksheet
j = 2
With ThisWorkbook
  Set Ws1 = .Sheets(1)
  Set Ws2 = .Sheets(2)
  With Ws2
    .Cells(1, 1).Value = "School"
    .Cells(1, 2).Value = "Address"
    .Cells(1, 3).Value = "City"
    .Cells(1, 4).Value = "State"
    .Cells(1, 5).Value = "Zip"
    .Cells(2, 1).Value = Ws1.Cells(1, 1).Value
  End With
  With Ws1
    For i = 1 To .UsedRange.Rows.Count
      If .Cells(i, 1).Value = Ws2.Cells(j, 1).Value Then
        For k = 2 To 5
          If .Cells(i, 2).Value = Ws2.Cells(1, k).Value Then
            Ws2.Cells(j, k).Value = .Cells(i, 3).Value
            Exit For
          End If
        Next
      Else
        j = j + 1
        Ws2.Cells(j, 1).Value = .Cells(i, 1).Value
        For k = 2 To 5
          If .Cells(i, 2).Value = Ws2.Cells(1, k).Value Then
            Ws2.Cells(j, k).Value = .Cells(i, 3).Value
            Exit For
          End If
        Next
      End If
    Next
  End With
End With
Application.ScreenUpdating = True
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[MajP]

Is this going to end up as a table in a database such as Access eventually or does it remain as a linked table still in Excel?
Because if it goes into say Access you could import it first then pretty much write a few simple queries to normalize the data into a new table. It could be done without any code.

 

[SusanPedersen]

This is going to be an access database and doesn't need to be linked to Excel at all.

I'm intrigued by MajP's solution (no code), but you must know that there are 30 potential fields of data, not just the 4 I've used in the example.

People who answer Forum questions rule!

 

[SusanPedersen]

I should also mention that there are over 1,000 schools. I know I need to loop through the recordset, but what is throwing me is the fact the the data is "Sparse" -- not all of the schools have all of the 30 fields of data.

 

[SkipVought]

Hmmmmm. What are the 30 fields?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

1)import into access
tblExcelData

SchoolName  FieldName     FieldData
School1	  Address         325 High Street
School1	  City            Dallas
School1	  State           TX
School1	  Zip             75001
School2	  Address         995 E 2nd St
School2	  City            Memphis
School2	  State           TN
School3	  Address         East 42nd St.
School3	  City            New York
School3	  State           NY
School3	  Zip             10002

2) make your new table
tblSchoolData
SchoolID (autonumber)
SchoolName (text)
streetAdd
city
state
zipcode
...
etx

3) push each school into new table

INSERT INTO tblSchoolData ( SchoolName )
SELECT DISTINCT tblExcelData.SchoolName
FROM tblExcelData;

4) start pushing different fields

UPDATE 
 tblSchoolData 
INNER JOIN 
 tblExcelData ON tblSchoolData.schoolName = tblExcelData.SchoolName 
SET 
 tblSchoolData.[b]streetAdd[/b] = [FieldData]
WHERE 
 (((tblExcelData.FieldName)=[b]"Address"[/b]));

now just start changing names and hit run
to do the zipcode for example
set
tblSchoolData.zipcode = [fieldData]
where
tblExcelData.fieldName = "Zip"

If you are in the query developer it is simply a matter of using a dropdown to change a field and typing in a differnt criteria. Even with 30 fields to update each update would take about 5 seconds to change the field name and criteria and hit run. The changes are in bold

 

[MajP]

Took me actually less than 5 seconds per update to pick a new field and change the criteria.

schoolID schoolName streetAdd        city     state  zip
1        School1    325 High Street  Dallas   TX     75001
2        School2    995 E 2nd St     Memphis  TN 
3        School3    East 42nd St.    New York NY     10002

 

[macropod]

Hi Susan,

Even though your data may end up in Access, providing for extra fields in the code I posted is quite simple - ust keep adding them to the 'With Ws2' ... End With' group and change the 5 in 'For k = 2 To 5' to however many you need.

At the end you'll have a nicely laid out set of data in whatever field order you want.

Cheers
Paul Edstein
[MS MVP - Word]

 

[Andrzejek]

Some assumptions here:

If you data is in a simple text file like this (let's say schools.txt) and columns align nicely:

[tt]
School1 Address 325 High Street
School1 City Dallas
School1 State TX
School1 Zip 75001
School2 Address 995 E 2nd St
School2 City Memphis
School2 State TN
School3 Address East 42nd St.
School3 City New York
School3 State NY
School3 Zip 10002
[/tt]

You could do this:

Option Explicit
Dim strAdd As String
Dim strCit As String
Dim strSta As String
Dim strZip As String
[green]'Add more fields here
[/green]
Sub SomeSubName()
Dim strTextLine As String
Dim strSchool As String

[green]'Set the very first School[/green]
strSchool = "School1"

Open App.Path & "\schools.txt" For Input As #1
Do While Not EOF(1)
    Line Input #1, strTextLine
MyLabel:
    If strSchool = Trim(Left(strTextLine, 10)) Then
        Select Case Trim(Mid(strTextLine, 11, 10))
            Case "Address"
                strAdd = Mid(strTextLine, 21)
            Case "City"
                strCit = Mid(strTextLine, 21)
            Case "State"
                strSta = Mid(strTextLine, 21)
            Case "Zip"
                strZip = Mid(strTextLine, 21)[green]
            'add more cases here[/green]
        End Select
    Else
        Call InsertIntoDB(CInt(Mid(strSchool, 7)))
        strSchool = Trim(Left(strTextLine, 10))
        GoTo MyLabel
    End If
Loop
Close #1

Call InsertIntoDB(CInt(Mid(strSchool, 7)))

End Sub

Private Sub InsertIntoDB(intShoolNo As Integer)
Dim strSQL As String

strSQL = "INSERT INTO MyTable(School, Address, City, State, ZIP) " & _
    "VALUES(" & intShoolNo & ", '" & Replace(strAdd, "'", "''") & "', '" & _
    Replace(strCit, "'", "''") & "', '" & _
    strSta & "', '" & _
    strZip & "')"

Debug.Print strSQL
[green]'DBConn.Execute strSQL[/green]

strAdd = ""
strCit = ""
strSta = ""
strZip = ""
[green]'Clear more fields here[/green]

End Sub

And you will end up with some Insert statements ready to use:
[tt]
INSERT INTO MyTable(School, Address, City, State, ZIP)
VALUES(1, '325 High Street', 'Dallas', 'TX', '75001')

INSERT INTO MyTable(School, Address, City, State, ZIP)
VALUES(2, '995 E 2nd St', 'Memphis', 'TN', '')

INSERT INTO MyTable(School, Address, City, State, ZIP)
VALUES(3, 'East 42nd St.', 'New York', 'NY', '10002')
[/tt]

(I know there is GoTo statement that some people look down on and I am not crazy about it myself, but since it is a simple and small piece of code - forgive me for using it )

Have fun.

---- Andy