0

I have a text file that is not in a format that I can use for printing labels. The current format is like this:

DY234-02   0.5   0.5   Qty 6
U21 U12 U14 U28

TR459-09   0.5   0.5   Qty 9
U11 U78 U7 U8 U30 U24

I need the file to end up like this:

DY234-02   0.5   0.5   Qty 6 U21 U12 U14 U28
TR459-09   0.5   0.5   Qty 9 U11 U78 U7 U8 U30 U24

The files contain about 100 lines of this format I have used vbscript to try to get what I need but the format is not much different. If someone could get me pointed in the right direction that would be great. I am open to all other methods for accomplishing this. Thanks

This is my code in vbscript, but is not doing the job correctly:

Const ForReading = 1

Const ForWriting = 2


Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\parse.txt", ForReading)

Do Until objFile.AtEndOfStream

    strLine1 = objFile.ReadLine

    strLine2 = ""

    If Not objFile.AtEndOfStream Then

        strLine2 = objFile.ReadLine

    End If

    strNewLine = strLine1 & strLine2

    strNewContents = strNewContents & strNewLine & vbCrLf

Loop

objFile.Close

Set objFile = objFSO.OpenTextFile("C:\Scripts\B3.txt", ForWriting, True)

objFile.Write strNewContents

objFile.Close
user692942
  • 16,398
  • 7
  • 76
  • 175
Noob2Java
  • 213
  • 1
  • 7
  • 18
  • Is the string you show us in a cell? – FaneDuru Mar 28 '20 at 17:00
  • Would you like (as you say) `U21 U12 U14 U28Qty 6`, or `U21 U12 U14 U28 Qty 6`? Were would you like the result to be returned? – FaneDuru Mar 28 '20 at 17:02
  • I need to lines 1 & 2 combined to the same line. And lines 4 & 5 combined and so on through the entire file. – Noob2Java Mar 28 '20 at 17:06
  • Do you understand that you did not answer any of my two questions...? I cannot help in such a situation. Your comment does not clarify anything. It makes the question more confusing... – FaneDuru Mar 28 '20 at 17:10
  • There is nothing in a cell, this is a text file. A space would be good, but let me edit the question and show how I need the file to look. Sorry for the confusion. **Even better would be having commas where the spaces are. – Noob2Java Mar 28 '20 at 17:16
  • 1
    Is there always a single empty line between each set of two lines? What about the spacing? _your upper line of each pair has strings separated by three space characters, your second by one space character_. Also, somewhat related, is this how you want it to look, `DY234-02 0.5 0.5 Qty 6U21 U12 U14 U28`? Finally, this isn't a code request site, we don't write it for you. On StackOverflow, you post a [mcve] of your coding attempt at performing the task, if it fails to work as written and intended. You mentioned a VBScript, so please remove the [[tag:batch-file]] tag, and post that script. – Compo Mar 28 '20 at 17:21
  • Thanks for that, @Noob2Java. Please note however that you should also confirm that the format of your file to modify is exactly spaced, _lines and strings_, as in your example. It may also be wise to confirm what type of line endings your file uses, and/or how the last line terminates. – Compo Mar 28 '20 at 17:35
  • Does this answer your question? [Read/Parse text file line by line in VBA](https://stackoverflow.com/questions/11528694/read-parse-text-file-line-by-line-in-vba) – user692942 Mar 29 '20 at 09:03

2 Answers2

0

Try next code, please. It is fast due to the fact it reads all the text value at once and drop the result, also at once. Everything is happening in memory.

Sub testSplitTextFile()
 Dim objFSO As Object, objTF As Object, strIn As String, fullFilename As String, retFile As String
 Dim arrIn As Variant, strRet As String, i As Long
  'use here your path
  fullFilename = "C:\Teste VBA Excel\Teste StackOverflow\TestSplit.txt"
  retFile = "C:\Teste VBA Excel\Teste StackOverflow\RetFile.txt"'your path

  Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTF = objFSO.OpenTextFile(fullFilename, 1)
        strIn = objTF.ReadAll 'it reads all the txt file string
    objTF.Close
    arrIn = Split(strIn, vbCrLf) 'it splits the above string on lines
    'Then, it builds a string based on your conditions:
    For i = 0 To UBound(arrIn) - 1
        If arrIn(i) <> "" And arrIn(i + 1) <> "" Then
            strRet = strRet & arrIn(i) & " " & arrIn(i + 1) & vbCrLf
        End If
    Next i
    strRet = left(strRet, Len(strRet) - 1)' it eliminates the last vbCrLf character
    FreeFile 1
    Open retFile For Output As #1
        Print #1, strRet 'it drops, at once the created string
    Close #1
End Sub
FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • thanks for your help. I just tried this code and I get the last portion of the text file in one line but nothing else. – Noob2Java Mar 28 '20 at 17:46
  • @Noob2Java: Did you test it after page refresh? – FaneDuru Mar 28 '20 at 17:52
  • What do you mean by "Page Refresh"? – Noob2Java Mar 28 '20 at 18:05
  • To refresh this (active) page... F5. I mean, I pasted the code and after that I observed that the obtained string was not built using the existing one. I updated the code in two, three minutes, but without refreshing the page you can use the former code version... – FaneDuru Mar 28 '20 at 18:17
  • Ahh ok, yes I did refresh and now I get two lines. My test file has 6 lines total (or 3 groups) – Noob2Java Mar 28 '20 at 18:52
  • @Noob2Java: Ups... Yes, the obtained array is zero based. The iteration should start from zero. I will adapt the code. Adapted... – FaneDuru Mar 28 '20 at 19:09
  • @Noob2Java: Theoretically, using an array and dropping the result at once, it should be the fastest way. But, differences will be observed only at a big file size – FaneDuru Mar 28 '20 at 19:30
  • Thanks for your efforts @FaneDuru I will give this a try. – Noob2Java Mar 28 '20 at 20:45
  • I did try your file and it works great. I would love to know more about how this code works if you have time. I did come across another file type that has one digit on a third line about 80 spaces to the right which leaves no empty row between the groups. This is why I would like to know more about how this works. Thank You! – Noob2Java Mar 31 '20 at 14:46
  • Thank you very much, I appreciate that. – Noob2Java Mar 31 '20 at 14:54
  • @Noob2Java: But, isn't my code faster then the one you marked like accepted answer, which reads and writes each line? – FaneDuru Mar 31 '20 at 14:56
  • It appears that it could be, I just haven't yet tried it on a large file. – Noob2Java Mar 31 '20 at 15:05
  • Could you add a note where the FOR loop starts and how it reads that? – Noob2Java Mar 31 '20 at 15:49
  • `For i = 0 To UBound(arrIn)`. I used `- 1` only for existing code, when needed to check the following array element, twoo. – FaneDuru Mar 31 '20 at 15:53
0

If the format is repeated like this, you can read in the text file line by line, and check if there is data on each line. If so join the data to an output string, otherwise add a carriage return to the output string, before finally outputting it to a new text file. Something like this perhaps:

Dim strInFile As String
Dim strOutFile As String
Dim intInFile As Integer
Dim intOutFile As Integer
Dim strInput As String
Dim strOutput As String
strInFile = "J:\downloads\data-in.txt"
strOutFile = "J:\downloads\data-out.txt"
intInFile = FreeFile
Open strInFile For Input As intInFile
intOutFile = FreeFile
Open strOutFile For Output As intOutFile
Do
    Line Input #intInFile, strInput
    If Len(Trim(strInput)) > 0 Then
        strOutput = strOutput & " " & strInput
    Else
        strOutput = strOutput & vbCrLf
    End If
Loop Until EOF(intInFile)
Print #intOutFile, strOutput
Reset

Regards,

Applecore
  • 3,934
  • 2
  • 9
  • 13