Importing TXT file into Access

[ajolson1964]

I am importing a TXT file into a table in access. Each field and each comma delimator has been set up and there are no errors in importing the text file.
What I want to do is set up a command button and a function so that the user can click on a command button and import the file from a saved TXT file in a folder. Any help in this would be greatly appreciated. For training purposes lets say the file name is test.txt and it is located at C:\ImportFiles\test.txt.

 

[MCuthill]

ajolson1964,

Took a look on google and found the following:

Question Posted:

This is my problem: I need to import a text file (.csv) into Access, using a macro. Can someone help me doing this? I am conversant with Excel VBA but am quite new to Access.

Reply:

DoCmd.TransferText acImportDelim, "", "Table1", "C:\Winxp\testing.csv", True, ""
' where table1 is your table

I have attached the website for your reference.

Hope this helps.

Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"

 

[ajolson1964]

Thanks it did help, as I am getting the file to open up and start the import possess. However I am having a bit of an issue. It might be with appending or field type not to sure.

Here is the issue. I now get an error message that says there is no A5 table.

Here is some background.
What I have set up is a database with a table named “Wells” that has 5 fields. The first field within the Wells table is a TEXT field named API (it also is the primary key).
I also have a test file set up to import into the Wells table. It is set up with 5 comma delaminaters with data (string) before each deleminator. The first data within the test file is A5.


The problem is that A5 is actually data that needs to be stored in the field API. Is this a append issue? Again any help would be greatly appreciated.

 

[PHV]

Put your cursor inside the TransferText word in your code and then press the F1 key.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ajolson1964]

Thanks again.

What I can tell is going on is that Access sees(or wants to use) the fist line of data in the test.txt file as the field names and when it can't it gives me that error message because I already have assigned feild names.

What I want it to do is add new records to a exsisting Table via a funtion. Here is the code I have so far
DoCmd.TransferText acImportDelim, "", "Test", "N:\shared\andy\testinputdocwell.txt", True,

 

[PHV]

Seems you didn't read the help !
DoCmd.TransferText acImportDelim, "", "Test", "N:\shared\andy\testinputdocwell.txt", [!]False[/!], ""

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ajolson1964]

I know I am getting close to solving this issue however,
I am still having problems.

I did read the help but F1 only told me that true was yes or no data. But when I rebuild the line of code I now understand that the false and true at the end of the statement is stating in the import text is the first line the field name.

With that said when I change the code to read "False", DoCmd.TransferText acImportDelim, "", "Test", "N:\shared\andy\testinputdocwell.txt", False, "" Access assigns it own field names when importing. F1 F2 F3 ect. F1 F2... does not match the feild names in the destination Table of the database. The field name within the table is completley diffrent. It seems this line of code is for importing a text file into a new table.

Is there a line of code that will import data into a exsisting table by appending, then (if need) egnoring dupicate records? OR is the code I am useing the correct one but I am somehow jacking it up?

 

[PHV]

A common way is to import your TXT file into a temporary table (or use a linked table), validate the data and then use an append and/or update query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ajolson1964]

That did the trick.
I have the file upload to a Temp Table then use a query to append the destination table. All dupicates are ignored and new files are appended into the table.

Now if I could get all the error messages to stop during the prossess it would be Perfect.

 

[PHV]

You may condider the DoCmd.SetWarnings method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ajolson1964]

PHV,

That did the trick, Setwarnings to false run the other docmd's, then setwarning to true. Thank you So much for your help. I am sure I will have other questions. But again Thans