Export from Table..with a twist

JVM · January 23rd, 2009, 09:44 PM

I wrote a database to help out at the school I teach. The administration software with which I need to connect is very picky. I have a table that looks like this:

Code:

ID     CN
13     eng1001
210    sci1234
13     sci431
19     lang1111
210    lang 1111
13     math1234

etc. ID is the student ID, CN is the course number (alphanumeric) that students requested. I need to dump it into a tab delimted table that looks like this:

Code:

13   eng1001   sci431   math1234
210  lang1111  sci1234
19   lang1111

each student will have from 1 to 8 different courses that all need to appear on the same line. A dump directly to tab-delimited or excel would work.
Any ideas? Thought perhaps I needed a nested loop

thanks in advance for any help you can offer!
Joe

mmcdonal · January 27th, 2009, 09:37 AM

I have helped people do this in the past. Here is some psuedo code off the top of my head:

Create text file
Close text file
Open text file for Writing (.csv)

Create recordset on table (SELECT DISTINCT ID FROM YourTable)
rs.MoveFirst
Do Until rs.eof
sID = rs("ID")
sString = sID
Create recordset (rs1) on table again (SELECT ID, CN FROM YourTable WHERE ID = sID)
Do Until rs1.eof (there will always be at least one record)
sString = sString & vbTab & rs("CN")
rs1.MoveNext
Loop
objFile.WriteLine sString

rs.MoveNext
Loop

rs,Close
rs1.Close
objFile.Close

This will take this data:

ID CN
1 eng101
1 eng102
2 math101
2 math102

and make this:

1 eng101 eng102
2 math101 math102

in a csv file.

How much of the code do you need?

mmcdonal · January 27th, 2009, 09:59 AM

Here is the code that I came up with to do this:

Code:

Dim sString As String
Dim rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim sSQL As String
Dim sSQL1 As String
Dim lID As Long
Dim objFile As Variant
Dim objFSO As Variant
Const ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("C:\SampleTextFile.csv")
objFile.Close
Set objFile = objFSO.OpenTextFile("C:\SampleTextFile.csv", ForAppending)
 
sSQL = "SELECT DISTINCT ID FROM Table1 ORDER BY ID"
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rs.MoveFirst
Do Until rs.EOF
    lID = rs("ID")
    sString = lID
    sSQL1 = "SELECT * FROM Table1 WHERE [ID] = " & lID & " ORDER BY [CN]"
    Set rs1 = New ADODB.Recordset
    rs1.Open sSQL1, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    Do Until rs1.EOF
        sString = sString & ", " & rs1("CN")
 
    rs1.MoveNext
    Loop
        objFile.WriteLine sString
        sString = ""
rs.MoveNext
Loop

This is the starting data:

Table1
ID CN
1 eng101
1 eng102
2 math101
2 math102
2 eng101
3 fren101
3 math101
4 bus101
4 span101

This is the result in Excel:

1 eng101 eng102
2 eng101 math101 math102
3 fren101 math101
4 bus101 span101

Well, each one of those is in an individual cell. I use comma delimited since tabs were not working correctly.

Did that help?

JVM · January 30th, 2009, 09:52 PM

Fantastic..JUST what I needed.
I really appreciate the help!
Joe