Solved

automatically importing a text file into a table.

Posted on 2006-06-12
9
471 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

911 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

17 Experts available now in Live!

Get 1:1 Help Now