DoCmd.TransferTxt ..... (1 Viewer)

[daievans]

SOmrthing is awry with the code below - it cannot find the file to import from even though it appears in the locals window as a variable etc ......


Appreciate your insight and assistance folks!

Sub txtfileimport()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
' Change this next line to True if the first row in Text File
' has field names
blnHasFieldNames = True

strPath = "C:\Users\evansd1\Documents\ACT SJF files\COR\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "SJF ACT COR"
strFile = Dir(strPath & "*.*.txt")
Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      
      DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames
 
      
' Uncomment out the next code step if you want to delete the
' text file after it's been imported
'       Kill strPathFile
      strFile = Dir()
Loop
End Sub

 

[Beetle]

strFile = Dir(strPath & "*[COLOR="Red"].[/COLOR]*[COLOR="red"].[/COLOR]txt")

Unless you're in the habit of using extra periods in your file names, I doubt that's the wild card pathname search you want.

 

[CJ_London]

I agree with Beetle - try
strFile = Dir(strPath & "*.txt")

 

[daievans]

Thanks Sean,( and CJ!) however, I'd already seen thatand fixed it, but the original problem still exists.

The files I am trying to read have the format filename.COR.txt - I added the .txt as a means of getting Access to import them ...

The code to that point is finding all the variables

HAve attached the error code ....

 

[daievans]

I'm beginnng to think that I'll have to define an import spec for the DoCmd. as the file is a Tab separated variable ... unfortunately, my Import Wizard on this machine has become corrupted, and I'll have to get the Propeller Boys to re-install Office from Texas or wherever they do it from ...

I'll have a go on the system at home later ...

Thanks again lads!

Sub txtfileimport()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
' Change this next line to True if the first row in Text File
' has field names
blnHasFieldNames = True

strPath = "G:\ACT SJF files\COR\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "SJF ACT COR"
strFile = Dir(strPath & "*.txt")
Do While Len(strFile) > 0
      strPathFile = strPath & strFile
      
      DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames
 
      
' Uncomment out the next code step if you want to delete the
' text file after it's been imported
'       Kill strPathFile
      strFile = Dir()
Loop
End Sub

 

[daievans]

Are there specific Data link properties I should be setting?

 

[CJ_London]

Unless I'm missing something, Just noticed this:
strPath = "G:\ACT SJF files\COR\"

strFile = Dir(strPath & "*.txt") '=" "G:\ACT SJF files\COR\*.txt

Do While Len(strFile) > 0
strPathFile = strPath & strFile '=" "G:\ACT SJF files\COR\G:\ACT SJF files\COR\*.txt

 

[daievans]

Unless I'm missing something, Just noticed this:
strPath = "G:\ACT SJF files\COR\"

strFile = Dir(strPath & "*.txt") '=" "G:\ACT SJF files\COR\*.txt

Do While Len(strFile) > 0
strPathFile = strPath & strFile '=" "G:\ACT SJF files\COR\G:\ACT SJF files\COR\*.txt

Appreciate the help CJ!

I looked at that earlier and had a similar thought, but when I run the code and track the varaibles in the locals window, all appears to be OK ... attached the snip of that FYI ....

The code fails on the DoCmd line .....

I needa cup of tea and a chocolate biscuit Its nearly time for home - and thank you for being up late!

 

[Beetle]

@CJ

strFile = Dir(strPath & "*.txt")

would only return the name of the first .txt file found, so it would only be;

SomeFileName.txt

@daievans

The code fails on the DoCmd line .....

Maybe because you have spaces in your table name, so it needs to be enclosed in brackets;

strTable = "[SJF ACT COR]"

 

[daievans]

@CJ

strFile = Dir(strPath & "*.txt")

would only return the name of the first .txt file found, so it would only be;

SomeFileName.txt

@daievans



Maybe because you have spaces in your table name, so it needs to be enclosed in brackets;

strTable = "[SJF ACT COR]"

Thanks Beetle - tried that, and alas nothing changes. Debug tells me the file code fails because it can't locate the file, yet it has "found" it with the dir ...

I'm befuddled .... want to try it at home later ...

 

[CJ_London]

OK, two other suggestions, then I'm hitting the sack, got 9 tonnes of soil to move tomorrow

There was a similar post a few days ago, can't find it now but the solution relevant to you is to replace filename.COR.txt with filename.txt

Thinking about it, the other thing with transfertext is if you don't specify a import profile it will fail. I've got round the problem with using this (sorry, just pulled from a routine of mine so you'll need to join the dots) which imports to a temporary table (all fields text) and then a subsequent append query to it's final destination)

Set TxtFile = CreateObject("Scripting.FileSystemObject").GetFile(SrcFile).OpenAsTextStream(1)
                If RStP.Fields("SysHasFieldNames") = True Then s = TxtFile.ReadLine
                While Not TxtFile.AtEndOfStream
                    s = TxtFile.ReadLine
[COLOR=seagreen]'this next bit in red (in your case) needs to change tabs to commas and tidy up single and double quotations (rstp is a recordset which holds details of expected source file in my case)[/COLOR]                 
[COLOR=red]If Len(RStP.Fields("SysRange")) = 2 Then
                        If Right(RStP.Fields("SysRange"), 1) = """" Then
                            s = Replace(s, "'", "''")
                            s = Replace(s, """", "")
                        End If
                    End If
                    
                    SrcFields = Replace(s, Left(RStP.Fields("SysRange"), 1), "','")
[/COLOR]                    
                    SQLStr = "INSERT INTO Tmp_Table (" & DstFields & ") SELECT '" & SrcFields & "'"
                    Tdb.Execute SQLStr
                Wend

Hope that makes sense - enjoy your bickies!

 

[daievans]

9 tons of soil? Serious excercise!
If I were close I'd offer to help.
Thanks for the clue - will try it later - after the McVitie's (from our version of Tesco - Fresh and Easy!)

 

[daievans]

Well, I solved the initial problem ...

The files were arriving as *.COR. My initial idea was simply to add .txt to make them text files .... so they were *.COR.txt .... This was causing an issue. When I remove the .cor the process worked ... sort of ... it processes all the files but lumps them into one field names as a concatenation of all the field names ....

Anyone seen this? Is it an issue with the import file setting?

 

[daievans]

Thanks for the inspiration I get from these pages, I identified my own problem - I was not defining the Import Spec as a variable and including it in the DoCmd.TransferTxt .....


I think I will thank myself!

Thanks again to Beetle and CJ though ....

 

[CJ_London]

Glad you got there in the end, after 11 tonnes (in the end, numerous pints of beer and a bottle of wine) I sharn't be doing much tomorrow! eeeee....

Thanks for your offer of help - maybe next time with a bit more notice!