isladogs
MVP / VIP
- Local time
- Today, 04:42
- Joined
- Jan 14, 2017
- Messages
- 18,549
Prior to Access 2007, import/import specifications were saved in 2 system tables : MSysIMEXSpecs / MSysIMEXColumns
In Access 2007, this system was largely replaced (but see note below) with a wizard driven & arguably more powerful XML based system of Import/Export Data Tasks.
However, ease of use has also led to greater obscurity in how it works.
Unfortunately, the contents of a saved Data Task cannot easily be viewed or edited. It is often easier to delete a task and start again if it doesn't run.
This is an example of the XML for an Import task:
As a recent thread https://www.access-programmers.co.uk/forums/showthread.php?t=307860 developed, the need for code to do both of these things became evident.
I've now created code to do exactly that.
Many thanks to jdraw & the DBGuy for prompting me with starter code and to smig for suggesting a procedure that I adapted.
The Access help file includes the following info:
The attached database includes a module modIMEX containing code to do the following:
• ViewImportExportSpecifications – saves all data tasks specifications to a user defined system table (normally hidden) called USysIMEXSpecs
• ExecuteDataTask / RunDataTask – two different ways of running a task using VBA
• EditDataTask – replace a specified section of the XML with a different text string
• ModifyDataTaskPath – replace the existing path of the external file with your own path. You do NOT need to know the existing path to use this
• ModifyDataTaskDestination – replace the existing destination table for an import task with your own table name. Once again the existing value isn’t needed
• RenameDataTask – ‘does what it says on the tin’
The zip file contains database has 2 import & 2 export data tasks for use with a sample text file test.txt. Store this in the same folder as the sample database or any folder of your choice
However, the 4 tasks will not run unless your path is the same as mine – VERY UNLIKELY!
Using the above procedures you should be able to edit the XML to make these work for you
To use this code in your own applications import the module modIMEX and (optionally) the system table USysIMEXSpecs
Hope this helps some of you
NOTE (updated 15 Nov 2019):
It is still possible to create the 'traditional' IMEX specification on TEXT files by clicking the Advanced button on the IMEX wizard then clicking Save As on the next window.
However the Advanced button doesn't appear when working with Excel files or Access tables so the only way to save the specification is by saving as a data task
In Access 2007, this system was largely replaced (but see note below) with a wizard driven & arguably more powerful XML based system of Import/Export Data Tasks.
However, ease of use has also led to greater obscurity in how it works.
Unfortunately, the contents of a saved Data Task cannot easily be viewed or edited. It is often easier to delete a task and start again if it doesn't run.
This is an example of the XML for an Import task:
Code:
<?xml version="1.0" encoding="utf-8" ?>
<ImportExportSpecification Path = "G:\MyFiles\ExampleDatabases\IMEXSpecifications\Test.txt" xmlns="urn:www.microsoft.com/office/access/imexspec">
<ImportText TextFormat="Delimited" FirstRowHasNames="false" FieldDelimiter="," TextDelimiter="" CodePage="850" Destination="Test" >
<DateFormat DateOrder="DMY" DateDelimiter="/" TimeDelimiter=":" FourYearDates="true" DatesLeadingZeros="false" />
<NumberFormat DecimalSymbol="." />
<Columns PrimaryKey="{Auto}">
<Column Name="Col1" FieldName="Field1" Indexed="NO" SkipColumn="false" DataType="Text" Width="84" />
</Columns>
</ImportText>
</ImportExportSpecification>As a recent thread https://www.access-programmers.co.uk/forums/showthread.php?t=307860 developed, the need for code to do both of these things became evident.
I've now created code to do exactly that.
Many thanks to jdraw & the DBGuy for prompting me with starter code and to smig for suggesting a procedure that I adapted.
The Access help file includes the following info:
The attached database includes a module modIMEX containing code to do the following:
• ViewImportExportSpecifications – saves all data tasks specifications to a user defined system table (normally hidden) called USysIMEXSpecs
• ExecuteDataTask / RunDataTask – two different ways of running a task using VBA
• EditDataTask – replace a specified section of the XML with a different text string
• ModifyDataTaskPath – replace the existing path of the external file with your own path. You do NOT need to know the existing path to use this
• ModifyDataTaskDestination – replace the existing destination table for an import task with your own table name. Once again the existing value isn’t needed
• RenameDataTask – ‘does what it says on the tin’
The zip file contains database has 2 import & 2 export data tasks for use with a sample text file test.txt. Store this in the same folder as the sample database or any folder of your choice
However, the 4 tasks will not run unless your path is the same as mine – VERY UNLIKELY!
Using the above procedures you should be able to edit the XML to make these work for you
To use this code in your own applications import the module modIMEX and (optionally) the system table USysIMEXSpecs
Hope this helps some of you
NOTE (updated 15 Nov 2019):
It is still possible to create the 'traditional' IMEX specification on TEXT files by clicking the Advanced button on the IMEX wizard then clicking Save As on the next window.
However the Advanced button doesn't appear when working with Excel files or Access tables so the only way to save the specification is by saving as a data task
Attachments
Last edited:
