Transpose data on import

  • Thread starter Thread starter a.t.brooks
  • Start date Start date
A

a.t.brooks

I have a program which generates a tab delimited text file in a column
format (see below)

ID 12345 12346
12347
Username Joe Bloggs Riley Briggs Liam
Gallagher
Noise 2.1 2.3
1.8
Background 62.1 43.1
51.2
Date 01/08/2006 31/07/2006
31/07/2006

The number of colums varies between 2 (where column1 is the headers and
colum 2 is the data) and 25 (1 header and 24 data sets).

I would like to import these into access without having to go through
excel and transpose first so that the data is in rows, not columns
e.g....

ID Username Noise Background
Date
12345 Joe Bloggs 2.1 62.1
01/08/2006
12346 Riley Briggs 2.3 ......
etc

Can this be done? or more importantly, can this be done easily? As the
txt file is generated automatically, I would prefer not to go through
excel, but at the moment this is only the option.
 
?You want Access to "know" that a new row should be started, as it sifts
through the import file? How do YOU know it should be? If you can answer
that, one approach would be to write a procedure that steps through each
incoming row, field by field, and writes the appropriate values to the
appropriate fields and starts over at the appropriate place.

If you aren't up for that much coding, consider using Excel's Transpose
function before importing the data.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Something like this (which needs testing and error-trapping /
bombproofing):


Sub trFile349()
'Demo procedure to transpose tab-delimited text file
Dim lngFIn As Long, lngFOut As Long
Dim InFile As String, OutFile As String
Dim S As String
Dim LineItems As Variant
Dim Lines As New Collection
Dim lngFields As Long, lngRecords As Long

InFile = "C:\Temp\ToTranspose.txt"
OutFile = "C:\Temp\Transposed.txt"

lngFIn = FreeFile()
Open InFile For Input As #lngFIn


'read file into collection of arrays
'each array contains a row of the original
Do Until EOF(lngFIn)
Line Input #lngFIn, S
LineItems = Split(S, Chr(9))
Lines.Add LineItems
Loop

Close #lngFIn
lngFOut = FreeFile()
Open OutFile For Output As #lngFOut

'Loop through collection for each element
'of the arrays to assemble the transposed rows
For lngRecords = 0 To UBound(LineItems)
S = ""
For lngFields = 1 To Lines.Count
S = S & Lines(lngFields)(lngRecords) & vbTab
Next
S = Left(S, Len(S) - 1) ' & vbCrLf
Print #lngFOut, S
Next
Close #lngFOut

End Sub
 
Back
Top