Editing an external text file is it possible? (1 Viewer)

smig

Registered User.
Local time
Today, 21:50
Joined
Nov 25, 2009
Messages
2,209
Do I assume you didn't try what I suggested after all?

That took me less than 2 minutes to download, import using the wizard with no reported errors, save the import specification and create a sub for future use.

I'll leave you to check if the lines listed are correct but as there were no import errors I see no reason why not
Sorry
Not yet.
 

isladogs

MVP / VIP
Local time
Today, 19:50
Joined
Jan 14, 2017
Messages
18,186
OK so look at the attached file in my last reply
 

smig

Registered User.
Local time
Today, 21:50
Joined
Nov 25, 2009
Messages
2,209
Do I assume you didn't try what I suggested after all?

That took me less than 2 minutes to download, import using the wizard with no reported errors, save the import specification and create a sub for future use.

I'll leave you to check if the lines listed are correct but as there were no import errors I see no reason why not
I tried using the Wizard and create a spec.
Seems to work fine.

Is there any way to tell it where the file to import exist?
or will it open a file open dialog if it won't find the file?
 

isladogs

MVP / VIP
Local time
Today, 19:50
Joined
Jan 14, 2017
Messages
18,186
Unfortunately you can't edit an existing data task AFAIK.
When you create your own task, you provide the location of the file to be imported.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:50
Joined
Oct 29, 2018
Messages
21,358
Unfortunately you can't edit an existing data task AFAIK.
When you create your own task, you provide the location of the file to be imported.

I thought I saw someone posted a function for modifying a saved import task. I just can't remember who.
 

smig

Registered User.
Local time
Today, 21:50
Joined
Nov 25, 2009
Messages
2,209
I did some search and found the specs are stored in the MsysIMEXspecs, MsysIMEXcolumns system tables.
They can't be edited directly (adding/deleting a record is possible) but they can be edited using code.
Unfortunately I can't find the import file location in there :(

I also found some code here https://stackoverflow.com/questions/143420/how-can-i-modify-a-saved-microsoft-access-2007-or-2010-import-specification
Code:
Below are three functions you can use to alter and use the MS Access 2010 Import Specification. The third sub changes the name of an existing import specification. The second sub allows you to change any xml text in the import spec. This is useful if you need to change column names, data types, add columns, change the import file location, etc.. In essence anything you want modify for an existing spec. The first Sub is a routine that allows you to call an existing import spec, modify it for a specific file you are attempting to import, importing that file, and then deleting the modified spec, keeping the import spec "template" unaltered and intact. Enjoy.

Public Sub MyExcelTransfer(myTempTable As String, myPath As String)
On Error GoTo ERR_Handler:
    Dim mySpec As ImportExportSpecification
    Dim myNewSpec As ImportExportSpecification
    Dim x As Integer

    For x = 0 To CurrentProject.ImportExportSpecifications.Count - 1
    If CurrentProject.ImportExportSpecifications.Item(x).Name = "TemporaryImport" Then
        CurrentProject.ImportExportSpecifications.Item("TemporaryImport").Delete
        x = CurrentProject.ImportExportSpecifications.Count
    End If
    Next x
    Set mySpec = CurrentProject.ImportExportSpecifications.Item(myTempTable)
    CurrentProject.ImportExportSpecifications.Add "TemporaryImport", mySpec.XML
    Set myNewSpec = CurrentProject.ImportExportSpecifications.Item("TemporaryImport")

    myNewSpec.XML = Replace(myNewSpec.XML, "\\MyComputer\ChangeThis", myPath)
    myNewSpec.Execute
    myNewSpec.Delete
    Set mySpec = Nothing
    Set myNewSpec = Nothing
    exit_ErrHandler:
    For x = 0 To CurrentProject.ImportExportSpecifications.Count - 1
    If CurrentProject.ImportExportSpecifications.Item(x).Name = "TemporaryImport" Then
        CurrentProject.ImportExportSpecifications.Item("TemporaryImport").Delete
        x = CurrentProject.ImportExportSpecifications.Count
    End If
    Next x
Exit Sub    
ERR_Handler:
    MsgBox Err.Description
    Resume exit_ErrHandler
End Sub

Public Sub fixImportSpecs(myTable As String, strFind As String, strRepl As String)
    Dim mySpec As ImportExportSpecification    
    Set mySpec = CurrentProject.ImportExportSpecifications.Item(myTable)    
    mySpec.XML = Replace(mySpec.XML, strFind, strRepl)
    Set mySpec = Nothing
End Sub


Public Sub MyExcelChangeName(OldName As String, NewName As String)
    Dim mySpec As ImportExportSpecification
    Dim myNewSpec As ImportExportSpecification
    Set mySpec = CurrentProject.ImportExportSpecifications.Item(OldName)    
    CurrentProject.ImportExportSpecifications.Add NewName, mySpec.XML
    mySpec.Delete
    Set mySpec = Nothing
    Set myNewSpec = Nothing
End Sub
 

isladogs

MVP / VIP
Local time
Today, 19:50
Joined
Jan 14, 2017
Messages
18,186
The system tables MSysIMEXSpecs and MSysIMEXColumns were used for Access versions prior to 2010.
They still exist but for backwards compatibility only.

The new data tasks are stored as XML. See this thread that I started yesterday. https://www.access-programmers.co.uk/forums/showthread.php?p=1650466&posted=1#post1650466

Anyway, more to the point, do you now have the solution for your text file import?

In answer to an earlier question, if the location of the file specified for import doesn't exist, an error message occurs but no file open dialog. Easiest to just create a new task
 
Last edited:

smig

Registered User.
Local time
Today, 21:50
Joined
Nov 25, 2009
Messages
2,209
The system tables MSysIMEXSpecs and MSysIMEXColumns were used for Access versions prior to 2010.
They still exist but for backwards compatibility only.

The new data tasks are stored as XML. See this thread that I started yesterday. https://www.access-programmers.co.uk/forums/showthread.php?p=1650466&posted=1#post1650466

Anyway, more to the point, do you now have the solution for your text file import?

In answer to an earlier question, if the location of the file specified for import doesn't exist, an error message occurs but no file open dialog. Easiest to just create a new task

Thank you for the information
Your code is similar to the code I posted.
I guess I have a full solution for what I need, as I can set the Spec's file path :)
 

isladogs

MVP / VIP
Local time
Today, 19:50
Joined
Jan 14, 2017
Messages
18,186
Glad you're happy now.
DBG & I were pleased to have helped

I'm in the process of creating a related item for the code repository
 

smig

Registered User.
Local time
Today, 21:50
Joined
Nov 25, 2009
Messages
2,209
This code works perfect for modifying the file's path
Code:
Public Sub pbModifyImportSpecPath(strNewPath As String, strSpecName As String)

Dim strSpecXML As String
Dim x As Integer
Dim x1 As Integer
Dim x2 As Integer


strSpecXML = CurrentProject.ImportExportSpecifications.Item(strSpecName).XML
Debug.Print CurrentProject.ImportExportSpecifications.Item(strSpecName).XML

x = InStr(LCase(strSpecXML), LCase("Path="))
x1 = InStr(x, LCase(strSpecXML), Chr(34))
x2 = InStr(x1 + 1, LCase(strSpecXML), Chr(34))

strSpecXML = Left(strSpecXML, x1) & strNewPath & Right(strSpecXML, Len(strSpecXML) - x2 + 1)

CurrentProject.ImportExportSpecifications.Item(strSpecName).XML = strSpecXML
Debug.Print CurrentProject.ImportExportSpecifications.Item(strSpecName).XML

End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:50
Joined
Oct 29, 2018
Messages
21,358

Users who are viewing this thread

Top Bottom