Transpose table

J

J Shrimps, Jr.

Suppose I have imported a table from
a log output with a sample like this:

fluxcapacitor .80
widgit in stock
flibertygidit 3.2
(blank record)
fluxcapacitor 1.10
widgit ordered
flibertygidit 2.3
(blank record)
Fuxcapacitor 4.01
widgit pending
flibertygidit .734
(blank record)

-There are about a dozen repeating metrics from
the log file separated by a blank record.

I'm trying to build a table like this:

Record# fluxcapacitor widgit flibertygidit
1 .80 in stock 3.2
2 1.1 ordered 2.3
3 4.01 pending 7.34

Is this possible in a series of queries?
 
D

Douglas J. Steele

There might be some third party programs available that could help you, but
it's also not that difficult to do using VBA.

Something along the lines of the following untested air-code should work:

Dim intFile As Integer
Dim intFirstSpace As Integer
Dim sngCurrCapacitor As Single
Dim sngCurrFliberty As Single
Dim strCurrEntry As String
Dim strCurrWidgit As String
Dim strBuffer As String
Dim strFile As String
Dim strSQL As String

strFile = <full path to file to be read>
intFile = FreeFile()
Open strFile For Input As #intFile
Do While EOF(intFile) = False
Line Input #intFile, strBuffer
' Check if it's the blank line.
' If it is, you should have all of the values you
' need stored in the 3 Curr variables
If Len(strBuffer) = 0 Then
strSQL = "INSERT INTO MyTable " & _
"(fluxcapacitor, widgit, flibertygidit) " & _
"VALUES (" & sngCurrCapacitor & ", " & _
Chr$(34) & strCurrWidget & Chr$(34) & ", " & _
sngCurrFliberty & ")"
CurrentDb.Execute, strSQL, dbFailOnError
Else
' Determine the value at the start of the row
intFirstSpace = InStr(strBuffer, " ")
strCurrEntry = Left$(strBuffer, intFirstSpace - 1)
Select Case strCurrEntry
Case "fluxcapacitor"
sngCurrCapacitor = CSng(Mid$(strBuffer, intFirstSpace))
Case "widgit"
strCurrWidget = Trim$((Mid$(strBuffer, intFirstSpace)))
Case "flibertygidit"
sngCurrFliberty = CSng(Mid$(strBuffer, intFirstSpace))
End Select
End If
Loop

This assumes that record number is an AutoNumber field. If not, you can keep
track of what record you're currently reading, and add that to the SQL
statement.
 
Top