473,320 Members | 1,990 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Importing text files into MS Access using a Macro

I have 2 problems:

1. I want to import a single text file into an access table using a
Macro. I am however getting an error that I need to put a
specification name argument. What does this mean? It also suggests
that I add a schema.ini in the source folder. What does this .ini do
and how do I create and use it?

2. After this is resolved, I would like to be able to import several
text files using a macro into the same table.

Thanks
Dec 3 '07 #1
4 12467
I'm sure others will be able to shed more light on this:

To create an import/export specification, launch the Import Wizard:

File | Get External Data | Import...

Point to the desired txt file

Select the "Advanced..." button

Specify the field delimiter, the text qualifier (you might want to
select "" in case some of your incoming text fields have punctuations,
etc.), and the field names and data types, etc.

Then select "Save As" and give that set of specifications a name.

You can now use this set of import/export specifications in a macro or
in a VBA module.

To see these specs as they are saved in the mdb file, select Tools |
Options | View, and check System Objects. You'll now see the tables,
MSysIMEXSpecs and MSysIMEXColumns.

PS you might want to "unshow" these system objects once you've looked at
them so you don't accidentally hose them...

ch******@googlemail.com wrote:
I have 2 problems:

1. I want to import a single text file into an access table using a
Macro. I am however getting an error that I need to put a
specification name argument. What does this mean? It also suggests
that I add a schema.ini in the source folder. What does this .ini do
and how do I create and use it?

2. After this is resolved, I would like to be able to import several
text files using a macro into the same table.

Thanks
Dec 3 '07 #2
PS You didn't specify your version of Access (I'm still using 2K3, so I
don't know how/if this works the same in 2K7).

Fester Bestertester wrote:
I'm sure others will be able to shed more light on this:

To create an import/export specification, launch the Import Wizard:

File | Get External Data | Import...

Point to the desired txt file

Select the "Advanced..." button

Specify the field delimiter, the text qualifier (you might want to
select "" in case some of your incoming text fields have punctuations,
etc.), and the field names and data types, etc.

Then select "Save As" and give that set of specifications a name.

You can now use this set of import/export specifications in a macro or
in a VBA module.

To see these specs as they are saved in the mdb file, select Tools |
Options | View, and check System Objects. You'll now see the tables,
MSysIMEXSpecs and MSysIMEXColumns.

PS you might want to "unshow" these system objects once you've looked at
them so you don't accidentally hose them...

ch******@googlemail.com wrote:
>I have 2 problems:

1. I want to import a single text file into an access table using a
Macro. I am however getting an error that I need to put a
specification name argument. What does this mean? It also suggests
that I add a schema.ini in the source folder. What does this .ini do
and how do I create and use it?

2. After this is resolved, I would like to be able to import several
text files using a macro into the same table.

Thanks
Dec 3 '07 #3
PPS. You also stated you wanted to import several text files to the same
table using the same macro. This, it should go without saying, will only
work if all the text files have exactly the same layout, not just
because they're all going to the same table, but also because they all
(presumably) will use the same import specification.

Not only that, but as far as I recall, when you import a file, it comes
in as a new table object, so I think you'd have to do all the imports
first, then do a series of Append queries...(right? anybody??)

Fester Bestertester wrote:
PS You didn't specify your version of Access (I'm still using 2K3, so I
don't know how/if this works the same in 2K7).

Fester Bestertester wrote:
>I'm sure others will be able to shed more light on this:

To create an import/export specification, launch the Import Wizard:

File | Get External Data | Import...

Point to the desired txt file

Select the "Advanced..." button

Specify the field delimiter, the text qualifier (you might want to
select "" in case some of your incoming text fields have punctuations,
etc.), and the field names and data types, etc.

Then select "Save As" and give that set of specifications a name.

You can now use this set of import/export specifications in a macro or
in a VBA module.

To see these specs as they are saved in the mdb file, select Tools |
Options | View, and check System Objects. You'll now see the tables,
MSysIMEXSpecs and MSysIMEXColumns.

PS you might want to "unshow" these system objects once you've looked
at them so you don't accidentally hose them...

ch******@googlemail.com wrote:
>>I have 2 problems:

1. I want to import a single text file into an access table using a
Macro. I am however getting an error that I need to put a
specification name argument. What does this mean? It also suggests
that I add a schema.ini in the source folder. What does this .ini do
and how do I create and use it?

2. After this is resolved, I would like to be able to import several
text files using a macro into the same table.

Thanks
Dec 3 '07 #4
On 3 Dec, 23:28, Fester Bestertester <whatmewo...@mad.netwrote:
PPS. You also stated you wanted to import several text files to the same
table using the same macro. This, it should go without saying, will only
work if all the text files have exactly the same layout, not just
because they're all going to the same table, but also because they all
(presumably) will use the same import specification.

Not only that, but as far as I recall, when you import a file, it comes
in as a new table object, so I think you'd have to do all the imports
first, then do a series of Append queries...(right? anybody??)

Fester Bestertester wrote:
PS You didn't specify your version of Access (I'm still using 2K3, so I
don't know how/if this works the same in 2K7).
Fester Bestertester wrote:
I'm sure others will be able to shed more light on this:
To create an import/export specification, launch the Import Wizard:
File | Get External Data | Import...
Point to the desired txt file
Select the "Advanced..." button
Specify the field delimiter, the text qualifier (you might want to
select "" in case some of your incoming text fields have punctuations,
etc.), and the field names and data types, etc.
Then select "Save As" and give that set of specifications a name.
You can now use this set of import/export specifications in a macro or
in a VBA module.
To see these specs as they are saved in the mdb file, select Tools |
Options | View, and check System Objects. You'll now see the tables,
MSysIMEXSpecs and MSysIMEXColumns.
PS you might want to "unshow" these system objects once you've looked
at them so you don't accidentally hose them...
chima...@googlemail.com wrote:
I have 2 problems:
>1. I want to import a single text file into an access table using a
Macro. I am however getting an error that I need to put a
specification name argument. What does this mean? It also suggests
that I add a schema.ini in the source folder. What does this .ini do
and how do I create and use it?
>2. After this is resolved, I would like to be able to import several
text files using a macro into the same table.
>Thanks- Hide quoted text -

- Show quoted text -
Thanks a Million, the import has worked. I will now try to do the same
for several text files at once.
Thanks once more

Cheers
Dec 6 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Debbie CD UK | last post by:
I have a large amount of log files from an electronic contro tester that I need to import in to an Access database Unfortunately, they are not comma delimited or fixed with but different fields...
1
by: D Mat | last post by:
Hi, I'm trying to get MS Access 2000 to automatically import a series of (~200) flat text, tab delimited, data files into a single Access table, with consistent fields and rows. The files have...
8
by: harry | last post by:
Hi Folks: I'm trying to do something that looks simple, but I can't make it work right. It's Access 2000 on a Win2000 computer. I create a database with 5 columbs. The data I need to import...
17
by: OdAwG | last post by:
Just some questions regarding tables. I am new Access Database and need a little help. I have the following data listed below 01. I have a table called tbl_Customer with the following...
5
by: hharriel | last post by:
Hi, I am hoping someone can help me with an issue I am having with excel and ms access. I have collected data (which are in individual excel files) from 49 different school districts. All...
1
by: Kosmos | last post by:
Hey guys, I'm fairly familiar with access by now, but I've never worked with importing text into an access database. I have some fairly large text files (lets say, for example, a folder of 20 text...
1
by: jith87 | last post by:
hi guys, i need to automate the process of importing txt files in a folder into the same table in access.i have created a macro todo the job.the following are the probelms that i face: 1....
1
by: gollumullog | last post by:
Good Day, I have been having this issue for quite a long time, and have yet to find an easy/elegant solution. I am trying to create tables in an Access database. I have these tables as CSV...
1
by: aconti74 | last post by:
Hello I am new to vba programming/coding. I am writing a program that goes through a directory of text files and imports them into the database. The problem is eventually the database gets to big...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.