Solved

Access97: import textfile specification error :(

Posted on 1997-07-07
3
373 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
ID: 1602992
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
ID: 1602993
could you give me more details?
Thank you

Chris Lee
0
 

Accepted Solution

by:
pttt earned 10 total points
ID: 1602994
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
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.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

825 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