Macro to remove spaces in note pad

[Gaurang Mhatre]

Dear Friends,

I have one query on Notepad. Yes, I am aware that this is an excel forum.

But, I have few doubts and hoping that excel macro can solve it.

I have note pad which contains space as it is automatically generated through SAP. It is tilde separated text file. This text file is payment file and same is uploaded in our bank portal for processing.

Now client is generating this text file through ERP and it comes with spaces.
Clients ERP does not trim spaces (which are before and after tilde). Thus, he have asked us to bring up with solution which would trim those spaces and gets uploaded in bank portal for payment processing.


Now, my question is will excel macro work, by removing space. Or there is separate converter all together. Instead of manually removing spaces in note pad. Is there any other alternative solution?

Attach is note pad which is generated through SAP and contains spaces. All i want is to remove extra spaces which is coming before and after ~ (tilde).

Thanks in advance for your help.


Regards,
Gaurang Mhatre

 

[shrivallabha]

Can you post processed result file for above posted sample? Will this be single text file or there would be several text files in the folder which need to be processed?

VBA can handle this.

 

[jindon]

See if this is how you wanted.

Sub test()
    Dim fn As String, txt As String
    fn = Application.GetOpenFilename("TextFiles,*.txt")
    If fn = "False" Then Exit Sub
    txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = " *(~+) *"
        txt = .Replace(txt, "$1")
    End With
    Open Replace(fn, ".txt", "_trimmed.txt") For Output As #1
        Print #1, txt
    Close #1
End Sub

 

[Gaurang Mhatre]

Thanks for taking out time Shrivallabha.

I have attached result file which we want. (Payment file without space) This I have manually removed spaces.
File which client will be generating through SAP system. (Attachment- Payment file with spaces) Thus, contents in text file will vary depending on how many payment he wants to do in a day. There will be only one file generated in which all details will be inputted. But it depends on client & payment details in text file will vary. But, it will include spaces.
Manually removing spaces before and after tilde (~) will be tedious and our main aim is to remove those spaces because with space file is not getting uploaded in our bank portal.

Thus, Macro to require those space before and after tilde.

Trust, this helps!



Thanks!
Regards,
Gaurang M

 

[Marc L]

Hi !

Another way (Roma classical), output file with a space before .txt :​

Sub Demo()
     Const FOLDER = "D:\Tests4Noobs\"
    If Dir(FOLDER) > "" Then ChDrive FOLDER: ChDir FOLDER
       FILE = Application.GetOpenFilename("Text Files, *.txt")
    If FILE = False Then Exit Sub
    FF% = FreeFile
    Open FILE For Input As #FF:  SPQ = Split(Input(LOF(FF), #FF), "~"):  Close #FF
    For R& = 0 To UBound(SPQ):  SPQ(R) = Trim(SPQ(R)):  Next
    Open Replace(FILE, ".txt", " .txt") For Output As #FF:  Print #FF, Join$(SPQ, "~");:  Close #FF
End Sub

Do you like it ? So thanks to click on bottom right Like !

Notice this can be done without Excel directly via a VBScript file …
​

 

[shrivallabha]

Jindon & Marc have already posted a solution. Here's one more. This can process one or many files. Go through comments and change it to suit your environment.

Public Sub ProcessFiles()
Dim objFSO As Object 'FileSystemObject
Dim objFld As Object 'Folder
Dim objFil As Object 'File
Dim objtxt As Object 'TextStream
Dim strFolder As String, strNewName As String, strOut As String
Const ForAppending = 0, ForReading = 1, ForWriting = 2

strFolder = "C:\Temp" '\\ Change Path to suit
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFld = objFSO.GetFolder(strFolder)

'\\ Loop through files in the folder
For Each objFil In objFld.Files
    '\\ Checks if it is text file and non processed one
    If objFil.Type = "Text Document" And InStr(objFil.Name, "_Out.txt") = 0 Then
        strNewName = Replace(objFil.Path, ".txt", "_Out.txt")
        Set objtxt = objFSO.OpenTextFile(objFil.Path, ForReading, False)
        strOut = Join(Application.Trim(Split(objtxt.ReadAll, "~")), "~")
        objtxt.Close
        Set objtxt = objFSO.CreateTextFile(strNewName, True)
        objtxt.Write strOut
        objtxt.Close
    End If
Next

'\\ Release objects
Set objtxt = Nothing: Set objFil = Nothing: Set objFld = Nothing: Set objFSO = Nothing

End Sub

 

[Marc L]

Yes, it's better with Excel Trim than VBA Trim, my previous code amended :​

Sub Demo()
     Const FOLDER = "D:\Tests4Noobs\"
    If Dir(FOLDER) > "" Then ChDrive FOLDER: ChDir FOLDER
         V = Application.GetOpenFilename("Text Files, *.txt")
      If V = False Then Exit Sub
    Open V For Input As #9
    Open Replace(V, ".txt", " .txt") For Output As #8
    Print #8, Join$(Application.Trim(Split(Input(LOF(9), #9), "~")), "~");
    Close #8, #9
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[jindon]

Trim method to the array should be used very carefully.
It will fail when any elements have more than 255 characters...

 

[Gaurang Mhatre]

Thanks Marc, Shri for the help..i will check and confirm shortly.

 

[Marc L]

As you ever no need Excel to mod a text file,
jindon's way is the one to follow for example within a VBScript …
​

 

[Gaurang Mhatre]

Thanks Marc& shrivallabha macro is working fine.
http://chandoo.org/forum/members/shrivallabha.2442/

I have pasted that code in excel and have made a button to run macro so that client can click on button to run macro.


Also, one more thing. Is there a way where file can be choosed from path and then clicked macro run button? Instead of specific folder i.e C drive (if u see in code I have written C Drive folder).


So is there any way that we can put in excel a button where he will be able to choose a folder of his choice where text file is saved.


Many thanks for your help earlier.


Regards,


Gaurang Mhatre.

 

[shrivallabha]

Good to know that you have it working. Jindon's script is already have feature for prompting for file name. Have you tried that one?

Marc's macro will also prompt If the Const directory path is not having any txt files.

 

[jindon]

This one maybe on Mac, so no need to reply, and already put this to my ignore list.

 

[shrivallabha]

Following modifications will prompt you for finding file first and then prompt for save path. You can overwrite as well (i.e. by selecting the source file).

Public Sub ProcessFiles()
Dim objFSO As Object
Dim objtxt As Object
Dim strSourceFile As String, strDestnFile As String, strOut As String
Const ForAppending = 0, ForReading = 1, ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
strSourceFile = Application.GetOpenFilename("Text Files, *.txt")
strDestnFile = Application.GetSaveAsFilename(FileFilter:="Text Files (*.txt), *.txt")
If strSourceFile = "False" Or strDestnFile = "False" Then Exit Sub

Set objtxt = objFSO.OpenTextFile(strSourceFile, ForReading, False)
strOut = Join(Application.Trim(Split(objtxt.ReadAll, "~")), "~")
objtxt.Close
Set objtxt = objFSO.CreateTextFile(strDestnFile, True)
objtxt.Write strOut
objtxt.Close

Set objtxt = Nothing: Set objFSO = Nothing
End Sub

Replace old code with this one.

 

[Gaurang Mhatre]

Thanks Shrivallabha you have been of great help. Just a minor changes i need in macro. i forgt to tell u earlier. We have removed spaces in text file which is correct. Only thing i want to add in this macro.

Below is the payment line in note pad (Dummy record) capital P which is in start means payment line. Tilde is after P.

P~007~29012016~D00000144~BMW~SAMSO S PVT LTD~~2000000003206~NEFT~152/16010185~144/16010185~29012016~1~TUNIP AGRO LTD~SBIN0013604~33000820949~110002497~33000820949~A-10/58, 10/59~MEERUT ROAD IND. AREA~GHAZIABAD, UP ~GHAZIABAD UP~~amit@dky.co.in~~~PPCSUPP~CMS CHQ RET NO.29536 ONQUEST~0~-1730~0~1730~-3460~


Below is Advise line, which starts from C.
C~002~29012016~D00000144~BMW~SAMSONITE SOUTH ASIA PVT LTD~P04/16010003~105453427001~NEFT~144/16010185~123/16010185~29012016~-1~TUNIP AGRO~SBIN0060335~33000820949~110002497~33000820949~A-10/58, 10/59~MEERUT ROAD IND. AREA~GHAZIABAD, UP~GHAZIABAD UP~~amit@dky.co.in~~~PPCSUPP~CMS CHQ RET NO.29536 ONQUEST~0~-1730~0~1730~-3460~


So the point is, we want payment line and advise line seperate. If you see macro removes space due to which payment and advise line are together. Thus, is there any way out where once C starts that part will go below. i mean instead of manually doing "ENTER" after payment line ends (above, payment line ends after 3460~) why not macro do ENTER thing once payment line ends and C (advise part) starts. During trimming space.


Attaching note pad for your reference.

Attach file name - advise line below (in this if u see advise part which starts from C is below payment line)

Attach file name -payment&advise together (macro deletes space but keeps everything together)

Thus, can we seperate payment and advise part as per attached (advise line below text file)

Thanks

Regards,
Gaurang

 

[shrivallabha]

Sorry, example input and output files do not match. You should provide a clear example of before and after.

Input file contains more than one instance of C? Do you want each instance of C to start on new line? Like C, will there be more than one P?

 

[Gaurang Mhatre]

Thanks for the reply.

i have attached one dummy file which is generated through SAP by our customer (text file includes space). Payment (P) and Advise (C) are multiples depending on hw many payments he wants to do and to that there is advise part. So there can be multiple instances. If you see in dummy text file there are multiple payment and advising part. But, since file is generated through SAP system automatically seperates payment and advise part but includes space for which we have made macro.

Now, when we run macro on dummy text file (which is attached).
Macro removes space successfully but joints payment and advising part together which we want to seperate. Like SAP system does.

Removing space is our aim but along with that is it possible to seperate multiple Payment and Advising part. Like the one you see in Dummy text file attached.

Hope this clarifies.

Let me know in case if you have any queries.

Regards,
Gaurang M

 

[shrivallabha]

Please see attached result and if it is what you want. If yes, then replace following line of code:

strOut = Join(Application.Trim(Split(objtxt.ReadAll, "~")), "~")

with

strOut = Replace(Replace(Join(Application.Trim(Split(objtxt.ReadAll, "~")), "~"), "~P~", "~" & vbCrLf & "P~"), "~C~", "~" & vbCrLf & "C~")

 

[Gaurang Mhatre]

Hey Shrivallabha thanks...this is the way i wanted... On monday i will check on portal whether this text file is getting successfully upload or not.
But, main part is done by this macro...

Kudos!
God bless u!

Regards,
Gaurang Mhatre

 

[shrivallabha]

Hey Shrivallabha thanks...this is the way i wanted... On monday i will check on portal whether this text file is getting successfully upload or not.
But, main part is done by this macro...

Kudos!
God bless u!

Regards,
Gaurang Mhatre

Great! Thanks for the feedback.