Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1169
  • Last Modified:

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

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
PCMENIAC
Asked:
PCMENIAC
  • 5
  • 5
  • 2
1 Solution
 
nico5038Commented:
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
 
rockiroadsCommented:
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
 
PCMENIACAuthor Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
nico5038Commented:
What's wrong with the linked table approach ?

Nic;o)
0
 
rockiroadsCommented:
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
 
nico5038Commented:
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
 
PCMENIACAuthor Commented:
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
 
nico5038Commented:
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
 
PCMENIACAuthor Commented:
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
 
nico5038Commented:
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
 
PCMENIACAuthor Commented:
Cheers!!
0
 
PCMENIACAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now