Convert text file into columns

[aquaflow]

contents of the text file
Name =
*"dev"/"null"
CodeVersion
=
ReleaseID
=

need to make col1 Name col2 CodeVersion col3 Releaseid using macro

Need some help

 

[Rick Rothstein \(MVP - VB\)]

Is that actual data you showed us? If so, I find it confusing as to what you
want. Do you want the word "Name" without the trailing equal sign in Column
A? Are we to skip over the *"dev"/"null" part? The other equal signs are on
lines by themselves? Anything you can give us to clarify the **actual** data
in the text file would be useful. Also, what is the approximate size of the
largest text file you would ever expect to process (in Kilobytes or
Megabytes)?

Rick

 

[aquaflow]

Is that actual data you showed us? If so, I find it confusing as to what you
want. Do you want the word "Name" without the trailing equal sign in Column
A? Are we to skip over the *"dev"/"null" part? The other equal signs are on
lines by themselves? Anything you can give us to clarify the **actual** data
in the text file would be useful. Also, what is the approximate size of the
largest text file you would ever expect to process (in Kilobytes or
Megabytes)?

Rick

Name=Microsoft Excel
Codeversion=WIndows 2003
Releaseid=22

 

[Rick Rothstein \(MVP - VB\)]

I wish you had answered my question about the size of the file... the answer
might affect the code I would give you. Here I am assuming your file is
around 10 to 15 Megs maximum (the code will still work, but might start
becoming inefficient if the file gets much larger than this limit). Change
my worksheet reference in the Set statement, the filename in the Open
statement and the starting row to put data in in the RowNum assignment
(immediately before the For statement) to match your own situation.

Sub LoadFile()
Dim X As Long
Dim RowNum As Long
Dim FileNum As Long
Dim WS As Worksheet
Dim TotalFile As String
Dim Records() As String
Set WS = Worksheets("Sheet4")
FileNum = FreeFile
Open "c:\temp\test\datafile.txt" For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Records = Split(TotalFile, vbCrLf)
RowNum = 2
For X = 0 To UBound(Records)
WS.Cells(1 + Int(X / 3), 1 + X Mod 3).Value = Records(X)
Next
End Sub

Rick

 

[aquaflow]

Name=Microsoft Excel
Codeversion=WIndows 2003
Releaseid=22

Here is the text file contents (total size is 4KB)
Name =
FAULTLOGSUPPORT
CodeVersion =
52.1
ReleaseID = (52.189.8000)

I want to create Col 1 Col 2 Col3
Name CodeVersion Releaseid

It is better to have a batch file to create a column based excel
output or a macro

Thanks for your help

 

[Rick Rothstein \(MVP - VB\)]

Here is the text file contents (total size is 4KB)

Name =
FAULTLOGSUPPORT
CodeVersion =
52.1
ReleaseID = (52.189.8000)

Here you are showing your data separated on different lines again. The
actual layout of the file is critical to being able to read it into your
worksheet. The code I gave you earlier assumes your file looks like you
responded the first time to me, namely...

Name = FAULTLOGSUPPORT
CodeVersion = 52.1
ReleaseID = (52.189.8000)

Rick

 

[Rick Rothstein \(MVP - VB\)]

Sorry, I forgot to parse out the stuff to the left of the equal signs. Use
this code instead...

Sub LoadFile()
Dim X As Long
Dim RowNum As Long
Dim FileNum As Long
Dim WS As Worksheet
Dim TotalFile As String
Dim Records() As String
Set WS = Worksheets("Sheet4")
FileNum = FreeFile
Open "c:\temp\test\datafile.txt" For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Records = Split(TotalFile, vbCrLf)
RowNum = 2 'The starting row for data placement
For X = 0 To UBound(Records)
WS.Cells(RowNum + Int(X / 3), 1 + X Mod 3).Value = _
Trim(Split(Records(X), "=")(1))
Next
End Sub

Rick