Importing a fixed width text file into a access table

Posted on 2012-09-20
Last Modified: 2012-09-24
I need to import a fixed width text file with a record length 249 characters into a access table with a data type of text and a lenght of 249. I need to keep all the spaces ithat are in the text file. Is there a way this can be done. Thanks
Question by:dfacemire
    LVL 39

    Expert Comment

    Can you show sample from this file? Is there only one column?

    Author Comment

    UTBank.txt fixed width length 1 to 249

    That you for the quick response. I've attached the text file that gets imported. Yes its just 1 column but there are multiple field with in the 249 characters. The access table is one column. Thanks
    LVL 39

    Accepted Solution

    You can manually set line width in import master (Press button Advanced) :
    import masterDelete all lines except first and set width to 255:
    LVL 28

    Assisted Solution

    In my Access 2010, white space at end of line gets truncated. If I do this in a function:
    Function gettext()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("table2") 'your destination table. Field must be memo data type

    Dim vstring As String
    Open ".\ff.txt" For Input As #1 'full path and name of source file
    Do While Not (EOF(1))
    Line Input #1, vstring
    Debug.Print Len(vstring) & " " & vstring
    With rs
    rs!newfield = vstring 'newfield is destination field name
    End With
    Close #1
    End Function
    all lines are 249 characters.
    LVL 28

    Expert Comment

    error in comment. Destination field can be text data type, length 255

    Featured Post

    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.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    732 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

    23 Experts available now in Live!

    Get 1:1 Help Now