Importing mulitiple .txt files into Access (1 Viewer)

[adammitchell23]

Hi Folks,

I'm attempting to import multiple tab-delimited .txt files into a table in my database. The files are then deleted (manually) and the folder gradually repopulates over time (one a day) with new files produced automatically by a weather monitoring station. I'm using the following to do this:

Function ImportWeather()

Dim fileName As String

fileName = Dir("Z:\Monitoring\AWS_Import" & "\*.txt")
While fileName <> ""
    DoCmd.TransferText acImportDelim, "ImportWeather", "tblWeatherImportTemp", fileName, True

    fileName = Dir()
Wend
End Function

Where the directory of the .txt file is "Z:\Monitoring\AWS_Import" and the table to which they're being appended is "tblWeatherImportTemp".

This works when I set up an import specification ("ImportWeather") and then run the function but if I attempt to then run the code based on that specification at a later date it won't work. I receive the error "Could not locate file "xxxxx.txt"".

This makes sense if I deleted the file with which I'm creating the import specification, but the error even occurs if I use a blank file as an 'anchor' for the specification to latch on to each time. If I go back and remake the specification using exactly the same criteria, it works again, but again, works only that one time.

Hopefully that makes sense...

Can someone help me out?

Cheers,

Ad

 

[MarkK]

Hello and welcome to the forum!

I think there are two ways to solve this, the issue being that the filename to import is a fixed value in the import spec's XML. You either need to...

1. ...change the filename on disk to conform to the name expected by the import/export spec. To take this approach, manually rename one of your text files to something like "toImport.txt" and create the import spec. Now, all you have to do is rename files you want to import to "toImport.txt" and run the spec. This is simpler.
2. ...create the import spec on the fly, supplying the filename at runtime. To take this approach, create the spec normally, but then go find it back and save its XML somewhere, and in that XML, find the place where the import filename is specified. Replace the filename in the spec XML with a place holder and save it like that. Then when you need to run that spec, retrieve your XML, replace the filename placeholder with the filename to import, and then programmatically create and run the new spec. This is more complicated, but cooler.

hth
Mark

 

[The_Doc_Man]

Mark beat me to solution #1, which is what I recommend.

If your input name for the specification has to be xyz.txt then a sequence such as ...

Kill xyz.txt
Name abc.txt As xyz.txt
{import}
Kill def.txt
Name def.txt As xyz.txt
{import}
{lather, rinse, repeat}

Seems pretty simple. And could be adapted for a loop over files identified by the FileSystem Object. Then you can import the files one at a time using the extant import spec and not have to diddle with XML strings.

 

[Pat Hartman]

When you use TransferText, the file name is NOT embedded in the spec. It is an argument in the statement. It is the "new" import that MS made because everyone had trouble with importspecs that has that problem.

The problem is probably that you can't use a wild card in the TransferText action. You are going to have to figure out what the file name actually is if it changes every day and then use that specific name in the TransferText. I'm not sure how to do this with the plain Access commands so I would use FSO (File System Object).

 

[MarkK]

Looking at the code, the problem there is probably that Dir() returns ONLY the filename. You need to prefix it with the path...

Sub ImportWeather()
    Const PATH as string = "Z:\Monitoring\AWS_Import\"
    Dim fileName As String

    fileName = Dir(PATH & "*.txt")
    Do While fileName <> ""
        DoCmd.TransferText acImportDelim, "ImportWeather", "tblWeatherImport", [COLOR="Blue"]PATH & fileName[/COLOR], True
        fileName = Dir()
    Loop
End Sub

 

[arnelgp]

here is the code that will alter your Import/Export spec.

on your Function:

Function ImportWeather()
Dim strPath As String
Dim fileName as string
Dim arrFiles() As String
Dim i As Long
Redim arrFiles(0)
strPath = "Z:\Monitoring\AWS_Import\"
fileName = Dir(strPath & "*.txt")
While fileName <> ""
	arrFiles(Ubound(arrFiles))=strPath & fileName
	' change the specification
	Call fnChangeImportExportSpec("ImportWeather", strPath & fileName)
	DoCmd.TransferText acImportDelim, "ImportWeather", "tblWeatherImportTemp", strPath & fileName, True

	fileName = Dir()
	Redim Preserve arrFiles(Ubound(arrFiles) + 1)
Wend
' optional delete the textfile already imported
' uncomment below if the test is successfull
'For i = 0 To Ubound(arrFiles)-1
'	Kill arrfiles(i)
'Next
End function

Copy and Paste it in Standard Module.

Public Function fnChangeImportExportSpec(ByVal specFile As String, ByVal NewTextFile As String)
'
' arnelgp
' 29-November-2017
'
' purpose is to alter import/export specification
' updating the Path portion to point
' to new path.
'
' specFile is the import/export specification name
'
' NewTextFile the complete path + filename + ext of the file
'       to be inserted to the specification
'
'
    Dim mySpec As ImportExportSpecification
    Dim lngPos As Long, lngPos2 As Long
    Dim strXML As String
    Dim strToBeReplaced As String
    For Each mySpec In CurrentProject.ImportExportSpecifications
        If mySpec.Name = specFile Then
            ' we get our import export specification
            ' find the "Path" portion and change it to NewTextFile
            strXML = mySpec.xml
            lngPos = InStr(strXML, "PATH")
            If lngPos > 0 Then
                ' we find it then find the "=" sign, coz
                ' this is the starting point of our
                ' replacement
                lngPos = InStr(lngPos, strXML, "=")
                If lngPos > 0 Then
                    ' we are luck again?#
                    ' move one place so we retain the "="
                    lngPos = lngPos + 1
                    ' now find "xmlns" coz' this is the
                    ' ending of our replacement
                    lngPos2 = InStr(lngPos, strXML, "xmlns")
                    If lngPos2 > 0 Then
                        ' do the replacement here
                        strToBeReplaced = Mid(strXML, lngPos, lngPos2 - lngPos - 1)
                        strXML = Replace(strXML, strToBeReplaced, """" & NewTextFile & """")
                    End If
                End If
            End If
           ' replace with new speci
            mySpec.xml = strXML
            Set mySpec = Nothing
            Exit For

        End If
    Next
End Function

 

[MarkK]

Arnel, if the spec is accurate, why run TransferText? Why not just run the Execute method of the spec?

 

[arnelgp]

More facts:

Import/Export Specification is an Object.
it is analogous to Querydef.
in Querydef we the .SQL property.
in ImportExportSpecification you have .xml
you can Run Querydef, also you can with ImportSpecs:

Dim thisSpec As ImportExportSpecification
Set thisSpec=CurrentProject.ImportExportSpecifications("ImportWeather")
thisSpec.Execute

on your function you can change it to:

Function ImportWeather()
Dim strPath As String
Dim fileName as string
Dim arrFiles() As String
Dim i As Long
Dim thisSpec as ImportExportSpecification
Redim arrFiles(0)
strPath = "Z:\Monitoring\AWS_Import\"
fileName = Dir(strPath & "*.txt")
While fileName <> ""
	arrFiles(Ubound(arrFiles))=strPath & fileName
	' change the specification
	Call fnChangeImportExportSpec("ImportWeather", strPath & fileName)
	Set thisSpec =CurrentProject.ImportExportSpecifications("ImportWeather")
	thisSpec.Execute
	Set thisSpec=Nothing
	fileName = Dir()
	Redim Preserve arrFiles(Ubound(arrFiles) + 1)
Wend
' optional delete the textfile already imported
' uncomment below if the test is successfull
'For i = 0 To Ubound(arrFiles)-1
'	Kill arrfiles(i)
'Next
End function

 

[Pat Hartman]

If Adam is using TransferText, he is not using the new import/export object.

Please feel free to jump in any time Adam to clarify.

 

[adammitchell23]

Hi guys,

Thanks for all the replies and sorry for not getting back to you all earlier; I live on a tiny island and the internet is extremely unreliable!

I've not gone through the thread properly yet, but I'll do so now and let you all know how I fare in the next few days!

Cheers!

Adam

 

[adammitchell23]

Hi all,

So I've now tried all the methods and although I've had success importing with all of them, I'm still running across the same problem; I can import a number of files at once from my import folder (say, 10 or 20 files) using the methods, then I can replace these files with new ones to be imported and run the code again importing another 10 or 20 files and I can continue to do this however many times. However, if I close the database and open it again, I am presented with that same "Cannot find the file xxxx.txt" where xxxx.txt is the filename of one of the files I'm trying to import (interestingly, the error seems to name one of the files in the folder at random - it might be the first, or the third, or the fifth) but will not import any of them. BUT... if I open up the code editor, delete the code and then replace it with the exact same code again, or if I open the import dialog and then close it again without changing anything it works until such a time as I close the database and reopen it.
I'm VERY confused...

Adam

 

[NauticalGent]

Give this video a look-see and determine if this may be what you are trying to do. He has a YouTube channel that I have learned MUCH from.

https://youtu.be/SYfdkdkkFRE

Can’t say much for his choice in music though...

 

[adammitchell23]

Ah! Thanks for the link; a slightly different approach from what I'd envisioned, but it's a winner!

Thanks NauticalGent, and thanks everyone for your help!

Ad

 

[NauticalGent]

Easy day...and the music?

 

[adammitchell23]

Cracking bit of German electropop!

 

[NauticalGent]

I saw your question to Phillip. I don’t want to answer a question on his channel, so I will do it here. If your going to set multiselect to yes, then you will need a For Each statement to handle the selected items.

This link gives a few examples:

http://analystcave.com/vba-application-filedialog-select-file/

 

[adammitchell23]

OK, so I now have the following, based mostly on Phillip's code;

Private Sub cmdSelectFile_Click()


Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogOpen)

With fd

.AllowMultiSelect = True



If .Show Then
For i = 1 To fd.SelectedItems.Count
        Me.txtFileName.Value = Me.txtFileName.Value & ";" & .SelectedItems.Item(i)
    Next

End If

End With

End Sub



Public Function ImportWeather()
    Dim txtfile As Variant
    Dim varFileNames As Variant
    varFileNames = Split(Me.txtFileName.Value, ";")
    DoCmd.OpenForm "frmImport2", , , , , acDialog
    For Each txtfile In varFileNames
        DoCmd.TransferText acImportDelim, "ImportWeather", "tblWeatherImportTemp", txtfile, True
        DoEvents
        
    Next
    DoCmd.Close acForm, "frmImport2"
End Function

My problem is that now transfertext won't recognise txtfile as the file name, although I'd thought that was defined earlier...

 

[MishaInc]

Hi,

I am working with MS Access 2003 and I try to import csv with delimited with Pipo "|" to MS Access 2003 Table, but I can't

I use this and display error tablet doesn't have F1 field:
DoCmd.TransferText acImportDelim, specificationname, tablename:="Absences", FileName:="K:\DB\test.csv", HasFieldNames:=NO

Can you give me to this issue?

Thank you

 

[NauticalGent]

Without any knowledge of the file you are trying to import, the first place I would look at is the Specification settings. Can you post a screen shot of it?

 

[MishaInc]

I attached error.

Thank you