Import/Export Data Tasks

isladogs

isladogs

MVP / VIP
Local time
Today, 04:39
Joined
Jan 14, 2017
Messages
18,549
A current thread by smig https://www.access-programmers.co.uk/forums/showthread.php?t=307860 reminded me of the differences in the old and new methods of importing/exporting files.

In the past, import/export specifications were stored to two system files MSysIMEXSpecs and MSysIMEXColumns. However the newer method involves Data Tasks which are generally both easier to use and more powerful.
However I'm not aware of any method of editing an existing data task NOR can I discover where the task is stored.
System table? If so, which?
Registry? If so, where?
Access file property? If so which?

Can anyone assist with answers
 
Would that be for 2010 onwards Colin?

There are some functions here that are meant to amend and they also mention your Specs and Columns files for earlier versions.
 
Hi Paul
Yes. The changes to import/export data tasks were introduced in A2010
 
Hi Colin. Maybe I am remembering this incorrectly, but I think those specs are stored as XML in a collection somewhere.

Sent from phone...
 
That was my first thought as well but they definitely aren't stored in the MSysAccessXML system table
 
I'll try to dig into it when I get home in case I find anything at all.
 
TIA. My online searches were unsuccessful. I may try searching using WIA.
It has to be within the application rather than the registry as the data tasks still work if you move the app to another workstation
 
Colin,

I checked a database (O365 version 16) and my import specs are as below
 

Attachments

  • ImportSpecLocation.jpg
    ImportSpecLocation.jpg
    105.5 KB · Views: 223
However the newer method involves Data Tasks which are generally both easier to use and more powerful.
Click to expand...
I'm going to have to disagree with that assessment. They are one of those "improvements" are are designed to shelter people from details. If you can't get to the details, you can't change anything including the source file name or location. You also can't use the same spec for multiple imports as you can with the earlier method. I have a process that both imports and exports the same file type and I use the same spec whether I am importing or exporting. The new, "improved" method can't do that. At my last client, I had to convert from the new to the old at least a dozen times for different people. They frequently made little databases that they shared among themselves. However, what would run on one person's PC wouldn't run on another's since the file paths were never the same. So I had to change all the import/export actions to use TransferText or TransferSpreadsheet so the source/target could be controlled by the user.
 
isladogs said:
TIA. My online searches were unsuccessful. I may try searching using WIA.
It has to be within the application rather than the registry as the data tasks still work if you move the app to another workstation
Click to expand...
Hi Colin. I think I found what I was thinking of. Check out:


CurrentProject.ImportExportSpecifications


Hope that's it...
 
And the XML I was talking about in the other thread is in here:


CurrentProejct.ImportExportSpecifications(0).XML
 
Last edited:
Thanks dbGuy I found 3 other (different) import specs
with
?CurrentProject.ImportExportSpecifications.Count
3

?CurrentProject.ImportExportSpecifications(0).XML
Code: 
<?xml version="1.0" encoding="utf-8" ?>
<ImportExportSpecification Path = "C:\Users\Jack\Documents\AllEventsSep24A.txt" xmlns="urn:www.microsoft.com/office/access/imexspec">
      <ImportText TextFormat="Delimited" FirstRowHasNames="true" FieldDelimiter="{Tab}" TextDelimiter="" CodePage="65001" Destination="AllEventsSep24A" >
               <DateFormat DateOrder="DMY" DateDelimiter="-" TimeDelimiter=":" FourYearDates="true" DatesLeadingZeros="false" />
           <NumberFormat DecimalSymbol="." />
             <Columns PrimaryKey="{Auto}">
                        <Column Name="Col1" FieldName="Level" Indexed="NO" SkipColumn="false" DataType="Text" Width="12" />
                        <Column Name="Col2" FieldName="Date and Time" Indexed="NO" SkipColumn="false" DataType="Text" Width="22" />
                        <Column Name="Col3" FieldName="Source" Indexed="NO" SkipColumn="false" DataType="Text" Width="43" />
                        <Column Name="Col4" FieldName="Event ID" Indexed="YESDUPLICATES" SkipColumn="false" DataType="Long" Width="9" />
                        <Column Name="Col5" FieldName="Task Category" Indexed="NO" SkipColumn="false" DataType="Text" Width="14" />
                        <Column Name="Col6" FieldName="Field6" Indexed="NO" SkipColumn="false" DataType="Text" Width="130" />
             </Columns>
   </ImportText>
</ImportExportSpecification>

and 2 others, but not the one in MsysIMEXSpecs??
 
Right. I think this is the new place where it's stored. Either that or the two places store different things now.
 
Thanks both. Will look at that later today.
MSysIMEXSpecs was used prior to 2010. It still exists but only for backwards compatibility.
 
Hi Colin. Great job! Thanks!
 
Re: View & Edit IMEX Data Task Specifications

Very easily if you have MZ-Tools which is how I added them ;)
Otherwise you can do so manually. Not sure of any VBA code to do so
Line numbering is helpful in bug fixing during development.
However why does it matter if line numbering exists?

BTW as I know you run A2007, I believe that the old system table method applies rather than the newer data tasks. Can you correct me if I'm wrong about that
 
Re: View & Edit IMEX Data Task Specifications

Hi Colin,
Not to worry, I found this site http://remove-line-numbers.ruurtjan.com/ which did the job admirably.

Yes, I have those tables and can recognise some linkages between them.
However that code to expose the XML also works for me.
 
Re: View & Edit IMEX Data Task Specifications

Glad you have a solution for line numbers though still not clear why it matters.
However just to clarify, in A2007, am I correct that the old system applies where import/export specs are saved to the two MSysIMEX tables
 
You must log in or register to reply here.

Similar threads

isladogs
Access Europe meeting on Wed 5 June - Extended File Properties / Manage Import/Export Data Tasks
Replies
2
Views
375
isladogs
isladogs
D
Error 31602 when using IMEX Data Tasks with isladogs frmManagingTasks Form
2
Replies
27
Views
1,222
isladogs
isladogs
S
The text file specification <name> does not exist. You cannot import, export, or link using the specification. (Error 3625)
Replies
12
Views
277
The_Doc_Man
The_Doc_Man
A
Trouble Exporting Sorted Data from Access Form with Subforms to Excel
Replies
2
Views
380
ebs17
E
R
Solved Importing and calculating the data
Replies
12
Views
869
Robrobby
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom