CODEWITHSUNDEEP
vbaexceldelimiter
GrahamD
GrahamD

Reputation: 1

How to read a delimited text file and export the data into individual columns of an excel sheet in VBA

The file that I am importing consists of tabs and commas, which will both need to be excluded from the the new excel sheet. I can't exactly figure this out and this is the code I have stared to use so far....Any help would be appreciated

Sub DelimitedTextFileToArray()
Dim Delimiter As String
Dim TextFile As Integer
Dim FilePath As String
Dim FileContent As String
Dim LineArray() As String
Dim DataArray() As String
Dim TempArray() As String
Dim rw As Long, col As Long

Delimiter = ","'I just used the comma as a test for the delimiter
FilePath = "C:\FILENAME.txt"
rw = 0

TextFile = FreeFile
Open FilePath For Input As TextFile

FileContent = Input(LOF(TextFile), TextFile)


Close TextFile


LineArray() = Split(FileContent, vbCrLf)

For x = LBound(LineArray) To UBound(LineArray)
  If Len(Trim(LineArray(x))) <> 0 Then
      TempArray = Split(LineArray(x), Delimiter)

    'Determine how many columns are needed
      col = UBound(TempArray)

    'Re-Adjust Array boundaries//Ive had problems debugging @ this step and beyond
      ReDim Preserve DataArray(col, rw)

    'Load line of data into Array variable
      For y = LBound(TempArray) To UBound(TempArray)
        DataArray(y, rw) = TempArray(y)
      Next y
  End If

    rw = rw + 1

Next x

End Sub

Upvotes: 0

Views: 48

Answers (1)

Sam
Sam

Reputation: 5721

Replace this line

TempArray = Split(LineArray(x), Delimiter)

with

TempArray = Split(Replace(LineArray(x), vbTab, Delimiter), Delimiter)

That will make every tab a comma prior to splitting

Upvotes: 1

Related Questions