Link to home
Start Free TrialLog in
Avatar of fx124
fx124

asked on

Deleting spaces and inserting a comma into it

Hiya Experts, hope you can help!!

I have many text files that look something like this:

BLOGGS   JOE     122334     DINMORE     LONDON
JONES   FRED    122234      TERRACE      BLACKPOOL

These files have thousands of lines in them of similar text, what I want to do is delete all spaces greater than 2 spaces and replace it with a comma (I assume this will make it a CSV file).

Is there any way that I can do this with code.  I would be using Access 97 to write the code in.

Hope this is enough information

Many thanks

Andy
Avatar of Microsoft
Microsoft

Public Function strStripChar(ByVal sString2Search As String, _
                             ByVal sChar2Strip As String) As String
    '
    ' strips a specified char from a string
    '
    Dim nPos As Integer
    Dim sTmpString2Search As String
    sTmpString2Search = sString2Search
   
    Do
        nPos = InStr(1, sTmpString2Search, sChar2Strip)
        If nPos > 0 Then
            sTmpString2Search = _
                  Left(sTmpString2Search, nPos - 1) & _
                  Mid(sTmpString2Search, nPos + 1, _
                  Len(sTmpString2Search) - nPos)
        Else
            strStripChar = sTmpString2Search
            Exit Do
        End If
    Loop
   
End Function
You could try this, this code allows you to state the string you want to search in the text your want to change and the text your want to change to in your case <space> - Comma.




Public Function gfReplaceString(strSearch As String, _
                                      strOld As String, _
                                      strNew As String) As String

    Dim lngFoundPos               As Long
    Dim strReturn                 As String
    Dim strReplace                As String
    Dim strIn                     As String
    Dim strFind                   As String
    Dim lngStartPos               As Long
   
    strIn = strSearch
    strFind = strOld
    strReplace = strNew
    lngFoundPos = 1
    lngStartPos = 1
    strReturn = ""
   
   
    'Process the string while strFind is found
    Do While lngFoundPos <> 0
       
        lngFoundPos = InStr(lngStartPos, strIn, strFind)
       
        'If strFind is found
        If lngFoundPos <> 0 Then
       
            'Take all characters before strFind, add strReplace
            'onto the end, and add this to strReturn
            strReturn = strReturn & _
                        Mid$(strIn, lngStartPos, lngFoundPos - lngStartPos) & _
                            strReplace
                           
        'If no strFind is found
        Else
           
            'Add the remainder of the string to strReturn
            strReturn = strReturn & _
                        Mid$(strIn, lngStartPos)
       
        End If
       
        'Start next search at the first character after the replaced string
        lngStartPos = lngFoundPos + Len(strFind)
           
    Loop
   
    'Return the new string
    gfReplaceString = strReturn
   
       
End Function
not as general a function:

Function Compress(ByVal OriginalString As String) As String
    While InStr(1, OriginalString, "  ")
        OriginalString = Replace(OriginalString, "  ", " ")
    Wend
    Compress = Replace(OriginalString, " ", ", ")
End Function
Function ConvertToCSV(Str1 As String)
 Str1 = Replace(Str1, "  ", ",")
 Str1 = Replace(Str1, " ", "")
 Do While InStr(Str1, ",,") > 0
    Str1 = Replace(Str1, ",,", ",")
 Loop
 ConvertToCSV = Str1
End Function


Cheers.
Function ConvertToCSV(Str1 As String)
Str1 = Replace(Str1, "  ", ",")  '2 spaces
Str1 = Replace(Str1, " ", "")    '1 space
Do While InStr(Str1, ",,") > 0   '2 commas
   Str1 = Replace(Str1, ",,", ",") '2 commas
Loop
ConvertToCSV = Str1
End Function

Cheers.
Note: the comments indicate the parameters used in the corr. lines, to make it clearer.
Avatar of fx124

ASKER

Hell again

Thanks for all the replies I have received so far but where do I put the code.  Do I need to have the file open beforehand, if yes how do I do it, with code? or just file open, I am assuming that I would need to run this in Word or something similar?

Any help would be appreciated

Andy

PS - Please be patient with me, i am very new to all this kind of stuff
No need to go to Word. Have a text box for entering the file path name. Have a command button for doing the conversion. Copy the following code, to your vb form:
----------------------

Private Sub Command1_Click()
    ToCSV txtFilePath.Text
    Msgbox "File Converted."
End Sub

Function ToCSV(FilePathName As String)
Dim lFileHandle1 As Integer
Dim lLineNo As Long
Dim lStrLine As String
Dim lStrTempFilePath As String
Dim lFileHandle2 As Integer 'Windows File Number Reference

    lStrTempFilePath = "tmpfile"
   
    lFileHandle1 = FreeFile() ' Get a free file number
   
    'open your original file
    Open FilePathName For Input As #lFileHandle1
   
    lFileHandle2 = FreeFile     'Write to a temp. file.
    Open lStrTempFilePath For Output As lFileHandle2
   
    lLineNo = 0
    Do While Not EOF(lFileHandle1)
        Line Input #lFileHandle1, lStrLine
       
        Print #lFileHandle2, ConvertToCSV(lStrLine)
               
        lLineNo = lLineNo + 1
    Loop
    Close #lFileHandle1
    Close #lFileHandle2
   
    FileCopy lStrTempFilePath, FilePathName
    Kill lStrTempFilePath
End Function

Function ConvertToCSV(Str1 As String)
 Str1 = Replace(Str1, "  ", ",")

'UnComment the following line, if you need to strip off single blanks spaces also, in your file lines.
 'Str1 = Replace(Str1, " ", "")

 Do While InStr(Str1, ",,") > 0
    Str1 = Replace(Str1, ",,", ",")
 Loop
 ConvertToCSV = Str1
End Function

----------------------

But I dont know, if it will help, if you have some missing fields in your text file, say, some fields are only blank.

Any problems, you tell, let me try to help. Can you tell, the purpose of your conversion, anyway?

hope this helps,
Cheers.
Avatar of fx124

ASKER

valli_an

The above code you gave me keeps stopping at the Replace function saying undefined Sub or Function, I've checked the references and they all seem to be fine, is there a specicfic one that should be checked?

Also when I get to the line:
Open lStrTempFilePath For Output As lFileHandle2

it comes up with the error "File already open"

Hope you can help

Andy
ASKER CERTIFIED SOLUTION
Avatar of Valliappan AN
Valliappan AN
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fx124

ASKER

Valli_an

I've used the code that Microsoft posted and it is now stopping at:

       Print #lFileHandle2, ConvertToCSV(lStrLine)

with a run-time error number 52, bad file name or number

have you any ideas what this is?

As for the version of VB - I don't know it is whatever comes with Access 97
Avatar of fx124

ASKER

Valli_an

Any ideas, please?

Andy
fx124,

could you paste the code, or send it to me, so that I could have a look at it. My mail id: valli_an@softhome.net

Cheers.
Avatar of fx124

ASKER

Valli_an

I've sussed it out, using a bit of your code and a bit of Microsoft's code - I shall split the points between you (25/25).  THanks for all your help.  The problem I was having after sorting the above out was actuallu getting the information back into another file.  I cracked it.

Once again Thank You

Andy
fx124,

Thats nice of you.

Cheers.
Avatar of fx124

ASKER

FAO MODS

Please award half the points as stated above

Many thanks

Andy
fx124:

The proper way to request a points split is by posting a zero-point question at community support (link below) with a link to this question.

In this instance, I can handle the issue for you. Microsoft is no longer a member of EE. Points awarded to valli_an.

amp
community support moderator

CS: https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
amp,
Hope Microsoft's new id is andysalih, if I am right. So, Can I share the points with them, if so, how to do, and how many points to do. Thanks.
Post a question with "Points for andysalih". Warning: He hasn't logged in since 2-Jan.

andysalih's profile indicates that he did used to be Microsoft.

If you choose to do that, why don't you email me a reminder in a week or so. We'll see if he logs in. Then I'll post the Points question for you. However, I don't like posting Points questions that are unlikely to be collected. This question at community support shows what happens:

https://www.experts-exchange.com/jsp/qShow.jsp?ta=commspt&qid=20258789

thanks!
amp
community support moderator
amp@experts-exchange.com
ok, amp, I will remind you by Feb. 2nd week or so, and if andysalih logs in, you can post a points question.

I just searched the link you provided, if there are some for my id. :)

Thanks.
Great!
amp, This is to remind you. Please acknowledge.

Cheers.
Sorry, valli_an. Still 2-Jan. Feel free to remind again!

amp
community support moderator