Solved

Access97: import textfile specification error :(

Posted on 1997-07-07
3
369 Views
Last Modified: 2008-02-26
Hi there, I tried to create a module to import text files into tables. When I put in the textfile specification like "cm import specification", the error is "no such specification is fould".  If I leave it blank like " ", the error is "3791: field F1 is not in the destination table" and I didn't see that msg when I import the file manually. So, could anyone tell me what caused the problem and how to fix it?
0
Comment
Question by:ny971181
  • 2
3 Comments
 

Expert Comment

by:pttt
Comment Utility
When you run the import wizard you get the chance to change the field names (the third page of the wizard), you must make sure that the first fieldnames match those in the destination table. I would imagine that when you imported the text file "manually" that you imported into a new table, and now you want to import into an existing table. When you import into a new table the field names are irrelevant.

Once you have entered all the details into the wizard you can save the import definition to use it again, select the advanced button, then select Save as.. and enter the name you want to use for the specification.

Now you can run the import from code, look at the TransferText method in the help file.
0
 
LVL 1

Author Comment

by:ny971181
Comment Utility
could you give me more details?
Thank you

Chris Lee
0
 

Accepted Solution

by:
pttt earned 10 total points
Comment Utility
The best way to import a text file in a module is to first import the text file manually into a new table.

First select "New" on the database page of your project, this will run the database wizard. Choose "Import Table". Select the text file you want to import. Then, select whether your text file is Fixed Width or Delimited, and click the "Next" button. This page allows you to either set the delimiting character or the width of the fields. When you have done this select "Next" again.

Choose "In a new table" and move to the next page. Now you can set the field names for the new table, name each field with a name which reflects the data contained in the field as this will make it easier to track down any problems. Click on each column in the displayed table and change the field name.

Move to the next page and set the primary key if required. On the next page enter the name for the new table, something like "Test" will do. Now run the import, by clicking the "Finish" button.

If everything went to plan you should now have a new "Test" table which has the same structure as the table you want to use. Open the table and check that all the data was imported correctly.

If the import was OK then it is time to create the import specification. Select "New" again from the database page. Again choose import and select the text file to import. Select "Fixed Width" or "Delimited". Set the delimiter or the field widths. Choose "In an existing table" and select the table you want to import to.

Click the "Next" button and you will be taken to the last page of the wizard. Click the "Advanced" button, this will allow you to save the import specification. Check the details are correct and click the "Save As.." button.

If you get an error message such as "The field 'Name' does not exist in the table or query you have chosen for your import or export operation." then it means that your table has too few fields to hold all the data in the text file. Cancel the wizard and compare your "Test" table with the one you are trying to import into and make the necessary changes to the import table.

Once you have corrected any problems run the wizard again. When prompted for a Specification Name enter something like "My Import Specification", there is no need to complete the import once you have saved the specification.

Now onto the module. To use the specification enter the following line, (or one similar)

DoCmd.TransferText acImportDelim, "My Import Specification", "MyTable", "MyTextFile"

Where:      "My Import Specification" is the name that you saved the specification to.      "MyTable" is the name of the table you wish to import into, and "MyTextFile" is the name and path of the text file to import from.

Note: I used the acImportDelim constant, if you are importing from a fixed width text file you need to use the acImportFixed constant.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
EXCEL 2010 7 39
Excel If tests 10 53
Excel VB Help 4 36
How to convert JSON file to csv? 7 49
This article will show you how to use shortcut menus in the Access run-time environment.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

772 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

12 Experts available now in Live!

Get 1:1 Help Now