Solved

Deleting spaces and inserting a comma into it

Posted on 2001-06-13
22
233 Views
Last Modified: 2010-05-02
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
Comment
Question by:fx124
  • 9
  • 6
  • 4
  • +2
22 Comments
 
LVL 2

Expert Comment

by:Microsoft
ID: 6189105
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
 
LVL 2

Expert Comment

by:Microsoft
ID: 6189108
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
 
LVL 28

Expert Comment

by:AzraSound
ID: 6189171
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
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6189674
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
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6189678
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
 

Author Comment

by:fx124
ID: 6192943
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
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6193793
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
 

Author Comment

by:fx124
ID: 6198918
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
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 50 total points
ID: 6199422
>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
 

Author Comment

by:fx124
ID: 6200289
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
 

Author Comment

by:fx124
ID: 6212104
Valli_an

Any ideas, please?

Andy
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6216801
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
 

Author Comment

by:fx124
ID: 6217405
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
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6217656
fx124,

Thats nice of you.

Cheers.
0
 

Author Comment

by:fx124
ID: 6374625
FAO MODS

Please award half the points as stated above

Many thanks

Andy
0
 

Expert Comment

by:amp072397
ID: 6757522
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
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6759199
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
 

Expert Comment

by:amp072397
ID: 6759415
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
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6760199
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
 

Expert Comment

by:amp072397
ID: 6760752
Great!
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6787513
amp, This is to remind you. Please acknowledge.

Cheers.
0
 

Expert Comment

by:amp072397
ID: 6787823
Sorry, valli_an. Still 2-Jan. Feel free to remind again!

amp
community support moderator
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now