Solved

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

Posted on 2004-04-30
12
905 Views
Last Modified: 2012-06-21
Hi,

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!!

Regards,

PCMENIAC
0
Comment
Question by:PCMENIAC
  • 5
  • 5
  • 2
12 Comments
 
LVL 54

Expert Comment

by:nico5038
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 ?

Nic;o)
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 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"

'Cleanup
DoCmd.DeleteObject acTable, "tmpImp"




0
 

Author Comment

by:PCMENIAC
ID: 10959031
Hmm..

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.

THanks!!
0
 
LVL 54

Expert Comment

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

Nic;o)
0
 
LVL 65

Expert Comment

by:rockiroads
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, ",")

    Loop

    'Close    
    Close #iHandle



here my file contains the followng

ID,Txt
1,XXX
2,YYY
3,ZZZ


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

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


0
 
LVL 54

Expert Comment

by:nico5038
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.

Nic;o)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:PCMENIAC
ID: 10960190
Nico,

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!!

Regards.
0
 
LVL 54

Expert Comment

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

DateReformat:dateserial(mid(fieldx,7,2),mid(fieldx,4,2),mid(fieldx,1,2))

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 ?

Nic;o)
0
 

Author Comment

by:PCMENIAC
ID: 10960783
NICO,

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??

Thx..
0
 
LVL 54

Expert Comment

by:nico5038
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, ...

Clearer?

Nic;o)
0
 

Author Comment

by:PCMENIAC
ID: 10961555
Cheers!!
0
 

Author Comment

by:PCMENIAC
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:
UPDATE [TEMP MASTER TABLE]
[TEMP MASTER TABLE] INNER JOIN [TEMP TABLE] ON
([TEMP MASTER TABLE].[Del] = cdate([TEMP TABLE].Field7))
AND
([TEMP MASTER TABLE].[Local Authority ID] = [TEMP TABLE].Field1)
SET
[TEMP MASTER TABLE].[Tape ref:] = [field2],
[TEMP MASTER TABLE].[Media Type] = [field8],
[TEMP MASTER TABLE].Density = [field9],
[TEMP MASTER TABLE].[DECKS USED] = [field10],
[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!!

Regards,

Parag Chauhan
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

757 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

19 Experts available now in Live!

Get 1:1 Help Now