Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text file into columns
contents of the text file
Name = *"dev"/"null" CodeVersion = ReleaseID = need to make col1 Name col2 CodeVersion col3 Releaseid using macro Need some help |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text file into columns
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" wrote in message ... contents of the text file Name = *"dev"/"null" CodeVersion = ReleaseID = need to make col1 Name col2 CodeVersion col3 Releaseid using macro Need some help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text file into columns
On May 16, 2:18 pm, "Rick Rothstein \(MVP - VB\)"
wrote: 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" wrote in message ... contents of the text file Name = *"dev"/"null" CodeVersion = ReleaseID = need to make col1 Name col2 CodeVersion col3 Releaseid using macro Need some help Name=Microsoft Excel Codeversion=WIndows 2003 Releaseid=22 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text file into columns
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" wrote in message ... On May 16, 2:18 pm, "Rick Rothstein \(MVP - VB\)" wrote: 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" wrote in message ... contents of the text file Name = *"dev"/"null" CodeVersion = ReleaseID = need to make col1 Name col2 CodeVersion col3 Releaseid using macro Need some help Name=Microsoft Excel Codeversion=WIndows 2003 Releaseid=22 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text file into columns
On May 16, 2:39 pm, aquaflow wrote:
On May 16, 2:18 pm, "Rick Rothstein \(MVP - VB\)" wrote: 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" wrote in message ... contents of the text file Name = *"dev"/"null" CodeVersion = ReleaseID = need to make col1 Name col2 CodeVersion col3 Releaseid using macro Need some help 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text file into columns
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text file into columns
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 "Rick Rothstein (MVP - VB)" wrote in message ... 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" wrote in message ... On May 16, 2:18 pm, "Rick Rothstein \(MVP - VB\)" wrote: 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" wrote in message ... contents of the text file Name = *"dev"/"null" CodeVersion = ReleaseID = need to make col1 Name col2 CodeVersion col3 Releaseid using macro Need some help Name=Microsoft Excel Codeversion=WIndows 2003 Releaseid=22 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert 3 columns into 1, as text. | Excel Discussion (Misc queries) | |||
Convert Text to Columns | Excel Worksheet Functions | |||
Convert text to columns | Excel Worksheet Functions | |||
Convert text to columns | Excel Discussion (Misc queries) | |||
Macro for Text to Columns - convert to text or general | Excel Programming |