• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

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
0
fx124
Asked:
fx124
  • 9
  • 6
  • 4
  • +2
1 Solution
 
MicrosoftCommented:
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
0
 
MicrosoftCommented:
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
0
 
AzraSoundCommented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Valliappan ANCommented:
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.
0
 
Valliappan ANCommented:
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.
0
 
fx124Author Commented:
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
0
 
Valliappan ANCommented:
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.
0
 
fx124Author Commented:
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
0
 
Valliappan ANCommented:
>Also when I get to the line:
>Open lStrTempFilePath For Output As lFileHandle2

This might be, since, you had not closed, after executing the program, and again trying to execute it.

>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?

What version of VB are you using? This works in VB 6.0. If previous versions, then you need to create a separate function for Replace, like the one, Microsoft has given already.

Cheers.
0
 
fx124Author Commented:
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
0
 
fx124Author Commented:
Valli_an

Any ideas, please?

Andy
0
 
Valliappan ANCommented:
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.
0
 
fx124Author Commented:
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
0
 
Valliappan ANCommented:
fx124,

Thats nice of you.

Cheers.
0
 
fx124Author Commented:
FAO MODS

Please award half the points as stated above

Many thanks

Andy
0
 
amp072397Commented:
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: http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
0
 
Valliappan ANCommented:
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.
0
 
amp072397Commented:
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:

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

thanks!
amp
community support moderator
amp@experts-exchange.com
0
 
Valliappan ANCommented:
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.
0
 
amp072397Commented:
Great!
0
 
Valliappan ANCommented:
amp, This is to remind you. Please acknowledge.

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

amp
community support moderator
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now