TransferText method fails on text file

[geoB]

In Access 2007:

I'm attempting to automate the import of a delimited text file without success. I can manually import the text file with the wizard without problem. When I use the command

DoCmd.TransferText acImportDelim, , "BB_Download", strFileName, -1

where strFileName is obtained thru the file dialog, I get a table with a single field named "ÿþ" and a potful of paste errors.

FWIW, looking at the text file reveals that lines are not terminated with both CR & LF.

I'd attach the text file but it contains confidential information.

Any clues as to what's going on?

Thanks.

George

 

[boblarson]

You probably need to create an IMPORT SPECIFICATION to use with your code.

 

[geoB]

Unfortunately, import specs won't help in this case. The application is designed to import a set of graded items from some course management software (Blackboard). Each instructor may have a unique set of graded items, so there's no consistency in imported fields. (In fact, the graded item field names are extracted and entered into another table.)

George

 

[boblarson]

Unfortunately, import specs won't help in this case. The application is designed to import a set of graded items from some course management software (Blackboard). Each instructor may have a unique set of graded items, so there's no consistency in imported fields. (In fact, the graded item field names are extracted and entered into another table.)

George

Then you have to import manually. You can't do it via code if it isn't consistent.

 

[geoB]

I was afraid of that. Oh well. Since the application is for instructors of MS Office they ought to be able to handle it! All it takes is RTFM.

George

 

[geoB]

Removed to correct errors.
gwb

 

[gemma-the-husky]

if the data isnt consistent, how can you automate an import at all?

 

[boblarson]

if the data isnt consistent, how can you automate an import at all?

That was my question too, but I had just let it wisp away...

 

[geoB]

The application is constructed to apply to a single instructor's instance of a class. For each instructor the file I'm trying to import will be imported once. The inconsistency is between instructors.

What I thought I had solved, but am still fine-tuning, is a byte by byte conversion of a Unicode text file to a format more readily imported with the TransferText method. This should be possible because imported files are all generated by the same process so their internal formats should be consistent. The fields are all that will vary.

Once imported, everything else is manageable.

I may be delusional but I think I can make it work. Film at 11.

George

 

[geoB]

Here's the function that makes at least one file that can be imported with the TransferText method.

Function MakeReadable(Filename) As Boolean

    Dim strFIn As String, strFOut As String, btChar As Byte
    
    On Error GoTo MakeReadable_Error

    strFIn = Filename
    If strFIn = "" Then
        MakeReadable = False
        Exit Function
    End If
    strFOut = strFIn & ".out"
    Open strFIn For Binary As #1
    Open strFOut For Binary As #2
    Get #1, , btChar                         'drop the first two bytes
    Get #1, , btChar
    Do Until EOF(1)
        Get #1, , btChar
        Select Case btChar
            Case 0                              'don't write 00h
            Case 9                              'tab character; ignore at end of line
                Get #1, , btChar                'read and ignore following 00h
                Get #1, , btChar                'if this is 0Ah, then do CR/LF, else write ,"
                If btChar = 10 Then
                    btChar = 13
                    Put #2, , btChar
                    btChar = 10
                    Put #2, , btChar
                Else
                    btChar = 44
                    Put #2, , btChar
                    btChar = 34
                    Put #2, , btChar
                End If
            Case Else
                Put #2, , btChar
        
        End Select
        
    Loop
    Close #1
    Close #2
    Kill strFIn
    Name strFOut As strFIn
    MakeReadable = True

    On Error GoTo 0
    Exit Function

MakeReadable_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure MakeReadable of Module Utility"
    
End Function

George