Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a text file with many more columns in it than are allowed in Excel, so I would like to transpose the data into columns while importing it into Excel. Any ideas on how I can do this?

share|improve this question
add comment

1 Answer

EDIT found a better way, so updated code

Probably best to write a VBA macro to do the import.

Here's a starter for you. Note that it will need some adjusting to suit you data.

Sub ImportTranspose()
    Dim fso As New FileSystemObject
    Dim txtFile As TextStream
    Dim col As Long
    Dim dat As Variant
    Dim sh As Worksheet

    Set sh = ActiveSheet
    Set txtFile = fso.OpenTextFile("C:\Tmp\SampleData.txt")
    col = 1
    Do While Not txtFile.AtEndOfStream
        dat = Application.Transpose(Application.Index(Split(txtFile.ReadLine, ","), 1, 0))
        sh.Cells(1, col).Resize(UBound(dat), 1) = dat
        col = col + 1
    Loop

CleanUp:
    On Error Resume Next
    txtFile.Close
    Set txtFile = Nothing
    Set fso = Nothing
End Sub

Notes:

  1. It assumes that all rows in the data file are comma seperated
  2. Includes no error handling
  3. Puts the data in the active sheet, starting at cell A1 and overwrites any existing data
  4. Hard coded file name and location - change to suit or add a GetFile dialog
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.