open saved import external data vba code

[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

 

[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

 

[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.

 

[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.

 

[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.

 

[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:

 

[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.

 

[DickyP]

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

It, like isladogs much more sleek and professional apps, is a simple design and development aid, and nothing to do with the operation of a database in use.

I built it for fun years back (probably in Access 2000) when I was still working and I've been retired for 15 years: it has proven very useful over the years. It was more of an exercise of learning how specs work: there is nothing particularly clever about it and that's why I'll make it available for people to play with, hack, destroy, criticise as they wish.

 

[ebs17]

I guess you don't understand the concept of the spec and how it is used.

Your conclusion is wrong. Of course, if text files always have the same structure, a specification only needs to be created once and then it will remain the same. This is the standard case, which is what we always aim for - with the emphasis on text files with the same structure.

However, text file suppliers often do not adhere to standards, but rather express their own "creativity".

Import csv to excel to access table

Hi, When I normally manually import a CSV file, I tend to convert it to xlsx first and then import, because I get no import errors this way. Now I'm trying to do this dynamically, and it works syntactically, but I want it to skip 12 lines and my delimiter is semicolon.. These two factors are...

www.access-programmers.co.uk

=> third paragraph
Here I decided that a continuous adjustment of the import specification and then the simple mass import is cheaper than a VBA construct with loops over individual values.
As I said, working in a standardized way is easy and preferable for good reasons, but the world is now more diverse. It's nice when one can find relatively easy ways to respond to the differences.

 

[DickyP]

Attached as promised is the utility I created in the mists of time.

This utility is offered 'AS IS' and is used at your own risk.

It is NOT of production quality and has some(lots of) rough edges. Especially it will fail if you try and assign anything to a column if there is no current Spec!

There is no dedicated support!

The content of the zip file is:

Specs.accdb - my development container and needs a spec created (and data tables added) to practice with.

2 x Word templates (ImportSpecificationFixed,dotm and ImportSpecificationDelim.dotm) for printing out the content of Fixed of delimited specs. The code as written in the module USys_BasIMEXDoc expects these to be in the same directory at the database.

3 x modules - basBits.acm, basConfiguration.acm, USys_basIMEX.acm and 2 x forms USys_fsubIMEXColumns.acf, USys_frmIMEXSpecification.acf which are for import to a database using the LoadFromText process, if you prefer this to importing them from the Specs.accdb. t also means you can inspect the constituents of the utility with a text editor offline before you use them in a database.

You are welcome to do what you wish with all or any of the content.