Where are the specs for the Import Specification?

Question

A year or so ago I created an import specification for importing an Excel

spreadsheet into Access 2007. So far it works on the monthly import, but if

they change the spreadsheet in the future it isn't going to work any more.

Where are the settings for the columns and field types, etc saved so that it

will be easy to modify them if needed? Becuase if I had to rewrite the

specs now for the current one I have completely forgotten what my parameters

and specs were! All I can find is a place to edit the description of the

specs i.e. "This imports the monthly cost report".

Mich

 

 

Answer 1

Mich,

 

Here you go...

http://www.access-diva.com/d6.html

 

--

Gina Whipp

2010-2012 Microsoft MVP (Access)

 

"MicheleS1" wrote in message news:6e2eebc7-c61f-4aeb-9c17-cde63c4e9f31...

A year or so ago I created an import specification for importing an Excel

spreadsheet into Access 2007. So far it works on the monthly import, but if

they change the spreadsheet in the future it isn't going to work any more.

Where are the settings for the columns and field types, etc saved so that it

will be easy to modify them if needed? Becuase if I had to rewrite the

specs now for the current one I have completely forgotten what my parameters

and specs were! All I can find is a place to edit the description of the

specs i.e. "This imports the monthly cost report".

Mich

 

 

---

-- Gina Whipp 2010 Microsoft MVP (Access) Please post all replies to the forum where everyone can benefit.

Answer 2

Oops, you said Import...

 

http://www.access-diva.com/d7.html

 

--

Gina Whipp

2010-2012 Microsoft MVP (Access)

 

"MicheleS1" wrote in message news:6e2eebc7-c61f-4aeb-9c17-cde63c4e9f31...

A year or so ago I created an import specification for importing an Excel

spreadsheet into Access 2007. So far it works on the monthly import, but if

they change the spreadsheet in the future it isn't going to work any more.

Where are the settings for the columns and field types, etc saved so that it

will be easy to modify them if needed? Becuase if I had to rewrite the

specs now for the current one I have completely forgotten what my parameters

and specs were! All I can find is a place to edit the description of the

specs i.e. "This imports the monthly cost report".

Mich

 

 

---

-- Gina Whipp 2010 Microsoft MVP (Access) Please post all replies to the forum where everyone can benefit.

Answer 3

I'm not asking how to create the specification file... I created and saved

the specification file a long time ago. But I don't recall how I structured

the file it created, it just works each month to import the data. I then

run code that processes that data several times and creates multiple tables

from it.

 

My question is: If they change the structure of their spreadsheet, where

can I look at the breakdown in the Specification File I created last year,

so that I can modify it to work with the new spreadsheet file they send me.

I'm being a pessimist about the spreadsheet file structure because of the

source!

 

In older versions of Access you could see the breakdown; e.g. date, text,

memo, currency, etc and make adjustments if needed before you used the spec

file.

 

Mich

"Gina Whipp" <=?utf-8?B?R2luYSBXaGlwcA==?=> wrote in message

news:6f069a4c-aa07-46e1-aad0-23f2cfdcfa8c...

Oops, you said Import...

 

http://www.regina-whipp.com/index_files/Import.htm

 

--

Gina Whipp

2010 Microsoft MVP (Access)

 

"MicheleS1" wrote in message news:6e2eebc7-c61f-4aeb-9c17-cde63c4e9f31...

A year or so ago I created an import specification for importing an Excel

spreadsheet into Access 2007. So far it works on the monthly import, but if

they change the spreadsheet in the future it isn't going to work any more.

Where are the settings for the columns and field types, etc saved so that it

will be easy to modify them if needed? Becuase if I had to rewrite the

specs now for the current one I have completely forgotten what my parameters

and specs were! All I can find is a place to edit the description of the

specs i.e. "This imports the monthly cost report".

Mich

 

 

 

 

-- Gina Whipp 2010 Microsoft MVP (Access) Please post all replies to the

forum where everyone can benefit.

 

 

Answer 4

You must have missed this part...

(Will work for Import or Export File Specifications)

           Follow Step 1 (selecting any file as you will not be importing it) thru Step 3

           Then click the Advanced button and select the Specification file you want to modify

           Modify and click Save As…  At this point you can either give it a new name or save

             overwriting the existing file name.

---

-- Gina Whipp 2010 Microsoft MVP (Access) Please post all replies to the forum where everyone can benefit.

Answer 5

I followed your instructions -

In Step 3 I am asked

Which worksheet

Is the first row column headings (Yes)

Field name, dtat type and Indexed? for each field

Primary key

Name of Table to import into

Save the steps as a spec file? Yes

 

In none of these steps was there the advanced button you show in your screen

shot.

 

If I try my original saved specification file my only options are to edit

the description, or run it. Modification is not an option.

 

Am I missing something here?

 

Mich

 

"Gina Whipp" <=?utf-8?B?R2luYSBXaGlwcA==?=> wrote in message

news:caef6b34-9741-42a3-8683-c627649feac7...

You must have missed this part...

(Will work for Import or Export File Specifications)

Follow Step 1 (selecting any file as you will not be importing

it) thru Step 3

Then click the Advanced button and select the Specification file

you want to modify

Modify and click Save As… At this point you can either give it a

new name or save

overwriting the existing file name.

 

 

-- Gina Whipp 2010 Microsoft MVP (Access) Please post all replies to the

forum where everyone can benefit.

 

 

Answer 6

Michelle,

I am sorry, never noticed that option was not availalbe for Excel worksheets.  I am going to have to modify my instructions.  Never even noticed they removed it and it has been removed for Excel worksheets, that I just confirmed.

Looks like there is no way to modify the specifications of Excel worksheet.

---

-- Gina Whipp 2010 Microsoft MVP (Access) Please post all replies to the forum where everyone can benefit.

Answer 7

MS did us a great dis-service by removing this option - I hope my import

spreadsheet never changes, but I just know it will...!

 

Thanks for showing me where it SHOULD have been!!!

 

Mich

 

"Gina Whipp" <=?utf-8?B?R2luYSBXaGlwcA==?=> wrote in message

news:499a60e9-0581-4492-9896-a627477f7aa1...

Michelle,

I am sorry, never noticed that option was not availalbe for Excel

worksheets. I am going to have to modify my instructions. Never even

noticed they removed it and it has been removed for Excel worksheets, that I

just confirmed.

Looks like there is no way to modify the specifications of Excel worksheet.

 

 

-- Gina Whipp 2010 Microsoft MVP (Access) Please post all replies to the

forum where everyone can benefit.

 

 

Answer 8

SNIFF SNIFF... yes, I know how you feel!

---

-- Gina Whipp 2010 Microsoft MVP (Access) Please post all replies to the forum where everyone can benefit.

Answer 9

Ridiculous!

Yet another time-saving feature ripped out of office 2010 without basis or explanation.

The past 15 or so minutes I've spent CASUALLY looking for a solution, by golly I haven't run into ONE post/person saying "Gee I'm sure glad they removed this feature. MS sure is on my side, making my job easier every day!"

No, on the contrary, people are confused and ticked off.

Access 2010 sux.

Answer 10

Convert Excel to a .csv file by opening and doing a "save as". 

Now, in Access, when you create a link to the .csv, you will have the "advanced" option so you can pick data types.

Answer 11

This is madness to have this behaviour for excel spreadsheets.

I'm finding that, once setting up an "Import Data" specification for Excel, if I re-run it, it forgets the specification and throws errors: it forgets the added autonumber ID field ("Let Access add primary key" setting in the "Import Spreadsheet Wizard") and actually deletes that primary key field that was created as part of the initial specification. It then throws a

"Cannot Delete a field that is part of an index or is needed by the system"

error when it is re-run. The import actually changes the structure of the table from when it was first part of the specification to when the import was re-run.

none of my CSV imports go through this hassle.

"Convert Excel to a .csv file by opening and doing a "save as". "

Thank you but that's a horrible work-around in my case. With manual daily imports of up to a dozen spreadsheets, that's a dozen extra steps that shouldn't be necessary. My data is only available as a spreadsheet and wiping out existing data in tables and replacing it with fresh stuff via the external data imports is painful enough as it is. No hope for automation, and linking instead of importing is impractical from a performance point of view, not when Access struggles as it is with some of the complex queries I get it to run.

"Access 2010 sux."

+1. I wish they spent time not breaking functionality instead of wasting it on that unnecessary ribbon navigation.

I might look around to see if OpenOffice or something else will work better now that I've used Access to create all my SQL for the reports and lookups I need.

 

Answer 12

They did not remove that feature to my knowledge. When you import an Excel sheet, you do not and can not and did not save or nor see the advanced option to save an import spec.

 

So unless that Excel sheet was a csv file (in that case you use transfer text), then I am not aware how and where you were specifying a import spec for a excel sheet when you never had the option to do so?

 

In fact, at least in Access 2010 you can in fact save that import along with the fields that you edit. So while you do not have an advanced button (and YOU NEVER DID), then in a2010 you can in fact rebuilt the import and save it.  You could not do this in a2003. To re-run the saved import you go:

 

docmd.RunSavedImportExport "name of saved Import or export"

 

So you have A BETTER option and a means to import excel now then you did in the past.

 

So the Access 2003 import wizard for example NEVER showed or displayed the Advanced button for Excel sheets. So no need to get all twisted up because you cannot trust your memory anymore.

 

Albert D. Kallal  (Access MVP)

Edmonton, Alberta Canada

Answer 13

No, it was not changed....

Answer 14

Goodness - that's harsh!

 

I reinstalled a copy of Access 2003 today and found that the only time the

advanced button shows up is if you use, as you say, a text file such as a

csv file, so I must have exported the spreadsheet before importing it into

Access.

 

At the time of my initial post I was using Access 2007, and I ended up

having to transfer the new spreadsheet into a temporary table, then

manipulate the data from there.

 

I had remembered changing the spec file in the past, but hadn't remembered

that it was a text file. I guess that's what happens when the memory starts

going...

But thanks for the memory JOLT!

 

Mich

 

 

"Albert D. Kallal" <=?utf-8?B?QWxiZXJ0IEQuIEthbGxhbA==?=> wrote in message

news:ffe2c3f9-088c-44ab-98bd-8ced824940df...

They did not remove that feature to my knowledge. When you import an Excel

sheet, you do not and can not and did not save or nor see the advanced

option to save an import spec.

 

So unless that Excel sheet was a csv file (in that case you use transfer

text), then I am not aware how and where you were specifying a import spec

for a excel sheet when you never had the option to do so?

 

In fact, at least in Access 2010 you can in fact save that import along with

the fields that you edit. So while you do not have an advanced button (and

YOU NEVER DID), then in a2010 you can in fact rebuilt the import and save

it. You could not do this in a2003. To re-run the saved import you go:

 

docmd.RunSavedImportExport "name of saved Import or export"

 

So you have A BETTER option and a means to import excel now then you did in

the past.

 

So the Access 2003 import wizard for example NEVER showed or displayed the

Advanced button for Excel sheets. So no need to get all twisted up because

you cannot trust your memory anymore.

 

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

 

 

Answer 15

No worry.

I hope I was not too harsh.

There is nothing at all wrong with people making errors here (it happens to everyone including me).

So there is no special deal about being wrong here. We are all here to live and let live and help others here.

It just not a big deal that some feature we thought existed never was. That is no more of a deal then some crumb falling off my donut during a coffee break today. No big deal here.

However when I pop my head up is when others reading this wrongly assume that some feature was removed when in fact it was not. The next thing you know is then 20 Access blogs have posts stating that such and such feature was removed and how bad this is and how features that we had and need are being removed. The whole thing then turns into a chorus line of crying people complaining how bad Access 2010 is based on false rumors.

The issue is not some about mistake made here, but to put a nails in a bad rumor or incorrect statement that has potential to spread, and spread fast. Such cases can result in a lot of damage and a lot of work to correct.

 

So, have a great day, and I thank you for your follow up.

Albert D. Kallal  (Access MVP)
Edmonton, Alberta Canada

 

Answer 16

The specs are contained in MS Access System Tables which are hidden by default. You can unhide the tables from the 'Tools->Options...' Menu (Acc 2003). Then view the MSys IMEX Tables where the deffinitions are located. If you know all the Index IDs for the Field Types you could alter the Deffinitions in these tables, However it is much easier to run the Import Wizard and select the Advanced Options to view or define the Import Specifications using the Advanced Import Specifications Dialog.

Use the Import wizard "Advanced" Specifications to save or edit the import specifications.

These are the System Tables containing the saved Import Specifications.

Table: MSysIMEXSpecs
Table: MSysIMEXColumns

Answer 17

For Access2010

Access Options-> Current Database->Navigation Options -> Display Options

select Show System Objects

Close Access and re open it

---

Li

Answer 18

In Accesss 2007 I have been able to modify import specifications,

Go to the Navigation Bar and select navigaion options.

Check the box that says sow sytem objects

MSyssIMEX specs has a list of specifications. Find you rspecification and note the index number

Then Look at MSysIMEX columns using that index number and you will see a row for each column in your spec.