Topics Search

Macro to import .txt file to a new table

Macro to import .txt file to a new table
Views: 1
I want to create a table by using a macro to grab a"data.txt" file off my computer. I need a Specification Name in the macro design but I'm not sure what to do here. I specified Import Fixed Width in the Transfer Type and I entered the complete file path and name in the File Name.

The Specification Name box is a drop-down but it is empty. The .txt file has two columns; first column starts in char position one and the second column begins in column 38.

My goal is to create this macro to import a.txt file into a table, each time deleting the previously imported data in the table (approx. 150,000 records) and stripping out certain characters using a query. The resulting query would then be exported as a new .txt (or .csv)
Sponsored Links:

More topics

 

Import txt files based on date

Preview
Here is the situation:

I want to import 3 recent txt files (based on date and time) to an access table. Each txt file is to be imported to it's table.

These txt files are located in a directory which is populated everyday with new txt files, hence the txt files keep changing.

A file is named as "Locations20120315.TXT" where 20120315 represents March 15, 2012.

How can I achieve this by click on a button
 

Navigate window to import txt

Preview
I need to import severla txt files daily with different names. The names of the files are are never the same. They are completely random.

I am hoping that there is a macro or vb code that will open a window that will alow me to navigate to the file location and allow me to choose the txt file. I already have the import specifications saved
 

TransferText import macro

Preview
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
 

Problem Using Macro to Import TXT file

Preview
I want to use a macro to import data from a comma-delimited text file and append its data to an existing table. I have created an 'Import Specification' in the Import Wizard and entered it as required into the macro design view --- along with the source data filename, table name, etc.

Problem occurs when I try to run the macro. It won't run and I get an error message (2590) that says the Import Specification "doesn't exist." I have double checked all spelling and case, but nothing seems to work.

I suspect the problem may somehow be related to security and 'trust' settings,
 

Access 2003: Import Multiple Text Files with different filenames using a Macro

Preview
I am using Access 2003, and I am trying to import several text files from the same location. Each text file will have a different name. For example:

From location:
Y:\Incentive Data Reports\Rejects\UPLOAD

I want to upload the following files:
HBACK-Rejects_06162010.txt
HBACK-Rejects_06172010.txt
HFAIR-Rejects_06182010.txt
HFair-Rejects_06222010.txt

The files are all formatted the same way, and I've created an import specification called (DM_Rejects). I want the files to be uploaded to the Table named "LoadFiles" in Access.

The file names will change daily.
 

Batch import .txt

Preview
I'm looking for help here and this is the problem. I have thousands of .txt file. Each .txt contains data of one person and there're multiple records/rows of this person in the .txt. The .txt files are tab separate delimited. 1. How do I import these .txt files into tables in Access? I don't want to do > File > Import a thousand times. 2. My goal is to stack these .txt files into one huge file that contains everyone's data. This should be a simple query if I have the tables (imported from .txt) linked to the primary key. Again, I don't want to set the primary key in each table and I don't want to draw the relationship lines a thousand times. Is there a way to do it? Any thought would be appreciated. Many thanks, Helena
 

Txt File Importing Question...

Preview
I use a madule to import multiple txt files. Is there a way to import a file and at the same time extract part of the file name to populate a column?

All the files I'm working with have the following format:
FileNameMM-DD-YY.txt
ex.
XYZ07-19-10.txt
XYZ07-20-10.txt
XYZ07-21-10.txt

When importing them is there a way to extract the date portion and have it populate a date column within the table? Unfortunately the file lacks the date in the data itself
 

import txt file to access 2007

Preview
I am trying to import a text file to a database but I am getting 3 rows for each record because the txt file is setup the same way. How do I fix the issue?

txt file stores the data like this:

line 1 name ssn dob
line 2 add city zip
line 3 pcp nurse

those 3 lines belong to the same patient and they're imported to access table the same way.
 

How to Create a Microsoft Access Macro to Import Information from a Text (.txt) File

Preview
Macros are operations used in Microsoft Access in order to automate functions in databases. They can be used to open reports, run queries, update the databases and import from various file formats such as Excel (.xls), database, (.dbf), and text (.txt).
This article will explain, in a step-by-step format, how to import information from a text file, format the information for Access, and create the macro so each step will not need to be created every day. This will save you time and put some of the database operations into the hands of the end-users and freeing you to do other things. Prior to creating the macro, the creation and saving of the import specification will be explained.
 

Text File Import - mindboggling error

Preview
I have a macro that imports a text file (to a table).

However, I am getting an error message back;

'You cannot import a textfile unless it has one of these extensions: TXT, CSV, TAB, ASC, TMP, HTM, HTML.'

Has anyone come across this before?

As far as I can tell the macro is pointing to the correct directory and the text file specified exists.

(Am running Access 11.6 on Windows XP SP2)