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?
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
- It assumes that all rows in the data file are comma seperated
- Includes no error handling
- Puts the data in the active sheet, starting at cell A1 and overwrites any existing data
- Hard coded file name and location - change to suit or add a GetFile dialog