How to split text file by character spaces...

[kkmoslephour]

Hi all,

I am trying to read in a text file and split it by character spaces and output to excel sheet, right now I can read in the text file, but I am having problem splitting it into an array by character spaces. Here is my vba code:

Sub ParseText()




Dim myFile As String, text As String, textline As String


myFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")
MsgBox myFile
If myFile <> "False" Then
    MsgBox "Opening " & myFile
End If




Open myFile For Input As #1


Do Until EOF(1)
    Line Input #1, textline
    MsgBox textline
    text = text & textline
    MsgBox text
Loop


Close #1


End Sub

below is a portion of my text file (as you can see the text file spacing isn't consistent)

DESCRIPTION C.QTY R.QTY H/W P/N SW Ver SW P/N CONFIG
==============================================================================
44 XXX XXX XXX 2 2 XX-XX8888-88 01.25.00.7MEI4B-8888-8888888888E
44 YYY YYYYYY YYY 2 NULL 01.05.00.7MEI4D-9999-4001NULL
44 ZZZ ZZZZ ZZZZ ZZZZ000 00 NULL 01.02.00.7MEI00-0000-0000NULL

 

[kkmoslephour]

For some reason my text files are all in A1. It is treating the whole text file as one line. When I open the text file in notepad++ they are nicely lined, but when I open it in notepad then they will be all cramped in one line.

 

[Stildawn]

Open the txt file directly in Excel, what does it look like then? Usually in my experience excel will find the line breaks and have them each on their own row.

 

[kkmoslephour]

Hi Stildawn,

Sorry for the long delay, I finally figured out the linebreaks right now I can parse my text file into the columns I want it to be parsed to, however, this is only for the first line item I will be needing it for all my line items. So I will use the for loop you suggested before? This is what I have for just the first line of my textfile:

Let me know what you think, I will need a for loop to loop through all the lines and input them into each designated cell.

Do While Not EOF(1)
    Line Input #1, textline
    data = Split(textline, vbLf)
    'MsgBox data(0)
Loop

Dim datePos, firstItemPosName, firstItemPosVer, firstOPS As Integer
Dim secondOPS As Integer
Dim secondItemPosName


'I want to do below code block for 'n' number of lines, right now it works for the first line....

'Extracting first Item from text...
firstItemPosName = InStr(data(4), "44") '1
'MsgBox "First Item number starts at: " & firstItemPosName
firstOPS = InStr(data(4), "OPS") '18
'MsgBox "First item number ends at: " & firstOPS + 2
'MsgBox Left(data(4), firstOPS + 2)
Range("A1").Value = Left(data(4), firstOPS + 2)
firstItemPosName = InStr(data(4), ".") - 2
'MsgBox "First Item number starts at: " & firstItemPosName
Range("B1").Value = Mid(data(4), firstItemPosName, 10)
firstItemPosName = InStr(data(4), "RD")
MsgBox "First Item number starts at: " & firstItemPosName
If firstItemPosName = 0 Then
    Range("C1").Value = "NULL"
End If
firstItemPosName = InStr(data(4), "MEI")
Range("D1").Value = Mid(data(4), firstItemPosName, 15)