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

Re: Importing text files into MS Access using a Macro

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...

chimambo@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

Re: Importing text files into MS Access using a Macro

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...
>
> chimambo@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

Re: Importing text files into MS Access using a Macro

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...
>>
>> chimambo@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

Re: Importing text files into MS Access using a Macro

On 3 Dec, 23:28, Fester Bestertester wrote:
> 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