Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


VBA to Import Data from a tab delimited text file into certain fields of an access table automatically

Posted on 2004-04-30
Medium Priority
Last Modified: 2012-06-21

I have a tab delimited text file with some data which I need to use to populate certain fields in a table in access by writting a macro in VBA. The data is not in the same order as the fields in the table. So, I was thinking would it be a beter approach to write a macro which read the text file and store each tab delimited data into a separate variables and then write the value of required variables in appropriate fields in the table.

I dont really want to create another temporary/intermediate table in access to write the data from the text file in teh same order and then insert the required value into the final table.

For Ex: Text file contains:

12345    achuyoo    25/0304   hjyuiii    458896    ICL    0   23/05/04   9

And we might need to store this data in the following manner in Access

FIELD1    FIELD2    FIELD3        FIELD4     FIELD5     FIELD6    FIELD7    FIELD8
              12345     25/03/04                   ICL           0                          hjyuiii

THanks for helping me out here!!


Question by:PCMENIAC
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
LVL 54

Expert Comment

ID: 10958442
Normally I link a table to such a text file and use an append query to "move" the fields to the appropriate fields in the target table.

Idea ?

LVL 65

Accepted Solution

rockiroads earned 1500 total points
ID: 10958594
Easier to import into a temporary table and have a SQL script which reads from temp into main table
(like Nico suggested, just a different approach)

the process would be summat like this

docmd.TransferText acImportDelim,,"tmpImp","c:\test.txt"    (tmpImp is name of table, creates automatically, c:\test.txt is your file)
DoCmd.RunSQL "INSERT INTO mainTable (Field1, Field2, Field3...) SELECT (Field1, Field2, Field3...) FROM tmpImp"

DoCmd.DeleteObject acTable, "tmpImp"


Author Comment

ID: 10959031

Rockiroads, My first reaction was to do the same but then had comlplications as we are trying to avoid to create new tables. Also, what I am trying to do is to create a button on a form which when clicked, reads the files and gets the values. This is because we get a new file every week but with the same name.

But I'll give your idea a shot. I'll let you know how it goes in an little while.

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

LVL 54

Expert Comment

ID: 10959166
What's wrong with the linked table approach ?

LVL 65

Expert Comment

ID: 10959347
If you wanted to read instead of creating a temp table (either physical or linked like Nico suggested), then here is some sample code

    Dim iHandle As Integer
    Dim sLine As String
    Dim sFields() As String

    'Get a file handle
    iHandle = FreeFile

    'Open a file for read
    Open "C:\imp.txt" For Input As #iHandle
    'Read each line until no more...
    Do While Not EOF(iHandle)

        'Read line, assign into string sLine
        Line Input #iHandle, sLine

        'Show me what u got
        Debug.Print sLine

        'Put values into a string array
        sFields = Split(sLine, ",")


    Close #iHandle

here my file contains the followng


split will take individual values and you can then extract what u want, build a insert statement and run it to insert data
split on 1,XXX will be

sFields(0) = 1
sFields(1) = XXX

LVL 54

Expert Comment

ID: 10959459
To link a table to a textfile just create a new table and select the option to link.
On the Open dialog select as filetype .txt and navigate to your file.

After the ceation of the link open the query editor, select this linked table and the needed fields. Next change the query type into an append query and select the target table. Equally named fields will be "linked" automatically by Access, but just every "move" is possible.

Save that query and all needed is to run the query by a double-click.
You can even replace the textfile with a newer version (as long as the name remains the same) and rerun the query to get new data imported.

All without a single line of code.


Author Comment

ID: 10960190

There's nothing wrong with your approach as well but I was under the impression that the "linking" approach would fail if the file gets updated regularly.

But there's a slight problem that after linking the text file, date parameters got changed from 30/04/04 to 2030-04-04. How can I stop this?? if I change the parameters during linking process to text, then it transfers date in the exact format but then If I try inserting text from this temp table into a date/time parameter in the final table, the nwould it not create a  problem??

THanks for letting me know!!

LVL 54

Expert Comment

ID: 10960277
In the query you can transform the "-" into a "/" or to be 100% sure use the dateserial command like:


I used mid to get the same statement, but left and right can also be used.
Syntax: Dateserial(string with year, string with month, string with day)

Clear ?


Author Comment

ID: 10960783

No, I am more confused now..

<< Syntax: Dateserial(string with year, string with month, string with day)

Can you explain this a bit further??

<< DateReformat:dateserial(mid(fieldx,7,2),mid(fieldx,4,2),mid(fieldx,1,2))
Which query are you refering to and how to use DateReformat:Dateserial feature??

LVL 54

Expert Comment

ID: 10960830
Sorry, just too cryptical as usual, this is referring to a "field cell" in the graphical query editor.
In SQL text you would find:
select  dateserial(mid(fieldx,7,2),mid(fieldx,4,2),mid(fieldx,1,2)) as DateReformat, ...



Author Comment

ID: 10961555

Author Comment

ID: 10993968
HEllo Nico,

There is a slight problem that I have found with taking the linking approach. If I link a temp table to the text file (which contains the data in it)and then write an update query to update the main table, its not letting me run an update query on linked table. The update query is:
([TEMP MASTER TABLE].[Del] = cdate([TEMP TABLE].Field7))
([TEMP MASTER TABLE].[Local Authority ID] = [TEMP TABLE].Field1)
[TEMP MASTER TABLE].[Tape ref:] = [field2],
[TEMP MASTER TABLE].[Media Type] = [field8],
[TEMP MASTER TABLE].Density = [field9],
[TEMP MASTER TABLE].[Customer Records] = [field11],
[TEMP MASTER TABLE].[Non-Customer Records] = [field12],
[TEMP MASTER TABLE].[Number of Rejected Records] = [field13];

I am using the 'update' statement Bcos I need to fill in the details in the main table where ID in the temp table is equal to the ID in main table.

Its more easy to write a macro to import the data and run the SQL query and you can have a button which runs this macro whenever clicked.

Thanks for your help anyways!!


Parag Chauhan

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

618 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