Importing a txt file into MS Access

Posted on 2009-04-29
Last Modified: 2013-11-27
If I use the built-in function of MS Access, is it true that it samples the first 10 rows to calculate the number of columns.

The problem that I am having is, the first 10 or so rows has data in 10 columns/fields, but my imported file should have 12 columns/fields, so there is data missing, which I use the run a certain query.

What would be the best way forward? Also this file will change over time, so would vba code to read data from a text file be the better option?

Question by:irishbil
    LVL 22

    Expert Comment

    by:Kelvin Sparks
    I understand it's about this first 25 rows.

    What sort of text file (CSV, Tabs etc). Most files would still have null values for those fields.

    You can force it with an import specification to say what columns to import.

    You can also have a table to import to that has the correct columns there.


    Author Comment

    Hi Kelvin, Thanks for you quick reply. The file is a CSV file...

    Your last comment seems the best for what i am looking for. So if I created the table first, how would i get the text file to import into it. Maybe I am missing something here!
    LVL 22

    Expert Comment

    by:Kelvin Sparks
    The best is to import to a table and then create an import specification to match the data.

    The Transfer text command will do this, just give the table name in the appropriate space in the code. To create the simport specification, manually import the data = (if Access 2003, use File>Import and when you see the first preview of the data click the Advanced button at the bottom left. Once you have told it what to do (and it'll take a bit to master) save it, and paste the name you saved the specification as into the correct part of the transfertext command.

    Once you have this sorted it should stay stable and reliable.

    Author Comment

    I have never used the transfertext command before, but i understand it looks for the saved import spec.

    Just to clarify, in the Import Specification, under Field information, can i just add in the extra fields names and the extra columns will be added. Sorry I can't fully test it at the minute.
    LVL 22

    Accepted Solution

    Yes you can.

    I'm away now (in New Zealand and after midnight).

    Will look again tomorrow

    Author Closing Comment

    Its easy when you know how! Thanks, Liam

    Expert Comment

    You first need to import the file, customising the field types etc., then save the specification. This is accessed using the Advanced button during the import process. Here is a simple example of some thing I use regularly and it works like a charm. The routine first deletes all records from the target table, then imports the target file, then runs a query on the new data :

    Private Sub ImportData_Click()

        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryP11D_Stage01_Delete"
        DoCmd.TransferText acImportDelim, "P11D_CONS Import Specification", "tblP11D_CONS", [TargetFile], True
        DoCmd.OpenQuery "qryP11D_Stage04"
        DoCmd.SetWarnings True

    End Sub

    Rather than have the file location hard coded into the routine I have a form with the field TargetFile which is populated using the following routine:

    Private Sub ImportDir_Click()
    On Error GoTo Err_ImportDir_Click
        Dim strFilter As String
        Dim lngFlags As Long
        strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
        Me.TargetFile = ahtCommonFileOpenSave(InitialDir:="C:\", _
            Filter:=strFilter, FilterIndex:=3, flags:=lngFlags, _
            DialogTitle:="Select File to Import")
        Exit Sub

        MsgBox "You have not specified a file"
        Resume Exit_ImportDir_Click
    End Sub

    Good luck, if you need a sample db let me know.
    LVL 22

    Expert Comment

    by:Kelvin Sparks

    It is only easy, when we have previously struggled our way through these things as you have. Believe me, one of the biggest challenges a developer can have is to build a robust import tool that handles every variance that the non technical world can throw at us.


    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    729 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

    21 Experts available now in Live!

    Get 1:1 Help Now