Import Tab Delimited Text File Using an Import Specification

richlocus
2StarLounger
Posts: 149
Joined: 03 Oct 2015, 00:30

Import Tab Delimited Text File Using an Import Specification

Post by richlocus »

Hello:
I am updating another developer's Access 2016 application which imports Excel tab delimited files using a button which references VBA code that automates the import with the assistance of a stored Import Specification.

I tried locating the import specification (CustomerImportSpec) by going to "External Data", "Saved Imports" (and I even looked in Saved Exports") but there were no import specifications. Do the specs not exist or am I looking in the wrong place?

Here is a snippet of the code which uses that import specification:

If cdlg.GetStatus = True Then
DoCmd.TransferText acImportDelim, "CustomerImportSpec", "tblAccountSalesRepAssignments", _
cdlg.GetName, True
Else
MsgBox "No file selected."
Exit Sub
End If

Thanks,
Rich Locus

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import Tab Delimited Text File Using an Import Specification

Post by HansV »

Saved Imports and Saved Exports are not the same as Import/Export Specifications.
When you import a text file or export to a text file, click Advanced...
You can create, load and save a specification there.

S1776.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

richlocus
2StarLounger
Posts: 149
Joined: 03 Oct 2015, 00:30

Re: Import Tab Delimited Text File Using an Import Specification

Post by richlocus »

Hello:
Yesterday Hans answered my question perfectly on how to display or update an existing Import Specification for Text files.
It worked. And then, this morning I could not reproduce how to do it!! I apologize for the brain malfunction (I don't have many of those).
I have been unable to reproduce my ability to see my stored specifications.

I actually have 6 saved specifications (invisible from the normal Excel objects)
Could I have a step by step example?

This is the brick wall I ran into this morning.

I followed these steps (I could not find a direct way to go to the saved import specifications).

1) I selected External Data
2) Then Saved Imports (Nothing there, so I continued)
3) Then I selected "New Data Source" (in the Import and Link section) From a File, Text File.
4) Then I Get the "Select Source and Destination of the Data" Screen
5) Unlike the example Hans provided and that worked for me yesterday, there was no "Advanced" button which previously allowed me to view my hidden specification.

I'm at my wits end. Could I get a step by step process so I can view and access my hidden import specifications? See the code below. For example,I was able yesterday to see the hidden "CustomerImportSpec".
CustomerImportSpecification.jpg
This code example shows the VBA used to reload table tblAccountSalesRepAssignments from a tab-delimited external file provided by another system using the import specification shown in the example.

If cdlg.GetStatus = True Then
DoCmd.TransferText acImportDelim, "CustomerImportSpec", "tblAccountSalesRepAssignments", _
cdlg.GetName, True
Else
MsgBox "No file selected."
Exit Sub
End If

Thanks for any assistance.
Regards,
Rich Locus
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import Tab Delimited Text File Using an Import Specification

Post by HansV »

On the "Select Source and Destination of the Data" Screen, click Browse... and select the text file.
Then select "Import the source data" and click OK.
Best wishes,
Hans

Gasman
StarLounger
Posts: 81
Joined: 22 Feb 2022, 09:04

Re: Import Tab Delimited Text File Using an Import Specification

Post by Gasman »

Here is an app that allows you to see and edit various specifications.

https://isladogs.co.uk/view-edit-imex-d ... cification
Using Access 2007.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: Import Tab Delimited Text File Using an Import Specification

Post by SpeakEasy »

Elect to show system objects in the navigation tree, then look for

MSysIMEXSpecs

and

MSysIMEXColumns

it is in these tables that contain the import specs

richlocus
2StarLounger
Posts: 149
Joined: 03 Oct 2015, 00:30

Re: Import Tab Delimited Text File Using an Import Specification

Post by richlocus »

Thanks to all of you for your valuable input.
Rich Locus