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
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
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
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 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.
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.
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.
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
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.
----------------------
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Valli_an
Any ideas, please?
Andy
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.
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.
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
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.
Thats nice of you.
Cheers.
ASKER
FAO MODS
Please award half the points as stated above
Many thanks
Andy
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
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.
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
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.
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.
Cheers.
Sorry, valli_an. Still 2-Jan. Feel free to remind again!
amp
community support moderator
amp
community support moderator
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