open saved import external data vba code (1 Viewer)

[eugzl]

Hi All.
I imported external text file and saved that process. Is it possible to open and modify code of the process in VBA?

Thanks

 

[Gasman]

Have a read of https://isladogs.co.uk/aeu-22/files/clsImExSpecs.pdf

I was looking for the app that @isladogs created and came upon that.

Found it. https://isladogs.co.uk/view-edit-imex-data-tasks/index.html

 

[Pat Hartman]

The old style import feature is far more flexible.

 

[isladogs]

The old style import feature is far more flexible.

The old style approach only works for text files. The new style approach also works for other files such as Excel
My web article & example app covers both methods.

Each method has advantages & disadvantages

View & Edit IMEX Data Tasks

Use to view & modify saved import/export XML data tasks

isladogs.co.uk

 

[Pat Hartman]

The old style approach only works for text files. The new style approach also works for other files such as Excel
My web article & example app covers both methods.

When you import Excel, using the old method, you get to define the data types. When you link, you don't. I think the "new" import is the same.

 

[ebs17]

When you import Excel, using the old method, you get to define the data types.

This is clearly wrong. In a typical Excel table itself there are no column-related uniform data types - in an "intelligent table" as a later imitation of a database table in preparation for Power Query (internally a modification of SQL Server) there are.

With access via SQL / TransferSpreadsheet to a typical Excel table, Jet independently interprets the data types of the columns according to content, so you can't intervene yourself.
By default, this interpretation evaluates the first eight rows of data (registry key TypeGuessRows).
With this data type interpretation you are powerless and run into an error (created error import table) if, for example: there are numerical values in the first lines and an alphanumeric expression, i.e. text, much deeper.

But this was about importing from a text file, and the old method is much more flexible. This means you can integrate a link to the text file and import specification directly into a query, and with a query itself you can do a lot more than just blindly import everything as it is.

 

[Pat Hartman]

This is clearly wrong.

The master has spoken. If you say it, it must be so

 

[ebs17]

using the old method

means (for me):
- The import specification is stored in two system tables and can be viewed there.
- The import specification stored under a can be used directly as an argument via standard import or linking in the same way as with TransferText. Also directly within queries.
That won't happen, you have to dry your tears yourself.

As mentioned, a saved import (newer other method) also allows an import specification for Excel, but works completely differently in many ways.

 

[Pat Hartman]

@ebs17. Let me recap the exchange for you.
I said -
---- The old style import feature is far more flexible.
@isladogs said -
---- The old style approach only works for text files.
I said -
---- When you import Excel, using the old method, you get to define the data types. When you link, you don't. I think the "new" import is the same.
You said -
---- This is clearly wrong .... By default, this interpretation evaluates the first eight rows of data (registry key TypeGuessRows). With this data type interpretation you are powerless

To reiterate -- When you import a spreadsheet, you get the same dialog as you get with a text file that allows you to specify data types. The picture says it all.

 

[ebs17]

When you import a spreadsheet, you get the same dialog as you get with a text file that allows you to specify data types.

That's right. The crucial question is not the dialogue, but the conclusion of the dialogue and the subsequent use of the result.
If you do not save the import as such, the information for column data types and others will expire.

Can you use an import specification for TransferSpreadsheet (counterpart to TransferText)? That would be a result of a successful dialogue.
In what form can you use your recorded import specification directly?

=> A saved import is called with the name of the saved import. This is very different than calling an import specification.

I am of course talking about automated use of the import. If someone carries out the import by hand every time, they can operate the dialog again each time and get their result. But that would not be the achievement of a programmer.

The master has spoken

I take that as a compliment, even if it's undeserved.

 

[isladogs]

If I can interrupt your dialogue briefly, my original comment in post #4 was referring solely to the old style saved import/export specifications (which are saved in the 2 MSysIMEX ... tables and can only be used with text files) versus the newer XML based IMEX data task specifications.

I was not referring to code such as DoCmd.TransferSpreadsheet for which I believe the approach hasn't changed in 20 years or so - so there isn't an old / new style for that approach