Error 31602 when using IMEX Data Tasks with isladogs frmManagingTasks Form (1 Viewer)

[Danick]

I've been using isladogs IMEX form found here to import my data into my database quickly.

View & Edit IMEX Data Task Specifications

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

www.access-programmers.co.uk

All was working till I created another import spec and tried running it or changing the file path using the form. Now I'm getting this popup

Error 31602 in line 40 of cmdExecute procedure. The specification with the specified index does not exist. Specify a different index 'import-Components <lot of spaces> '.

For some reason there seems to be a lot of spaces in the name of the shown in the error popup, but there are no spaces when viewed in the "External Data" - "Saved Imports" - "Manage Data Task" form.

It's still working fine for a different data table I'm using, so not sure what the problem could be.
Any ideas?
Thanks

 

[isladogs]

Sounds like you are running an old version of my app. That was a bug that I fixed in v2.2. which you can download from post #5 in the thread you linked

View & Edit IMEX Data Task Specifications

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

www.access-programmers.co.uk

Or download the latest version 2.3 from my website which should also be OK

View & Edit IMEX Data Tasks

Use to view & modify saved import/export XML data tasks

www.isladogs.co.uk

 

[Danick]

Thanks for responding so quickly. I was running v2.2. Just downloaded v2.3 but unfortunately I'm still getting this error.
I'm downloading 2 spreadsheets from SAP so they are always in the same format. When I use the IMEX to import on the first file, it works fine. But when I use IMEX on the second spreadsheet to import (or even just run) the import spec, I always get the error. I though it might have been because I was trying to download two spreadsheets, but I created a blank database and found that it always fails on that same spreadsheet. Yet that spreadsheet imports fine if I use the Access menu dialog (External Data - Saved Imports).

 

[ebs17]

The solution from @isladogs with its possibilities is all honorable, but most of the time you don't need it at all.

DoCmd.RunSavedImportExport SavedImportExportName

The SavedImportExport is fixed (without additional processing).
- Name and path of the Excel file to be imported
- the information about the import specification, which controls how the Excel table is to be read
- the destination table

downloading 2 spreadsheets from SAP so they are always in the same format

It can therefore be assumed that the structure of the Excel tables will remain the same as will the destination tables for the import.
The only variable that will be variable is the path to the files to be imported. Since you know the path used when you create a new saved import, you only have to rename a new file to be imported to this path and the import will recognize and use this file.

Name statement (VBA)

Office VBA reference topic

learn.microsoft.com

Or what is still missing?

 

[isladogs]

Thanks for responding so quickly. I was running v2.2. Just downloaded v2.3 but unfortunately I'm still getting this error.
I'm downloading 2 spreadsheets from SAP so they are always in the same format. When I use the IMEX to import on the first file, it works fine. But when I use IMEX on the second spreadsheet to import (or even just run) the import spec, I always get the error. I though it might have been because I was trying to download two spreadsheets, but I created a blank database and found that it always fails on that same spreadsheet. Yet that spreadsheet imports fine if I use the Access menu dialog (External Data - Saved Imports).

Hi
I just tried to replicate the error using v2.3 with partial success (but a different error number
I created 6 spreadsheets with the same structure and imported the first DatatypeTest1.xlsx to a new table using the IMEX Data Tasks wizard
I deliberately made the worksheet number match the file name ...so Sheet1....Sheet6

I then created a new data task to append the spreadsheets to the existing table
Using my app, I changed the file path to DataypeTest2.xlsx and ran the task geenerating error 3709 (NOT 31602) in line 40 of cmdExceute_Click

The cause of the error was that the worksheet name was different to that in the specifications
Editing the task to change the worksheet name allowed it to run successfully ...as did changing the worksheet name in each worksheet to be the same.

I was unable to generate error 31602 no matter what I tried

I don't have access to SAP spreadsheets but suspect there was also a difference between the two files (apart from the name) that you had overlooked e.g. worksheet name or something else specified in the data task XML. Please check & let me know

If you can't identify the cause, can you email me the two spreadsheets (see link in my signature line) for me to test

Running these tests has indicated a few improvements that I can make to the app including creating more user friendly error messages than the generic Access versions

As I'm leading an Access User Group presentation on this utility in May, I have an incentive to do any improvements ... so thanks for your feedback

 

[isladogs]

The solution from @isladogs with its possibilities is all honorable, but most of the time you don't need it at all.

DoCmd.RunSavedImportExport SavedImportExportName

The SavedImportExport is fixed (without additional processing).
- Name and path of the Excel file to be imported
- the information about the import specification, which controls how the Excel table is to be read
- the destination table

It can therefore be assumed that the structure of the Excel tables will remain the same as will the destination tables for the import.
The only variable that will be variable is the path to the files to be imported. Since you know the path used when you create a new saved import, you only have to rename a new file to be imported to this path and the import will recognize and use this file.

Name statement (VBA)

Office VBA reference topic

learn.microsoft.com

Or what is still missing?

The purpose of this utility was to make the Data Tasks wizard more user friendly. It is very powerful but its actions are totally obscure to end users.
My aim was to make it as user friendly as possible despite Access keeping all the importasnt details from us!

My standard approach is indeed to use a fixed file path for repeated imports of worksheets with the same structure.
That's fine providing I know the source file path used when the data task was created.
However, this doesn't help when the path is unknown (possibly because someone else created the data task)
Similarly if the worksheet name differs (as in my previous reply) or other subtle ways that the files differ

Of course, you could create a new data task for each new import file but that completely defaeats the purpose

Also, if you just import data using text files such as CSV, you can use the old style import/export (IMEX) specifications which are saved in 2 system tables : MSysIMEXSpecs / MSysIMEXColumns. This gives you total control over the import but cannot be used with files such as Excel.
New users tend to find that process difficult initially which is probably why data tasks were introduced in Access 2007.
Unfortunately, the wizard also took away all user control over the process

 

[Danick]

The solution from @isladogs with its possibilities is all honorable, but most of the time you don't need it at all.

DoCmd.RunSavedImportExport SavedImportExportName

The SavedImportExport is fixed (without additional processing).
- Name and path of the Excel file to be imported
- the information about the import specification, which controls how the Excel table is to be read
- the destination table

It can therefore be assumed that the structure of the Excel tables will remain the same as will the destination tables for the import.
The only variable that will be variable is the path to the files to be imported. Since you know the path used when you create a new saved import, you only have to rename a new file to be imported to this path and the import will recognize and use this file.

Name statement (VBA)

Office VBA reference topic

learn.microsoft.com

Or what is still missing?

Hello ebs17,

I do use this code to import new data. It's actually just a button on a from.

DoCmd.RunSavedImportExport SavedImportExportName

The problem is that I'm providing this front end to other users. This is the reason for needing the IMEX form from isladogs. I want to make it easier for users to change the import folder/file before they can run the code. I preferred using the normal transfer method, but for some reason, data gets truncated when importing long text fields. But that doesn't happen when running this import code.

 

[Danick]

Hi
I just tried to replicate the error using v2.3 with partial success (but a different error number
I created 6 spreadsheets with the same structure and imported the first DatatypeTest1.xlsx to a new table using the IMEX Data Tasks wizard
I deliberately made the worksheet number match the file name ...so Sheet1....Sheet6

View attachment 112889

I then created a new data task to append the spreadsheets to the existing table
Using my app, I changed the file path to DataypeTest2.xlsx and ran the task geenerating error 3709 (NOT 31602) in line 40 of cmdExceute_Click

View attachment 112888

The cause of the error was that the worksheet name was different to that in the specifications
Editing the task to change the worksheet name allowed it to run successfully ...as did changing the worksheet name in each worksheet to be the same.

I was unable to generate error 31602 no matter what I tried

I don't have access to SAP spreadsheets but suspect there was also a difference between the two files (apart from the name) that you had overlooked e.g. worksheet name or something else specified in the data task XML. Please check & let me know

If you can't identify the cause, can you email me the two spreadsheets (see link in my signature line) for me to test

Running these tests has indicated a few improvements that I can make to the app including creating more user friendly error messages than the generic Access versions

As I'm leading an Access User Group presentation on this utility in May, I have an incentive to do any improvements ... so thanks for your feedback

Hello @isladogs.

I couldn't find anything that would make one spreadsheet work and the other fail. I've even tried to just recreate a blank database with only one spreadsheet but that didn't help either. So I've sent you a couple of sanitized SAP downloads to your email for testing. The one called "export.xlsx" works fine but the other one called "export1.xlsx" fails every time. Hopefully this will help improve the app.

 

[isladogs]

Hi Danick

Thanks for sending the spreadsheets. I've replied by email as well.
The problem was obvious before I even tried to import the files

The 2 files aren’t identical in structure – in fact they are totally different

1. Export.xlsx has 57 columns A -> BE
2. Export1.xlsx has 14 columns A-> N

Furthermore:

1. there are import errors on the field Component Part Nbr of the first file (Export)
2. The columns in the second file (Export1) aren’t even the same as the first 14 columns of the other file

So, of course the IMEX data task fails on the second task– you cannot use the same import task to import data from two totally differently structured spreadsheets. The issue is nothing to do with my application

You need to do the import in a different way (e.g. limiting to just those columns which match) or modify the two files. I still didn’t see error 31602 however

 

[Danick]

Hello @isladogs.

Yes the spreadsheets are totally different but that's because they are going to two different tables. And there is a common field [order] between the two that links them.

My database has two separate import DoCmd.RunSavedImportExport buttons. One for each and both work fine once they are set up using the Access menu dialog (External Data - Saved Imports). I fixed the import errors on the export.xlsx file by just changing the name of one of the column headers that has the same name during the import dialog. Then it runs fine after that. But that file isn't the one with the problem. I get the error even if I have a blank database and just try changing the path to the Export1.xlsx.

So are you saying that you don't get an error with a blank database and just importing export1.xlsx? If so, then maybe it's something on my side that causing the error.

 

[arnelgp]

can you post your sample "files" on this thread, so that others can participate on finding a workable solution for you.
there are many approaches that you can go, i am sure.

 

[isladogs]

@Danick
Clearly we were at cross purposes. I thought that you were trying to import both spreadsheets to the same table

So are you saying that you don't get an error with a blank database and just importing export1.xlsx? If so, then maybe it's something on my side that causing the error.

Correct. I’ve run the test again as two separate imports based on two data tasks. Both worked with no errors

I then emptied both tables and reimported using the Run Task action on my form. Once again no errors.
However of course, the data tasks were creating a new table each time so no additional records were created.

So I tried setting up new data tasks to append data to the existing tables.
For the first file ExportXLSX, the data task showed this error dialog

It then failed with this message and the data wasn’t imported

To fix this would require modifying the original table – I’ll leave that to you if you want to use that approach
However see the alternative suggestion below

I repeated and successfully created an Append Data Task for the second file Export12.XLSX and ran it.
So the second table has 34 records (2 identical sets of 17)

There appear to be NO issues with my utility – the problem is entirely with appending your original file using a data task

I've replied by email attaching the file with just your 3 data tasks . I haven't included it here as I'm unclear whether the data is private

My suggestion would be to use this fairly standard approach:
Import both sets of data to temp ‘staging’ tables using data tasks then transfer the data to your final tables.
This should avoid any issues and allow you to do any required manipulation on the data after import.

 

[GPGeorge]

I totally endorse the approach of using staging tables when importing data. One can create a temporary accdb for that purpose to avoid bloating the production accdb. Once the data is in an Access table, it is very straightforward to craft the appropriate append queries to format and convert the data appropriately for the production tables.

It's all about maintaining control over your data.

 

[ebs17]

I totally endorse the approach of using staging tables when importing data.

Export.xlsx has 57 columns A -> BE

The contents of 57 columns from a report can hardly be used in a table of a properly planned data model. This is where the real work - distributing the data across several tables - only begins. I call an import complete when the data is in the correct tables ready for use.
Therefore, you could simply link the external table instead of importing it. In my opinion, an import only makes sense if you temporarily index the imported table in order to achieve additional effects.

 

[GPGeorge]

The contents of 57 columns from a report can hardly be used in a table of a properly planned data model. This is where the real work - distributing the data across several tables - only begins. I call an import complete when the data is in the correct tables ready for use.
Therefore, you could simply link the external table instead of importing it. In my opinion, an import only makes sense if you temporarily index the imported table in order to achieve additional effects.

If the linked table does not properly identify the datatypes in one or more fields, it is much harder to manage the import. You have to account for that at some point.

 

[GPGeorge]

The contents of 57 columns from a report can hardly be used in a table of a properly planned data model. This is where the real work - distributing the data across several tables - only begins. I call an import complete when the data is in the correct tables ready for use.
Therefore, you could simply link the external table instead of importing it. In my opinion, an import only makes sense if you temporarily index the imported table in order to achieve additional effects.

By the way, a "Staging Table" is, by definition, an interim step in the process, so we don't disagree on that point.

 

[ebs17]

This is the only advantage of a saved import: An import specification for Excel tables is also possible here, which means that data types do not have to be interpreted but are determined by the specification.

In my practice there is only one problem with data type interpretation, namely when there are numbers in the first lines of a column and texts follow later, because by default the first eight lines are used for the interpretation. This shouldn't actually happen with a classic output from a database system (SAP).
But one thing is clear: If there are invalid column names, as noted above, you have to be prepared for further surprises.
Before complaining about the tools used, it is worth taking a look at the original tables used.

But of course: Whether you can work directly with a link or use a temporary table as an intermediate step is, on the one hand, a question of the entire process and the tasks to be solved, and, on the other hand, a question of personal style. A link is easier to clean up than a temporary table.

 

[GPGeorge]

I apologize. I wasn't trying to complain about the tools being used. Sorry if it came across that way.

A staging table is an alternative to either having to physically alter the original tables, or having to request that the exported xlsx files be provided in a different format. If you do have sufficient end-to-end control over the process to accomplish those things, by all means that's a good approach.

 

[Danick]

Just to alleviate the possibility of the problem being a computer issue, I copied the database onto my home computer for testing. My work computer uses Office 365 while my home computer has a older standalone version of Office 2016. However I'm still getting the error 31602 in line 20 when trying to change the folder path or run the task using the IMEX form. But only when trying to run the export1.xlsx file. I'll keep trying to see what could be causing the problem. But in the meantime, I've also uploaded a new export1.xlsx file here to see if anyone else is having issues in trying to change the file path using the IMEX form tool to this spreadsheet.

 

[isladogs]

Hi @Danick
Just for confirmation, did you see my post #12 as that may have got 'buried' by the subsequent exchanges.
I tried on two different computers running different versions of Access 365 and get no errors.
Much as I'd like to help, I cannot replicate your issue.

If you are unable to do so either, I strongly suggest you try the alternative approach as described in post #12 -18.
As already stated, link to the 2 Excel files using standardised names for each.
As the import isn't straightforward due to poor field names etc, import the data first to the temp (staging) tables which allows you to fix any issues using another set of queries or a VBA procedure for the final import