stripping out characters from a text file b4 import into Access

[maudedo]

Hello All

I have a text file which has two fields, a unique number and a description.

LogID, Description
1, "blah, blah (character return)
! ***" etc

there are thousands of these unique numbers with free text descriptions of upto 255 characters. This has been downloaded from a business objects report, interrogating a database. The description field from this database can contain any character on the keyboard.

Therefore if i try to import into Access the commas, character returns, / and \ etc which are embodied within the text will try to split the string into parts, and thus some of the description will be dis-associated with the unique log number it relates to.

The long way around is notepad and find and replace function. This obviously is no good for character returns or hidden ascii characters, and is very time consuming.

Is there any code in access which can strip out all of the characters and replace with a non seperator character (such as "-"). then once this is done, then import it into a table for just two fields of LogID and Description?

I am not familiar with functions, so any help would be much appreciated.

I am familiar with the acimport functions however, so once the 'stripping' is complete I can carry on the import code from there.

regards
Donna

 

[GeekGirlau]

You might want to try just replacing the first comma with a tab - this becomes your field delimiter. After replacing this character, you can use "TransferText" to import the text file. This works best if you save an import specification first - import the file manually, and save the options you set to import the text.

Sub ImportData()
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Purpose:      Change the text file field delimiter to TAB
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Dim strOriginal As String           ' original text file
    Dim strNew As String                ' new text file
    Dim strLine As String               ' read line from original text file
    Dim strNewLine As String            ' new line for text file
    
    On Error GoTo ErrHandler
    
        ' set actual paths here
    strOriginal = "C:\Test\MyTest.txt"
    strNew = "C:\Test\NewTest.txt"
    
        ' does the file exist?
    If Dir(strOriginal, vbNormal) = "" Then
        MsgBox "Cannot locate text file!" & vbCrLf & vbCrLf & _
            strOriginal, vbCritical, "File Not Found"
    
    Else
            ' delete any existing "new" text file
        On Error Resume Next
        Kill strNew
        On Error GoTo ErrHandler
        
        Open strOriginal For Input As #1
        Open strNew For Append As #2
        
        Do While Not EOF(1)
            Input #1, strLine
            
                ' find first comma and replace with tab
            strNewLine = Left(strLine, InStr(1, strLine, ",") - 1)
            strNewLine = strNewLine & Chr(9) & InStr(1, strLine, ",") + 1
        
            Print #2, strNew
        Loop
    End If
    
    
    
ExitHere:
    On Error Resume Next
    Close #1
    Close #2
    Exit Sub
    
ErrHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume ExitHere
End Sub

 

[maudedo]

Hi
thank you for the above.

i've given it a shot, and on the
strNewLine = Left(strLine, InStr(1, strLine, ",") - 1)

i keep getting error 5: invalid procedure call or arguement.


i've moused over the line and key criteria and all seems to be ok.. any ideas?

 

[GeekGirlau]

Maybe there isn't a comma in that line.

Try this:

                ' find first comma and replace with tab
            If InStr(1, strLine, ",") > 0 then
                strNewLine = Left(strLine, InStr(1, strLine, ",") - 1)
                strNewLine = strNewLine & Chr(9) & InStr(1, strLine, ",") + 1
        
                Print #2, strNewLine

            Else
                Print #2, strLine
            End If

 

[maudedo]

Hello GeekGirlau

Many thanks for pointing me in the right direction. I have amended your code slightly to strip out all commas within the string as opposed to only the 1st one, and then strip out all the other characters I dont need.

this is as follows


Sub ImportData()
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Purpose: Change the text file field delimiter to TAB
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Dim strOriginal As String ' original text file
Dim strNew As String ' new text file
Dim strLine As String ' read line from original text file
Dim strNewLine As String ' new line for text file

On Error GoTo ErrHandler

' set actual paths here
strOriginal = "C:\pcs2\DM_Delays lists to PS SD.txt"
strNew = "C:\pcs2\DM_Delays lists to PS SD_apd.txt"

' does the file exist?
If Dir(strOriginal, vbNormal) = "" Then
MsgBox "Cannot locate text file!" & vbCrLf & vbCrLf & _
strOriginal, vbCritical, "File Not Found"

Else
' delete any existing "new" text file
On Error Resume Next
Kill strNew
On Error GoTo ErrHandler

Open strOriginal For Input As #1
Open strNew For Append As #2

Do While Not EOF(1)
Input #1, strLine

a = Replace(strLine, ",", ".", 1, -1)
b = Replace(a, Chr(10), ".", 1, -1)
c = Replace(b, "*", "", 1, -1)
d = Replace(c, "!", ".", 1, -1)
e = Replace(d, "/", "_", 1, -1)
f = Replace(e, Chr(13), ".", 1, -1)
g = Replace(f, "\", "_", 1, -1)
h = Replace(g, "&", "", 1, -1)
i = Replace(h, "", "£", 1, -1)

Print #2, i

Loop
End If

ExitHere:
On Error Resume Next
Close #1
Close #2
Exit Sub

ErrHandler:
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere
End Sub

 

[GeekGirlau]

I'm glad that worked for you!

Might I suggest that you don't need the variables "a", "b" etc - just use strNewLine.

strNewLine = Replace(strLine, ",", ".", 1, -1)
strNewLine = Replace(strNewLine, Chr(10), ".", 1, -1)
strNewLine = Replace(strNewLine, "*", "", 1, -1)
strNewLine = Replace(strNewLine, "!", ".", 1, -1)
strNewLine = Replace(strNewLine, "/", "_", 1, -1)
strNewLine = Replace(strNewLine, Chr(13), ".", 1, -1)
strNewLine = Replace(strNewLine, "\", "_", 1, -1)
strNewLine = Replace(strNewLine, "&", "", 1, -1)
strNewLine = Replace(strNewLine, "", "£", 1, -1)

 

[maudedo]

oh yes, i will give this a shot.. thanks again

regards
Donna