Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

automatically importing a text file into a table.

Posted on 2006-06-12
9
Medium Priority
?
481 Views
Last Modified: 2008-02-01
Hello Experts and thank you in advance for your help.

I’m trying to import a text file with 9 columns into access 2000. This is easy enough I know, however the thing I have to import this 3 times a day every day and I'd like to make something so my users could do it without having to think very much. Currently the text file is imported via ms access wizard. Here they have to assign how the fields are separated (coma, period, etc) the data type and all that good stuff. Then they have to name each column...etc. Once the data is in a table, I do an append query to get that data on the tail end of the table I need it in.

What I would like is to be able to do is, make a macro to automate this since nothing changes day to day same text file that resides in the same folder gets appended to the same table.

Any help will be appreciated.

-VN
0
Comment
Question by:vnares
  • 4
  • 2
  • 2
9 Comments
 
LVL 58

Accepted Solution

by:
harfang earned 1000 total points
ID: 16890944
Hello vnares

Do it once more, very carefully, and click on the [Advanced] button. Check everything again, examine the new advanced options. Then click on [Save] to create an "import specification", under any name, but write that name down.

You can then use that import specification in a macro or VB function to import the text file  without going through the wizard again. It contains all the answers and decisions you made in the wizard.

Good luck!

(°v°)
0
 

Author Comment

by:vnares
ID: 16891662
Nice! I'll try it out and let you know. Thanks!
0
 
LVL 58

Expert Comment

by:harfang
ID: 16891801
Feel free to ask if you need help with the macro or the VB function itself, too.
(°v°)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 5

Expert Comment

by:sriramiyer
ID: 16892494

For your query to automate data transfer into access file, you can use the following command,

TransferText Method
                       
The TransferText method carries out the TransferText action in Visual Basic.

Syntax:-

DoCmd.TransferText [transfertype][, specificationname], tablename, filename[, hasfieldnames][, HTMLtablename][, codepage]

Example:-
-------
DoCmd.TransferText acImportDelim, "Table_Import_Specification",  "tableName", "c:\textdata\textfilename.txt"

The TransferText method has the following arguments.

Argument Description
transfertype One of the following intrinsic constants:
acExportDelim
acExportFixed
acExportHTML
acExportMerge
acImportDelim (default)
acImportFixed
acImportHTML
acLinkDelim
acLinkFixed
acLinkHTML
  If you leave this argument blank, the default constant
(acImportDelim) is assumed.
  Notes   You can link to data in a text file or HTML file, but this data is read-only in Microsoft Access.
Only acImportDelim, acImportFixed, acExportDelim, acExportFixed, or acExportMerge transfer types are supported in a Microsoft Access project (.adp).
 
specificationname A string expression that's the name of an import or export specification you've created and saved in the current database.

step: 1

First to create import_specification_name,

(1)  Go to File menu: Get External Data : Import
(2)  Find your text file, and hit Import.
(3)  In the 'Import Text Wizard' form that appears, hit the 'Advanced' button in the lower left.
(4)  Here's where you can specify how to slice-dice your text file, specifically a semi-colon as the delimeter, and forcing columns of data into what data types it is fed into a table.  Save your Import Spec, and remember the name.  

step: 2


(5)  Then, when you want to import, use this line of code in your macro

DoCmd.TransferText acimportdelim, "Your Import Spec Name", "Your Table Name", "Your File Name.txt"

I hope this helps.

My suggestion to increase points to 500,.

Thanks in advance.

from
SriRamIyer.

0
 
LVL 58

Expert Comment

by:harfang
ID: 16894057
<SriRamIyer>

Your post contains large portions of the MS Access help file (search for "TransferText" in help, or type "TransferText" in the immediate pane or VB window and press [F1]).

Although M$ will not sue you for copyright violation, you reveal very low intellectual standards by doing so. EE guidelines also considered it bad practice to copy-and-paste large portions of text available elsewhere, instead of showing a link or a short reference {http:/help.jsp#hi60}.

If we remove any plagiarism from your post (both from M$ and from my previous post), we are left with this as actual information content:

    « Use the DoCmd.TransferText method »

Well..., thank you for your valuable input!

Finally, I find your "suggestion to increase points" totally uncalled for, irrelevant given the circumstances, and rather offensive. The implications are naturally: "increase points and give them to me for my cut-n-paste job". I certainly hope this will *not* happen.

Regards,

Markus "harfang" Fischer

</SriRamIyer>
0
 

Author Comment

by:vnares
ID: 16896563
Dont want to get in the middle of this, but I was in this case looking for a solution I could implement with no visual basic coding.  Harfang's response was more what I was looking for.

Regards and many thanks to all of you.

-VN
0
 
LVL 58

Expert Comment

by:harfang
ID: 16899121
vnares

This is your question, and we should be the ones apologizing for the exchange of unrelated comments, especially as there is no good way to mark off-topic material. So please do accept my apologies.

I'm glad this worked for you, and wish you the best of luck with your application.

(°v°)
0
 
LVL 5

Expert Comment

by:sriramiyer
ID: 16899503
Hello vnares,

1. The statement  "My suggestion to increase points to 500"
I posted it here is only my suggestion/view.  If it make you feel irrelavant, I apologise and  really sorry for that.

2. "make a macro to automate this ..."

I have given you here the syntax and one exmaple for TransferText Method.

I have it taken from Microsoft help of course. I have posted here elaborately  to understand better and others who see the answers may also got benefitted out of this without refering back again help files and other reference books.

Anyhow as far as I am concerned copy/paste is a necessary evil and which can not be avoided in IT field to save time and effort in typing mistakes, etc.

I once again thanks you for your valuble comments.

from
SriRamIyer.
0

Featured Post

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

971 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