Importing text file displays as a single row and not a table

**Xcl1** · 12-01-2012, 06:00 AM

This is the text file: Video.txt. I import it using the following settings: "Delimited" as Original data type and "Comma" as Delimiters, with all the other settings left to default. Everything is imported into a single row. It is supposed to be a table, with Stk_No, Title, Certificate, up to In_Stk as column titles; then, all the rest should come below the column titles as rows (they seem to be separated by 5 commas). Am I missing a setting or there is a problem with the text file?

**Special-K** · 12-01-2012, 03:25 PM

There are no carriage return/line feeds in the file.
Everything is separated by commas and commas alone.
So you will indeed end up with one long row.

Suggest you recreate the text file and save the file as csv format rather than txt.

**mike7952** · 12-01-2012, 04:22 PM

A macro can help you here.

Option Explicit
Sub txtVideoFile()
 Dim sFile As String
 Dim FileNum As Integer
 Dim TotalFile As String
 Dim Lines, i As Long, n As Long

 sFile = "C:\Users\Mike\Desktop\Video.txt" '<-- Adjust to you txt files path

 FileNum = FreeFile

 Open sFile For Binary As #FileNum
 TotalFile = Space(LOF(FileNum))
 Get #FileNum, , TotalFile
 Close #FileNum
    
 Lines = Split(TotalFile, ",")
 
 For i = 0 To UBound(Lines) - 1 Step 9
    n = n + 1
    With Cells(n, 1).Resize(, 9)
        .Value = Array(Lines(i), Lines(i + 1), Lines(i + 2), Lines(i + 3), Lines(i + 4), Lines(i + 5), Lines(i + 6), Lines(i + 7), Lines(i + 8))
    End With
 Next
End Sub