text import

[sailngsue]

I have a text file that is one record and uses a ~ as a delimiter between fields.
I used the DoCmd.transferText Statement
DoCmd.TransferText acImportFixed = 1, , "NewImport", "W:\NewImport.txt
It imported as one record creating field names field1....field255.
What I really want it import it as is one field with as many records as it needs.

any suggestions?

[TheShabz]

I asked for clarification in your first thread but as this is a separate topic, good call in making a new thread.

Easiest way would be to get an advanced text editor that allows you to replace a character with the return carriage. once you do that, you can import as normal and it will be one word per record.

[sailngsue]

I have a text editor that can split the fields for me, but I'm trying to automate the process so that a accounting clerk can just push a button.

[TheShabz]

The issue you run into is that in order to do this in Access, you would have to get it into a table in Access first. In your other thread, you mentioned there were 255. That number leads me to believe that there were more words but were truncated due to Access' 255 field limit. Unless you can get your source file to be formatted properly, you are going to run into major issues.

[sailngsue]

the 255 fields concerned me as well, thats why I re posted here in the import/export area. The solution needs to be as it's being imported. I was hoping I just missed a setting or parameter.

[randman1]

I do something similar with text files that contain multiple lines of text. Each line is a record in a table containing 14 fields. This is a modification of the code I use so that you can add each of the 255+ records to a table. It's untested and may require some changes to suit your application. You must also add the reference Microsoft Scripting Runtime to your project.

Private Sub ImportFile(sFilePath As String)
    Dim oFso As New FileSystemObject
    Dim oTs As TextStream
    Dim oRs As DAO.Recordset
    Dim vValues() As Variant
    Dim exp As Integer

    Set oTs = oFso.OpenTextFile(sFilePath, ForReading)
    vValues = Split(oTs.ReadAll, "~")

    Set oRs = CurrentDB.OpenRecordset("YourTableName", , dbAppendOnly)
    For exp = 0 To UBound(vValues)
        oRs.AddNew
        oRs!YourFieldName = vValues(exp)
        oRs.Update
    Next exp

    oRs.Close

End Sub