importing a changing text file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a delimited text file with survey data from multiple courses. I'd like
to set up a macro or create code to import the data easily. I know i can
create a specification file, but the fields in the data file change from time
to time (old fields always remain, but occassionally new fields are added to
the file). There are also fields in the file that I don't want to import.

I have a separate delimited text file that lists the fields in the data file
and what course they belong to. If possible, i'd like to use that as the
basis for what fields in the data file I import (e.g., the separate file
indicates that fields A1-A10 and fields A36-A50 come from course A - which i
want to import, but fields A11-A35 come from course B - which i don't want to
import).

I imagine it would take code to set something like this up, but i don't know
how to do it. Since the number of fields in the data file can change over
time, I don't think i can use a specification file. I haven't worked with
schema.ini files before. Would that be the right way to go here? Is it
possible to create a schema.ini file on the fly and then import the data
file?

Thanks,
rachel
 
You can set up an import spec to import just the columns you want.

1. Choose Get External from the File menu.

2. In the Open dialog, locate the file, and open.

3. The wizard will walk you through the screen where you choose the columns
to import.

4. Click the Advanced button. You can now save an Import Specification.

If you have to do this regularly, you can automate this process with a macro
or code that uses TransferDatabase. That's where the saved Import Spec is
useful.
 
I know i can create an import spec file that i can use later, but one of the
issues i have is that the fields in the text file may change over time (new
fields get added). If i create a spec file based on the characteristics of
the text file today, and the text file changes tomorrow, won't there be
problems using the original spec file?

I'd rather not have to create a new spec file every time new fields are
added, in part because the text file contains hundreds of fields and it is
time consuming to go through and indicate for each one whether i want to
import the field or not.

thanks
rachel
 
Okay, can understand that.

But if you don't create the import spec, Access will just parse the file as
it sees fit. Whichever suits you.
 
That's why i was wondering if it was possible to create a spec file (or a
schema.ini file, which to be honest i know little about) on the fly using
code and the field information i have in a separate table. Does this mean
that's not possible?

rachel
 
Hi, Rachel,

I wonder if you have solved the problem by now. I have a similar problem.
The answer seems to be related to using a schema.ini file which you could
programattically alter much easier than manually changing the specification.
Everyone seems to be focusing on the specification file that is created when
you step through the Wizard for importing text. I think that not enough
information is posted on how to use a schema.ini file in Access (not getting
into the XML subjects which are much more complicated).

The theory is to use either an SQL statement with a database import driver
using something like DAO, or the built in Access feature in a programming
module called Docmd.TransferText ".....". With the TransferText command, the
schema.ini file would be used in the switches for these commands. However, it
appears that the schema.ini file is not always recognized and that is the
sticky point that may be getting skirted around in the posts on these support
forums. If you have solve this, please let us know, OK. Thank you,
Daniel
 
Daniel & Rachel,

For schema.ini documentation,

1) Search Access Help for "Initializing the Text Data Source Driver",
and when you find the article scroll down past the registry stuff to
where it deals with schema.ini

2) See also

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512

ACC: How to Use Schema.ini for Accessing Text Data
http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090

Use ODBC set up to create Schema.ini
http://www.devx.com/tips/Tip/12566

http://www.microsoft.com/accessdev/articles/daoconst.htm

Access will create a schema.ini file automatically if you export to
Microsoft Word Merge format.
 
Back
Top