Import/Export Data Tasks (1 Viewer)

isladogs

CID VIP
Local time
Today, 19:22
Joined
Jan 14, 2017
Messages
14,030
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
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:22
Joined
Sep 21, 2011
Messages
7,170
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.
 

isladogs

CID VIP
Local time
Today, 19:22
Joined
Jan 14, 2017
Messages
14,030
Hi Paul
Yes. The changes to import/export data tasks were introduced in A2010
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:22
Joined
Oct 29, 2018
Messages
13,251
Hi Colin. Maybe I am remembering this incorrectly, but I think those specs are stored as XML in a collection somewhere.

Sent from phone...
 

isladogs

CID VIP
Local time
Today, 19:22
Joined
Jan 14, 2017
Messages
14,030
That was my first thought as well but they definitely aren't stored in the MSysAccessXML system table
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:22
Joined
Oct 29, 2018
Messages
13,251
I'll try to dig into it when I get home in case I find anything at all.
 

isladogs

CID VIP
Local time
Today, 19:22
Joined
Jan 14, 2017
Messages
14,030
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
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:22
Joined
Jan 23, 2006
Messages
13,402
Colin,

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

Attachments

  • ImportSpecLocation.jpg
    ImportSpecLocation.jpg
    105.5 KB · Views: 35

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:22
Joined
Feb 19, 2002
Messages
30,136
However the newer method involves Data Tasks which are generally both easier to use and more powerful.
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:22
Joined
Oct 29, 2018
Messages
13,251
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
Hi Colin. I think I found what I was thinking of. Check out:


CurrentProject.ImportExportSpecifications


Hope that's it...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:22
Joined
Oct 29, 2018
Messages
13,251
And the XML I was talking about in the other thread is in here:


CurrentProejct.ImportExportSpecifications(0).XML
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 14:22
Joined
Jan 23, 2006
Messages
13,402
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??
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:22
Joined
Oct 29, 2018
Messages
13,251
Right. I think this is the new place where it's stored. Either that or the two places store different things now.
 

isladogs

CID VIP
Local time
Today, 19:22
Joined
Jan 14, 2017
Messages
14,030
Thanks both. Will look at that later today.
MSysIMEXSpecs was used prior to 2010. It still exists but only for backwards compatibility.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:22
Joined
Oct 29, 2018
Messages
13,251
Hi Colin. Great job! Thanks!
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:22
Joined
Sep 21, 2011
Messages
7,170
Last edited by a moderator:

isladogs

CID VIP
Local time
Today, 19:22
Joined
Jan 14, 2017
Messages
14,030
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
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:22
Joined
Sep 21, 2011
Messages
7,170
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.
 

isladogs

CID VIP
Local time
Today, 19:22
Joined
Jan 14, 2017
Messages
14,030
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
 

Users who are viewing this thread

Top Bottom