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

 

[DickyP]

The old style import feature is far more flexible.

Agreed, but with the rider that we are talking just about text files.

The problem is one of nomenclature - Microsoft using the term specification for two separate, albeit related, purposes.

The new Import/Export Specifications are, at heart, just saved import and export routines for repetitive tasks for various types of files. This is a useful facility but should have been named something else.

The old style was / is a specification of the data being exported/imported in text files. This is very important especially for importing CSV files to a table in Access where you need to define datatypes which, by design, aren't defined in the CSV file itself. This usage of specification is often critical for using TransferText especially in bulk mode.

One minor problem is that the trad version of specs doesn't deal directly with newer data types like LongLong and Decimal.

 

[Pat Hartman]

The reason I prefer the old style import is because you get to specify the file name on import/export each time. With the newer version, the file name is fixed rather than variable and that is inflexible. Colin has posted methods that explain how to update the MSys tables and while that works fine, it is not obvious how to discover this.

In neither case are "specs" available for links to spreadsheets but "specs" are available for imports of spreadsheets although I don't think they are reusable. I've never tried.

 

[DickyP]

Colin has posted methods that explain how to update the MSys tables and while that works fine, it is not obvious how to discover this.

At least the MSys tables can bee seen and updated in VBA code. The new Import / Export are completely opaque.

Some years ago I built an analogue of the dialogue that appears when click the Advanced button, similar to isadogs's offering. When I have successfully modified its usage of my routines in my library Utils.accde library database to make it standalone I'll put it on the forum for use as anyone sees fit (and to hack to their hearts content).

Picture attached:

 

[Pat Hartman]

That would be useful. The problem with the built in dialog is that it uses begin/end rather than just length which makes it virtually impossible to update easily.

 

[DickyP]

That would be useful. The problem with the built in dialog is that it uses begin/end rather than begin/length which makes it virtually impossible to update easily.

I just showed an example of delimited but the dialogue for fixed does show the begin end columns.

The joy of this utility is that you can create and edit specs independently of actually importing and exporting. And it create the necessary MS tables if they don't exist.

I'm almost ready to deploy it, plus 2 Word templates for print off a spec. I think the only dependencies left are the need to have references to Office and Word, plus reference to scrrun.dll for File System Object. The proof will be when somebody tries to use it!

 

[ebs17]

The joy of this utility is that you can create and edit specs independently of actually importing and exporting.

In the old style, the specifications are in two system tables. Once you understand these tables, you can also change the relevant record via query/recordset and thus fully automatically during the import.

 

[DickyP]

In the old style, the specifications are in two system tables. Once you understand these tables, you can also change the relevant record via query/recordset and thus fully automatically during the import.

That's exactly what the utility does!

 

[isladogs]

Just for clarification:
1. The old style IMEX specs (which are for text files only) can be edited directly in the 2 MSysIMEX system tables. My utility just makes it easier to do this by using a form
2. The new style IMEX data tasks are for a wider variety of files including Excel, Access & text files. These are stored as XML (NOT in the MSysIMEX tables). Although certain features can be directly edited from the wizard (name/file path etc), the bulk of the data task XML can only be accessed using relatively obscure ImportExportSpecifications VBA code. My utility was mainly designed to make editing the XML as straightforward as possible.

The app is available from my website

 

[ebs17]

That's exactly what the utility does!

Using a graphical interface for manual selection interrupts automated programmed processes.