Changing Import Spec

[lazydays]

I use an import specification set up through the import wizard.

However, there may be a column added or deleted in certain months, and I need to be able to change the import spec.
Is this possible?

If not, is there any way the import spec can be written in VB?

Thanks!

 

[smedvid]

A cheap work-around for me was to create a multitude of import specs. Then based on user defined selection, type of import (i.e. 6 or 7 columns) I would define the code to use that specific Import Spec.

I believe that table that stores the import spec is read-only. I tried to manipulate it manually once many moons ago...

Another work-around, less favorable is to perhaps read the import file manually via Input statement... I've used this approach to parse very complex text files in the past. It is the most flexible, but also not as efficient as using the import spec.

hthw.

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[lazydays]

Thanks Steve,

Unfortunately, I can't really make up a few different specs due to the nature of the data I am importing.

Looks like I will have to set up a new spec each time the file changes.
Pity, I wanted to make this as user friendly as possible!

Thanks for your help anyway!

 

[DrSimon]

lazydays - have you looked at using schema.ini? This allows you to describe the properties of the input file. Let me explain how I'm using it.
I have a client who needs daily extracts on a spreadsheet from a non ODBC system. I've written a background task that produces a delimited flat file, but it can have a different structure from day to day, so the task also produces an equivalent schema.ini file. Access then fires up, runs a macro which calls VBA code and creates a new link to the flat file and then exports the spreadsheet. I happen to create a link, but the concept works OK with Import too.

There is a reasonable amount of info about schema.ini on the Microsoft site. Try "How to Use Schema.ini for Accessing Text Data":

http://support.microsoft.com/defaul...port/kb/Articles/Q149/0/90.asp&NoWebContent=1

Simon Rouse

 

[lazydays]

Thanks Simon,

I'll look into that!

Sandra

 

[smedvid]

Me also... Looks interesting. never heard of that approach before. thanks... may need it one day.

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[lazydays]

I am having trouble getting the schema.ini file to work.

I have copied the example from the microsoft article, but keep getting error 13 - Type mismatch, on the following line:

set rs = db.OpenRecordset("Contacts.txt&quot

I have only very basic knowledge of VB, so is there something I need to add in the reference library or anything?

 

[smedvid]

May need to have location of where schema.ini is located in your PATH statement. Just a guess...

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[lazydays]

I have tried to put in the full file path in this line, but it brings back the message that it can't find the file.
The file path is definitely correct!

 

[smedvid]

I was thinking the OS PATH statement. Perhaps it can not locate the Schema.ini file. Exit to DOS and type PATH to see if the location of where Schema.ini is located is included within the current path. If not, perhaps add it? Again, just a shot in the dark...

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[DrSimon]

Hi Guys, wasn't around for much of yesterday.
You shouldn't be having problems, and it may be that the schema isn't quite right.
I've have found a much simpler alternative - use TransferText to import the table. I must admit the Help file is more tantalising then helpful when using schema.ini. All you have to do is leave the SpecificationName field blank as in:
DoCmd.TransferText acImportDelim, , TableName, FileName, False
The FileName needs to include the path and the schema.ini must be in the same path.

Sandra, if this doesn't work, post an example of the text file and the schema.ini and I'll see if anything is wrong.
Simon

 

[lazydays]

Simon,

I've found a way round this now - export the table MSysIMEXColumns which is created when you create a spec.
I have tested this and it works!

Thanks for your help though!

Sandra