automatically importing a text file into a table.

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
vnaresAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
harfangConnect With a Mentor Commented:
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
 
vnaresAuthor Commented:
Nice! I'll try it out and let you know. Thanks!
0
 
harfangCommented:
Feel free to ask if you need help with the macro or the VB function itself, too.
(°v°)
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.

 
sriramiyerCommented:

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
 
harfangCommented:
<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
 
vnaresAuthor Commented:
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
 
harfangCommented:
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
 
sriramiyerCommented:
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
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.

All Courses

From novice to tech pro — start learning today.