Solved

automatically importing a text file into a table.

Posted on 2006-06-12
9
470 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 250 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

18 Experts available now in Live!

Get 1:1 Help Now