Solved

automatically importing a text file into a table.

Posted on 2006-06-12
9
473 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

805 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