TransferText import macro

[zogpound]

I'm trying to create a transfertext macro to import a delimited .txt file. I keep getting a "microsoft jet engine could not find XXXXfile. The file path name I believe is correct.

My situation is this: I receive several daily .txt file reports via e-mail. I want to automate importing these files into my database. My access skills are limited. Is the transfer text the best way to accomplish this task. Any suggestions which would save me from having to save and rename the file attachments each day.

Thanks, Eric

 

[Dgavilanes]

Importing excel file into MS Access

Well I just finished creating a macro that imports an excel file into Access.

First thing you do is import that delimited file into an exell file.

then

1. Name the new exel file master (create a permanent file location in your computer
example D:/ Master

2. go to MS Access and import that excel file
3. on the last step when importing name that MS table "Temp"

now you have an identical table in Access with same number of fields and filed names (correct)

4. Delete the data from "temp" so is empty

5. go to macros and click "new"

6. for Actions select "Treansferspreadsheet. then

7. for transfer type select "Import"

8.for table name select "temp"

9. for file name type the location of the excel file you created in step 1... Example d:/Master~1.xls
(If you right click on the excel file you get this from properties

10. has fields name s leave "no."
11. close and name the macro example "mcdmimport"

now you go to a form, create a button without the wizard and in the event procedure select the macro name you just created.

run the form and click on the macro,,,,,what it does if there is no mistakes is it runs without any warnings, it finds the excel file in step 1. and transfer it to the temp file.
open temp table and see if the number of records is the same as original file
now after importing you could append the temp table to your actual datatable.

for your E- mails if you get it in excel filetype.

open your excel file and save as "Master" in the same location you saved the original file you created before step 1. example d:/Master

you will get a warning telling you you allready have a file name "Master" would you like to replaced you click "YES"

This is how you update your Master (excel File)
then go to MS Access and follow the routine outline before..

good luck


Dennis

 

[tawkkal]

fyi...

i receive file in csv format ... which i import to access. for doing this... i first import the file in excel... & then transer the same to access.. by doing this i m able to cross check if every thing is working fine or not.. while importing in excel i can notice if there is any error & fix.. it.. then transfer the same to access...

Any suggestions on this...

 

[Ziggy1]

you don't need to work with Excel, just use the transfer text macro action (can convert to VB). But first you need to create an Import Specification template, you do this when you create your table during the initial import (make a new table for testing).

Select Import on the tables TAB, and change the files of type to txt,CSV... the nxt screen you will see a button "ADvanced". Click it and in there click "save as" and here you create the name....click OK and proceed throught the Wizard, decide how you want your table to look (field headings, datatypes, key etc). in the end you will have your imported new table.

Now in a Macro, use the transfer text action. you will now see the spec file in the drop down (for the arguments below). fill in all the details , the Filename will be the complete path to the file including the file extension.

Similar to what Dennis was saying i create a Temp table which I would append to the main table with append queries. I ussually try to create a unique key that can't be duplicated (depends on your data), it could be a combination of fields. I set that field to not allow duplicates in the main table, than the append query has the "Key" field so when you append, the duplicates will not get imported (you can turn off the warnings).


As far as saving your attachment, your e-mails can be your backup, then whenever you want to import a file, got to the folder where it is to be saved,select the file in that folder and click save and yes to replace the file., then run the Access Macro (via button on form or whatever).....The macro should be designed like this...

1st action Delete query to delete data in Temp table
2nd Transfer Text action
3rd Append query


Ziggy

 

[bluenose76]

I'm trying to create a transfertext macro to import a delimited .txt file. I keep getting a "microsoft jet engine could not find XXXXfile. The file path name I believe is correct.

My situation is this: I receive several daily .txt file reports via e-mail. I want to automate importing these files into my database. My access skills are limited. Is the transfer text the best way to accomplish this task. Any suggestions which would save me from having to save and rename the file attachments each day.

Thanks, Eric

Hi Eric,

I have just een doing exactly what you are trying and indeed i was trying to use a Macro. Thanks to a chap from this forum i have got my DB working fantastically but i used an OnClick event procedure instead of a macro to do the work! and i take my data directly from a .CSV file and dont go anywhere near Excell.

Here is that code thanks to Anthony George:

Insert the following code into a Command button's on click event (i've used a button caled Command38)

The first row of the code deletes the old table Table1 (note! the first time you run this, an old table 1 must exist, any old

table will do even an empty table with just one field, so long as it is called Table1)

The second row of the code imports the file (My file is called MyExcelFile.csv, in a folder called aMacrotest, on the C

drive, obviously you can use any file,table,and folder names that you want so long as they match with the rest of the code)

The other 4 code rows rename the column headings.


Here is the Code:


Private Sub Command38_Click()
On Error GoTo Err_Command38_Click


DoCmd.DeleteObject acTable, "Table1"

DoCmd.TransferText acImportDelim, , "Table1", "C:\aMacroTest\MyExcelFile.csv", False

CurrentDb.TableDefs("Table1").Fields("F1").Name = "Make"
CurrentDb.TableDefs("Table1").Fields("F2").Name = "Product"
CurrentDb.TableDefs("Table1").Fields("F3").Name = "Packing"
CurrentDb.TableDefs("Table1").Fields("F4").Name = "Qty"

Exit_Command38_Click:
Exit Sub

Err_Command38_Click:
MsgBox Err.Description
Resume Exit_Command38_Click

End Sub


As you can see from this, it allows me to rename teh columns of the table after importing the data, This means that when i run a Query, my results make sense instead of being in columns named F1 F2 F3 etc etc

I hope that this helps you

Bev

 

[Malakai7]

Import different spreadsheets from the same Excel File

I have a different problem.
Is there any possibility in Access to import differents spreadsheets which are part of on file.
For example I have One spreadsheet that is structured as a template and every user fills it. From this template the information is linked to another two "data" spreadsheets.
In the past the "data" sheet was only one and Access automatically decided which of the two spreadsheets to import (the data or the template).
But know I created and second data sheet and wanted to create and second Macro in Access which to import the information only from the "data2" spreadsheet. The problem is that Access autmatically takes the information from the first spreadsheet in the file. Is there some option to show Access from which spreadsheet to take information?

Thanks in advance

 

[Ziggy1]

In you macro design, there is a field for "RANGE", put something like Sheet2!A:Z here. Youl have to figure out what range you need. you can make it more specific with the row numbers, otherwise just use the columns