Combine First Two rows in text file

Hi,

I have a text file where I want to combine the first and second row into one row. How do I do it using the
Do Until objFile.AtEndOfStream
Loop



Thanks
SampleData.txt
what-is-needed.png
RayneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RayneAuthor Commented:
I don't want to open the file in excel via [workbooks.open] method since opening it in excel gives the text file some weird formatting things that cause issues downstream so I would stick with the objFile.AtEndOfStream thing
0
tuviCommented:
Is it only 3 columns?
Also, you want to output on the same sheet with original data and new data as in the picture?
With the formats (line and color, etc...) ??
0
als315Commented:
You can use code like this:
For i = 1 To 3
    Cells(1, i) = Cells(1, i) & " " & Cells(2, i)
Next i
Rows(2).Delete

Open in new window

Correct row numbers if it is not 1 and 2

If you like to do it in text file, show your code
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Martin LissOlder than dirtCommented:
Run the MergeFirstTwoRows macro.

Sub MergeFirstTwoRows()
Dim FF As Integer
Dim lngNextRow As Long
Dim strline As String
Dim bMerged As Boolean

FF = FreeFile

' Change this line
Open "C:\Solutions\SampleData.txt" For Input As #FF

Do While Not EOF(FF)
    Line Input #FF, strline
    Select Case lngNextRow
        Case 0, Is > 1
            If Not bMerged Then
                lngNextRow = lngNextRow + 1
            End If
            bMerged = False
            WriteData strline, lngNextRow
        Case Else
            WriteData strline, lngNextRow, True
            lngNextRow = lngNextRow + 1
            bMerged = True
    End Select
Loop

Close
End Sub
Private Sub WriteData(strline As String, lngRow As Long, Optional bMerge As Boolean = False)
Dim strParts() As String
Dim intPart As Integer

strParts = Split(strline, vbTab)
With ActiveSheet
    For intPart = 0 To UBound(strParts)
        If Not bMerge Then
            .Cells(lngRow, intPart + 1) = strParts(intPart)
        Else
            .Cells(lngRow, intPart + 1) = .Cells(lngRow, intPart + 1) & " " & strParts(intPart)
        End If
    Next
End With

End Sub

Open in new window

0
RayneAuthor Commented:
Hi Martin,

I tried your code on the sample file I have attached previously and it didn't work
0
RayneAuthor Commented:
Martin, your code is not working or showing the final result. Have you tested it on the sampledata.txt?
0
RayneAuthor Commented:
Hi Tuvi,

for now, its only 3 columns but that can change in the long-term
It can be the same file that has the intended change or new file, it doesn't matter
0
tuviCommented:
Ok, here is the code:

Const MAIN_SHEET_NAME As String = "Sheet1"

Private Function GetColumnCount(ByVal Line As String) As Integer
   Dim Pos As Integer
   Dim Count As Integer
   
   Count = 0
   Pos = InStr(1, Line, vbTab)
   While Pos > 0
      Count = Count + 1
      Line = Right(Line, Len(Line) - Pos)
      Pos = InStr(1, Line, vbTab)
   Wend
   
   GetColumnCount = Count + 1
End Function

Private Sub Extract(ByVal Line As String, Part() As String, TotalCol As Integer)
   Dim Pos As Integer
   Dim Index As Integer
   
   For Index = 1 To TotalCol - 1
      Pos = InStr(1, Line, vbTab)
      Part(Index) = Left(Line, Pos - 1)
      Line = Right(Line, Len(Line) - Pos)
   Next Index
   Part(TotalCol) = Line
End Sub

Public Sub Combine()
   Dim objSys As Scripting.FileSystemObject
   Dim objFile As Scripting.File
   Dim objStream As Scripting.TextStream
   Dim Line As String
   Dim Part() As String
   Dim Extra() As String
   Dim Index As Integer
   Dim MainSheet As Worksheet
   Dim TotalCol As Integer
   Dim Row As Integer
   
   Set MainSheet = ThisWorkbook.Worksheets(MAIN_SHEET_NAME)
   
   Set objSys = New Scripting.FileSystemObject
   Set objFile = objSys.GetFile("D:\Documents\Experts Exchange\SampleData.txt")
   Set objStream = objFile.OpenAsTextStream(ForReading)
   
   Line = objStream.ReadLine
   TotalCol = GetColumnCount(Line)
   
   ReDim Part(TotalCol)
   ReDim Extra(TotalCol)
   
   Extract Line, Part, TotalCol
   Line = objStream.ReadLine
   Extract Line, Extra, TotalCol
   For Index = 1 To TotalCol
      MainSheet.Cells(1, Index) = Part(Index) & " " & Extra(Index)
   Next Index
   
   Row = 2
   Do Until objStream.AtEndOfStream
      Line = objStream.ReadLine
      Extract Line, Part, TotalCol
      For Index = 1 To TotalCol
         MainSheet.Cells(Row, Index) = Part(Index)
      Next Index
      Row = Row + 1
   Loop
   objStream.Close
End Sub

Open in new window



The data will be display in Sheet1.... if you want in another sheet, just redefine MAIN_SHEET_NAME at the top.....
0
Bill PrewCommented:
Here is a stand alone VBS script you can try, no need for Excel.  Save as a VBS file and run as:

cscript EE29101666.vbs SampleData.txt out.txt

Option Explicit

' Define needed I/O constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Define global variables
Dim objFSO
Dim strInFile, strOutFile
Dim objInFile, objOutFile
Dim strLine, arrFirst, arrSecond, intLine, i

' Create filesystm object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Get parms from command line
If (WScript.Arguments.Count < 2) Then
    WScript.Echo "Usage: " & Wscript.ScriptName & " <input-file> <output-file>"
    WScript.Quit
Else
    strInFile = objFSO.GetAbsolutePathname(WScript.Arguments(0))
    strOutFile = objFSO.GetAbsolutePathname(WScript.Arguments(1))
End If

' Make sure input file exists
If Not objFSO.FileExists(strInFile) Then
    Wscript.Echo "*ERROR* Input file does not exist. (" & strInFile & ")"
    Wscript.Quit
End If

' Open input and output files
Set objInFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
Set objOutFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)

intLine = 0

' Loop through all input file lines
Do While objInFile.AtEndOfStream <> True
    ' Read next line
    strLine = objInFile.ReadLine

    intLine = intLine + 1
    Select Case intLine
    Case 1
    	arrFirst = Split(strLine, vbTab)
    Case 2
        arrSecond = Split(strLine, vbTab)
        For i = 0 to UBound(arrFirst)
            arrFirst(i) = arrFirst(i) & " " & arrSecond(i)
        Next
    	objOutFile.WriteLine Join(arrFirst, vbTab)
    Case Else
    	objOutFile.WriteLine strLine
    End Select   
Loop

' Close input and output files
objInFile.Close
objOutFile.Close

Open in new window


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
oBdACommented:
VBScript is so unbelievably ... clunky.
For the fun of it, a PowerShell version; save wherever as Whatever.ps1 and run from a PS console as
.\Whatever.ps1 -Path .\SampleData.txt -Destination .\out.txt
-Destination ... is optional; if you don't pass it, the file will be saved with the original file name plus "_out" (SampleData_out.txt, for example).
If PowerShell nags you that the script isn't signed, enter
Set-ExecutionPolicy RemoteSigned
and try again.
Param([Parameter(Mandatory=$true)]$Path, $Destination)
If (-not (Test-Path -Path $Path -PathType Leaf)) {Throw "Input file '$($Path)' not found"}
If (-not $Destination) {$Destination = $Path -replace "(\$([IO.Path]::GetExtension($Path))$)", '_out$1'}
$first, $second = Get-Content -Path $Path -TotalCount 2
$(For ($i = 0; $i -lt $first.Split("`t").Count; $i++) {$first.Split("`t")[$i], $second.Split("`t")[$i] -join ' '}) -join "`t" | Set-Content -Encoding UTF8 -Path $Destination
Get-Content -Path $Path | Select-Object -Skip 2 | Add-Content -Encoding UTF8 -Path $Destination

Open in new window

0
Martin LissOlder than dirtCommented:
Martin, your code is not working or showing the final result. Have you tested it on the sampledata.txt?
Here's a workbook that uses your file and seems to work for me.
29101666.xlsm
0
RayneAuthor Commented:
Thank you, everyone, for chipping in. you all are a lifesaver. Thank you
0
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
              Experts Exchange Top Expert VBA (current)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Shell Scripting

From novice to tech pro — start learning today.